SQL SELECT DISTINCTL
- The DISTINCT keyword in SQL eliminates all duplicate records from the result returned by the SQL query.
- The DISTINCT keyword is used in combination with select statements.
- Only unique records are returned when the DISTINCT keyword is used while fetching records from a table having multiple duplicate records.
- DISTINCT can also be used along with aggregate SQL functions like COUNT, MAX, SUM, AVG etc.
- DISTINCT operates not only on a single column of a table but also has support for multiple columns of a table.
- DISTINCT in SQL will eliminate those rows where all the selected columns are identical.
Syntax:
SELECT DISTINCT column _ name
FROM table _ name;
- A DISTINCT clause in SQL can be applied to any valid SELECT query, where it will filter out all rows that are not unique in terms of all selected columns.
- column _ name: name of the column on which we want to apply the DISTINCT clause.
- table _ name: name of the table from which we want to retrieve the records.
Use of DISTINCT in SQL:
Let us consider the following STUDENTS table, containing roll no, name, and age :
Roll No | Name | Age |
---|---|---|
101 | Nobita | 18 |
102 | Suneo | 16 |
103 | Shizuka | 18 |
104 | Gain | 23 |
First, write a SQL query to return all student's ages, including duplicate values:
Example:
SELECT Age as age
FROM students;
Output:
- The above SQL query returns the age of every student, including duplicate value ie., 18 occurs twice in the age column of the student's table.
- To remove these duplicate age values, we can use DISTINCT in the SQL clause before the column name in combination with the SELECT query
Example:
SELECT DISTINCT age as age
FROM Students;
Output:
- The above SQL query returns only the unique ages from the student's table
- Duplicate value 18 is not returned because the DISTINCT clause eliminates all the duplicate values from the output.