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.
Distinct Operations
There is a Table with names of students with Rollno, Name, Address, Phone and Age. Below is the query:
Query
CREATE TABLE students (
ROLL_NO INT,
NAME VARCHAR(50),
ADDRESS VARCHAR(100),
PHONE VARCHAR(20),
AGE INT
);
Inserting some random data to perform distinct operations.
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);
Select * from students;
Output

Students Table
Example 1: Now, to fetch unique names from the NAME field.
Query:
SELECT DISTINCT NAME FROM Student;
Output:

output
Example 2: Now, to fetch a unique combination of rows from the whole table.
Syntax:
SELECT DISTINCT * FROM Table_name;
Query:
SELECT DISTINCT * FROM students;
Output:

output
Using Distinct Clause with Order By
Here, we will check the order by clause with a distinct clause that will filter out the data based on the order by clause.
Query
SELECT DISTINCT ROLL_NO FROM Students ORDER BY AGE;
Output

output
Using Distinct Clause with COUNT() Function
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

output
How the DISTINCT Clause Handles NULL Values?
Finally, does the DISTINCT clause considers a NULL to be a unique value in SQL? The answer is yes.
CREATE TABLE:
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', NULL);
Output

students table
Query
SELECT DISTINCT AGE
FROM students;
Output

output
Note:
Without the keyword distinct in both the above examples 6 records would have been fetched instead of 4, since in the original table there are 6 records with the duplicate values.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our
Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our
Complete Interview Preparation course.
Last Updated :
30 Oct, 2023
Like Article
Save Article