SQL Distinct Clause

The SQL DISTINCT keyword is used in queries to retrieve unique values from a database. It helps in eliminating duplicate records from the result set. It ensures that only unique entries are fetched. Whether you’re analyzing datasets or performing data cleaning, the DISTINCT keyword is Important for ensuring data integrity and precision.

In this article, we will learn the DISTINCT keyword, its syntax, practical examples, and common use cases. We’ll also discuss how it works with various SQL functions like COUNT() and how it handles NULL values.

What is the SQL DISTINCT Keyword ?

The distinct keyword is used in conjunction with the select keyword. It is helpful when there is a need to avoid duplicate values present in any specific columns/table. When we use distinct keywords only the unique values are fetched.

Syntax

SELECT DISTINCT column1, column2 
FROM table_name

Parameters Used

column1, column2: Names of the fields of the table.
table_name: Table from where we want to fetch the records.

This query will return all the unique combinations of rows in the table with fields column1, and column2.

Note

If a distinct keyword is used with multiple columns, the distinct combination is displayed in the result set.

Examples of DISTINCT in SQL

There is a Table in SQL with names of students with Rollno, Name, Address, Phone and Age. We will see some examples of using the DISTINCT keyword with a sample students table.

Query

CREATE TABLE students (
  ROLL_NO INT,
  NAME VARCHAR(50),
  ADDRESS VARCHAR(100),
  PHONE VARCHAR(20),
  AGE INT
);

INSERT INTO students (ROLL_NO, NAME, ADDRESS, PHONE, AGE)
VALUES 
  (1, 'Shubham Kumar', '123 Main Street, Bangalore', '9876543210', 23),
  (2, 'Shreya Gupta', '456 Park Road, Mumbai', '9876543211', 23),
  (3, 'Naveen Singh', '789 Market Lane, Delhi', '9876543212', 26),
  (4, 'Aman Chopra', '246 Forest Avenue, Kolkata', '9876543213', 22),
  (5, 'Aditya Patel', '7898 Ocean Drive, Chennai', '9876543214', 27),
  (6, 'Avdeep Desai', '34 River View, Hyderabad', '9876543215', 24),
  (7, 'Shubham Kumar', '123 Main Street, Bangalore', '9876543210', 23),  -- Duplicate
  (8, 'Shreya Gupta', '456 Park Road, Mumbai', '9876543211', 23),  -- Duplicate
  (9, 'Naveen Singh', '789 Market Lane, Delhi', '9876543212', 26),  -- Duplicate
  (10, 'Aditya Patel', '7898 Ocean Drive, Chennai', '9876543214', 27),  -- Duplicate
  (11, 'Aman Chopra', '246 Forest Avenue, Kolkata', '9876543213', 22),  -- Duplicate
  (12, 'Avdeep Desai', '34 River View, Hyderabad', '9876543215', 24);  -- Duplicate

Output

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	Shubham Kumar	123 Main Street, Bangalore	9876543210	23
2	Shreya Gupta	456 Park Road, Mumbai	9876543211	23
3	Naveen Singh	789 Market Lane, Delhi	9876543212	26
4	Aman Chopra	246 Forest Avenue, Kolkata	9876543213	22
5	Aditya Patel	7898 Ocean Drive, Chennai	9876543214	27
6	Avdeep Desai	34 River View, Hyderabad	9876543215	24
7	Shubham Kumar	123 Main Street, Bangalore	9876543210	23
8	Shreya Gupta	456 Park Road, Mumbai	9876543211	23
9	Naveen Singh	789 Market Lane, Delhi	9876543212	26
10	Aditya Patel	7898 Ocean Drive, Chennai	9876543214	27
11	Aman Chopra	246 Forest Avenue, Kolkata	9876543213	22
12	Avdeep Desai	34 River View, Hyderabad	9876543215	24

Example 1: Fetch Unique Names from the NAME Field.

Query

SELECT DISTINCT NAME FROM students;

Output

Example 1

The query returns only unique names, eliminating the duplicate entries from the table.

Example 2: Fetching Unique Combinations of Multiple Columns

If you want to retrieve unique combinations of NAME and AGE

Query

SELECT DISTINCT NAME, AGE FROM students;

Output

NAME	AGE
Shubham Kumar	23
Shreya Gupta	23
Naveen Singh	26
Aman Chopra	22
Aditya Patel	27
Avdeep Desai	24

This query retrieves distinct combinations of NAME and AGE — if two rows have the same name and age, only one of them will appear in the result set.

Example 3: Using DISTINCT with the ORDER BY Clause

We can combine the DISTINCT keyword with the ORDER BY clause to filter unique values while sorting the result set.

Query

SELECT DISTINCT AGE FROM students ORDER BY AGE;

Output

AGE
22
23
24
26
27

This query retrieves the unique ages from the students table and sorts them in ascending order.

Example 4: Using DISTINCT with Aggregate Functions (e.g., COUNT())

Here, we will check the COUNT() function with a DISTINCT clause, which will give the total number of students by using the COUNT() function.

Query

SELECT COUNT(DISTINCT ROLL_NO)  FROM Students;

Output

COUNT(DISTINCT AGE)
5

How the DISTINCT Clause Handles NULL Values ?

In SQL, the DISTINCT keyword treats NULL as a unique value. If NULL appears in a column, it will be counted as a distinct value.

Query

INSERT INTO students (ROLL_NO, NAME, ADDRESS, PHONE, AGE)
VALUES (13, 'John Doe', '123 Unknown Street', '9876543216', NULL);

SELECT DISTINCT AGE FROM students;

Output

AGE
23
26
22
27
24
-

Conclusion

The SQL DISTINCT keyword is an essential tool for removing duplicates and fetching unique data from your tables. It can be used with a single column or multiple columns and works seamlessly with other SQL clauses like ORDER BY and aggregate functions such as COUNT(). By understanding how to use DISTINCT, you can improve your queries and ensure that your results are clean, accurate, and free of redundancies.