SQL Distinct Clause

The DISTINCT keyword in SQL is used to retrieve only unique values, eliminating duplicates from query results. It ensures data accuracy and is often used with the SELECT statement for clean, precise reporting.

• Removes duplicate rows
• Ensures accurate, clean results
• Works on single or multiple columns

Syntax: 

SELECT DISTINCT column1, column2 
FROM table_name

Parameters:

• 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.

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:

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 NULL as a unique value. NULL is treated as a distinct value, so it will appear only once if there are multiple NULLs.

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: