The SQL DISTINCT clause is used to remove duplicate values from query results. It ensures that the output shows only unique records for the specified column(s).
Example: First, we will create a demo SQL database and table, on which we will use the Distinct Clause command.
Query:
SELECT DISTINCT Department
FROM Employees;Output:
Syntax:
SELECT DISTINCT column1, column2
FROM table_name;- column1, column2: Names of the fields of the table.
- Table_name: Table from where we want to fetch the records.
Note: If used on multiple columns, DISTINCT returns unique combinations of values across those columns.
Examples of DISTINCT in SQL
Let’s create a sample table and populate it with some duplicate entries. We will see some examples of using the DISTINCT keyword with a sample students table.
Example 1: Fetch Unique Names from the NAME Field
The query returns only unique names, eliminating the duplicate entries from the table.
Query:
SELECT DISTINCT NAME
FROM students;
Output:
Example 2: Fetching Unique Combinations of Multiple Columns
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.
Query:
SELECT DISTINCT NAME, AGE
FROM students;
Output:
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. This query retrieves the unique ages from the students table and sorts them in ascending order.
Query:
SELECT DISTINCT AGE
FROM students
ORDER BY AGE;
Output:
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:
Example 5: DISTINCT with NULL Values
In SQL, the DISTINCT keyword treats all NULL values as the same, not as separate unique values. As a result, even if multiple rows contain NULL, DISTINCT will return NULL only once in the result set.
Query:
INSERT INTO students (ROLL_NO, NAME, ADDRESS, PHONE, AGE) VALUES (13, 'John Doe', '123 Unknown Street', '9876543216', NULL), (14, 'James Brown', '129 Unknown Street', '9876554317', NULL); SELECT DISTINCT AGE
FROM students;
Output:
- Multiple rows with AGE = NULL are treated as the same value by DISTINCT.
- As a result, NULL appears only once in the query output.