Loading

SQL SELECT DISTINCT

  • 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 NoNameAge
101Nobita18
102Suneo16
103Shizuka18
104Gain23

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 identical values from the output.