SQL - Select Distinct Records

A column in the database table may contain duplicate values. The SELECT DISTINCT statement is used to retrieve unique values of a column or combination of one or more columns.

Syntax:

SELECT DISTINCT column_name1, column_name2,...
FROM table_name
[WHERE Condition];

For the demo purpose, we will use the following Employee table in all examples.

EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Khanna' 18000 2019-01-25

The following query returns unique records from the Employee table.

SQL Script: Select Distinct Records
SELECT DISTINCT * FROM Employee;

The above query returns all the unique records even if at least one column has a unique value.

EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
5 'Amit' 'Khanna' 18000 2019-01-25

The following query retrieves the unique FirstName values.

SQL Script: Distinct Columns
SELECT DISTINCT FirstName FROM Employee;
FirstName
'John'
'James'
'Neena'
'Lex'
'Amit'
'Abdul'

The following query retrieves the unique combination of FirstName and LastName values.

SQL Script: Distinct Columns
SELECT DISTINCT FirstName, LastName FROM Employee;
FirstName LastName
'John' 'King'
'James' 'Bond'
'Neena' 'Kochhar'
'Lex' 'De Haan'
'Amit' 'Patel'
'Abdul' 'Kalam'
'Amit' 'Khanna'

The following counts the unique EmpId.

SQL Script: Count Distinct Values
SELECT count(DISTINCT EmpId) FROM Employee;
count(DISTINCT EmpId)
6