What you will be able to do
After this lesson you can turn a column full of repeats into a clean list of the different values, count how many different values there are, and recognise when DISTINCT is the wrong tool.
The problem: repeats
A column often holds the same value on many rows. The employees table has twenty rows, but far fewer job titles, and several people share a department. If you select only the title, the repeats show up:
SELECT job_title FROM employees;+----------------------+
| job_title |
+----------------------+
| Engineering Director |
| Senior Developer |
| Senior Developer |
| Developer |
| Developer |
| Sales Director |
| Account Executive |
| Account Executive |
| Sales Associate |
| Marketing Manager |
| Content Strategist |
| Designer |
| Finance Manager |
| Accountant |
| Support Lead |
| Support Engineer |
| Support Engineer |
| Consultant |
| Junior Developer |
| Sales Associate |
+----------------------+Reading twenty lines to find the different titles is tedious. DISTINCT right after SELECT removes repeated rows:
SELECT DISTINCT job_title FROM employees;+----------------------+
| job_title |
+----------------------+
| Engineering Director |
| Senior Developer |
| Developer |
| Sales Director |
| Account Executive |
| Sales Associate |
| Marketing Manager |
| Content Strategist |
| Designer |
| Finance Manager |
| Accountant |
| Support Lead |
| Support Engineer |
| Consultant |
| Junior Developer |
+----------------------+There are fifteen different titles for twenty employees. Developer, Senior Developer, Account Executive, Sales Associate and Support Engineer are the ones that repeat.
DISTINCT works on the result
DISTINCT works on the result, not on the stored table. It never deletes anything. It compares the rows your select list produced and keeps one copy of each. That also means the result of DISTINCT depends on which columns you picked. Compare these two:
SELECT DISTINCT city FROM departments;+-----------+
| city |
+-----------+
| Pune |
| Mumbai |
| Delhi |
| Bengaluru |
+-----------+SELECT city FROM departments;+-----------+
| city |
+-----------+
| Pune |
| Mumbai |
| Delhi |
| Mumbai |
| Pune |
| Bengaluru |
+-----------+Mumbai and Pune each belong to two departments, so the first query returns four cities and the second returns six rows.
DISTINCT with several columns
DISTINCT always applies to the whole row of selected columns, even though it is written once. It is not a function on the first column. Two rows are duplicates only if every selected value matches:
SELECT DISTINCT department_id, job_title
FROM employees
WHERE department_id IN (1, 2);+---------------+----------------------+
| department_id | job_title |
+---------------+----------------------+
| 1 | Engineering Director |
| 1 | Senior Developer |
| 1 | Developer |
| 2 | Sales Director |
| 2 | Account Executive |
| 2 | Sales Associate |
| 1 | Junior Developer |
+---------------+----------------------+Department 1 has four different titles among its employees and department 2 has three, so the result has seven rows. The pair (1, Developer) is shown once even though two people share it. If you wrote DISTINCT department_id alone you would see just the two department numbers.
You cannot make DISTINCT apply to only one of the columns by wrapping it in parentheses. SELECT DISTINCT (department_id), job_title means the same as before. PostgreSQL has a separate DISTINCT ON (column) form that keeps one row per value of a column. It is specific to PostgreSQL and is not covered here.
NULL and DISTINCT
For comparisons NULL is special, as the NULL lesson explains. But for DISTINCT, all NULLs are treated as the same, so they collapse into one row:
SELECT DISTINCT department_id FROM employees;+---------------+
| department_id |
+---------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| NULL |
+---------------+The NULL row is the consultant Ravi Pillai, who has no department. The result has six rows: five departments that have people plus the NULL. Department 6, Research, has no employees, so it does not appear at all.
Counting distinct values
How many departments have at least one employee? Put DISTINCT inside COUNT:
SELECT COUNT(DISTINCT department_id) AS departments_with_staff,
COUNT(department_id) AS employees_with_department,
COUNT(*) AS all_employees
FROM employees;+------------------------+---------------------------+---------------+
| departments_with_staff | employees_with_department | all_employees |
+------------------------+---------------------------+---------------+
| 5 | 19 | 20 |
+------------------------+---------------------------+---------------+COUNT(DISTINCT department_id) ignores the NULL and counts five different departments. COUNT(department_id) counts 19 non-NULL values, and COUNT(*) counts all 20 rows. The aggregate functions lesson returns to this.
When not to use DISTINCT
DISTINCT has a cost: the database has to compare or sort rows to find the duplicates. More importantly, it is easy to misuse. If a query unexpectedly returns the same row several times, the usual cause is a mistake in how tables are combined or filtered. Adding DISTINCT hides the symptom, and the next change can make the numbers silently wrong. Understand why duplicates appear first. Use DISTINCT when you genuinely want the unique values, as when you fill a drop-down list with cities.
Differences between databases
SELECT DISTINCT and COUNT(DISTINCT ...) behave the same on MySQL, PostgreSQL, SQLite and SQL Server. Details to know:
DISTINCT ONexists on PostgreSQL only.COUNT(DISTINCT a, b)with two columns works on MySQL, but SQLite, PostgreSQL and SQL Server do not accept it. To count distinct combinations portably, use a subquery, which a later lesson covers.- Text comparison may be case sensitive or not, depending on the database and its settings, so
Puneandpunemight or might not count as the same value.
The MySQL equivalent lesson is DISTINCT and aliases.
Common mistakes
- Thinking
DISTINCTaffects only the first column. It applies to the entire row of the select list. - Adding
DISTINCTto fix unexplained duplicates instead of finding the cause. - Expecting
COUNT(DISTINCT col)to count NULL. It does not. - Selecting a unique id along with other columns and then wondering why
DISTINCTremoves nothing. If one column is different on every row, every row is distinct.
Exercise
List the different cities where departments are based, in a column called office_city, and then write a second query that counts them.
Show one possible answer
SELECT DISTINCT city AS office_city FROM departments;+-------------+
| office_city |
+-------------+
| Pune |
| Mumbai |
| Delhi |
| Bengaluru |
+-------------+SELECT COUNT(DISTINCT city) AS number_of_cities FROM departments;+------------------+
| number_of_cities |
+------------------+
| 4 |
+------------------+Key points
- SELECT DISTINCT returns each different row once, so repeated values collapse into one.
- DISTINCT looks at the whole row of selected columns, not just the first column.
- NULL counts as a value for DISTINCT, so all NULLs collapse into a single row.
- COUNT(DISTINCT column) counts the different non-NULL values in a column.
- Do not add DISTINCT just to hide duplicates you do not understand. Find out why they appear.