SQL DISTINCT: Remove Duplicate Rows From Results

When you select a column, you get one value per row, including repeats. If five employees work in three departments, SELECT department returns five rows. DISTINCT gives you each value once.

The example table#

Without DISTINCT#

SELECT department
FROM employees;

department
-----------
Sales
Engineering
Engineering
Marketing
Sales

With DISTINCT#

SELECT DISTINCT department
FROM employees;

department
-----------
Sales
Engineering
Marketing

DISTINCT goes right after SELECT and removes duplicate rows from the result. The table itself is not changed.

Syntax#

SELECT DISTINCT column1, column2, ...
FROM table_name;

DISTINCT with several columns#

With more than one column, DISTINCT removes rows where the whole combination is repeated, not each column separately:

SELECT DISTINCT department, city
FROM employees
ORDER BY department, city;

department  | city
------------+----------
Engineering | Bengaluru
Engineering | Pune
Marketing   | Mumbai
Sales       | Delhi
Sales       | Mumbai

"Engineering" appears twice because it pairs with two different cities. Every row in the result is a unique pair.

Sorting distinct values#

The order of rows from DISTINCT is not guaranteed. Add ORDER BY when order matters:

SELECT DISTINCT city
FROM employees
ORDER BY city;

city
---------
Bengaluru
Delhi
Mumbai
Pune

Count unique values#

Put DISTINCT inside COUNT to count how many different values exist:

SELECT COUNT(DISTINCT city) AS cities
FROM employees;

cities
------
4

Five employees live in four different cities, because Asha and John are both in Mumbai.

How DISTINCT treats NULL#

DISTINCT treats all NULLs as equal, so they collapse into a single NULL row. COUNT(DISTINCT column), however, ignores NULLs completely, like every aggregate function except COUNT(*).

DISTINCT vs GROUP BY#

These two queries return the same rows:

SELECT DISTINCT department FROM employees;

SELECT department FROM employees GROUP BY department;

Use DISTINCT when you just want unique values. Use GROUP BY when you also want to calculate something per group, such as a count or an average.

Common mistakes#

Practice#

SELECT DISTINCT city
FROM employees
ORDER BY city;

city
---------
Bengaluru
Delhi
Mumbai
Pune

SELECT COUNT(DISTINCT department) AS departments
FROM employees;

departments
-----------
3

Key takeaway#