SQL SELECT DISTINCT

A plain SELECT returns one result row for every table row, so the same value often appears many times. SELECT DISTINCT removes the duplicates and returns each unique value, or each unique combination of values, only once.

Sample data

Duplicates without DISTINCT

Which cities do our employees work in?

SELECT cityFROM employeesORDER BY city;

The result has 12 rows because there are 12 employees: Bengaluru and Pune appear several times.

SELECT DISTINCT on one column

SELECT DISTINCT cityFROM employeesORDER BY city;

DISTINCT on several columns

With more than one column, DISTINCT removes rows where all the listed columns are the same. It works on the combination, not on each column separately:

SELECT DISTINCT dept_id, cityFROM employeesORDER BY dept_id, city;

DISTINCT and NULL

DISTINCT treats all NULLs as equal, so they collapse into one NULL row:

SELECT DISTINCT commissionFROM employeesORDER BY commission;

COUNT(DISTINCT column)

To count how many different values a column has, put DISTINCT inside COUNT:

SELECT COUNT(*)             AS employees,       COUNT(city)          AS rows_with_city,       COUNT(DISTINCT city) AS different_cities,       COUNT(DISTINCT dept_id) AS departments_usedFROM employees;

Which customers have placed at least one order? DISTINCT turns “one row per order” into “one row per customer”:

SELECT DISTINCT customer_idFROM ordersORDER BY customer_id;

DISTINCT vs GROUP BY

SELECT DISTINCT city FROM employees and SELECT city FROM employees GROUP BY city return the same rows. Use DISTINCT when you only want unique rows; use GROUP BY when you also want a count, sum or other aggregate per group:

SELECT city, COUNT(*) AS staffFROM employeesGROUP BY cityORDER BY staff DESC, city;

SELECT DISTINCT job_titleFROM employeesORDER BY job_title;

SELECT COUNT(DISTINCT category) AS categoriesFROM products;

SELECT DISTINCT FAQ