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:

Example 1: Fetch Unique Names from the NAME Field.

Query:

SELECT DISTINCT NAME FROM students;

Output:

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:

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:

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:

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:

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.