Chapter 11 of 35

DISTINCT

Ask a simple question like "which cities do our employees live in?" and run SELECT city FROM employees. You get 10 rows, but Bengaluru and Mumbai appear twice. You asked for a list of cities, and SQL gave you one city per employee instead.

That is because SQL, by default, returns every row that matches, duplicates included. When you want each value only once, you add one keyword: DISTINCT.

In this chapter we will see how DISTINCT works on one and on many columns, how it treats NULL, how it interacts with ORDER BY and LIMIT, what it costs the database, and the common mistakes people make with it. All examples use the shared company_db sample database with real MySQL 8.4 output.


What is DISTINCT?

DISTINCT removes duplicate rows from the result of a SELECT. Two result rows are duplicates when they have the same value in every selected column.

The syntax is simple. DISTINCT goes right after SELECT, before the column list:

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

Here is the problem from the introduction. First without DISTINCT:

SELECT city FROM employees;
+-----------+
| city      |
+-----------+
| Bengaluru |
| Chennai   |
| Bengaluru |
| Mumbai    |
| Mumbai    |
| Kolkata   |
| Delhi     |
| Kochi     |
| Hyderabad |
| NULL      |
+-----------+
10 rows in set (0.00 sec)

And with DISTINCT:

SELECT DISTINCT city FROM employees;
+-----------+
| city      |
+-----------+
| Bengaluru |
| Chennai   |
| Mumbai    |
| Kolkata   |
| Delhi     |
| Kochi     |
| Hyderabad |
| NULL      |
+-----------+
8 rows in set (0.00 sec)

10 rows became 8. The second Bengaluru and the second Mumbai are gone.

Why Doesn't SQL Remove Duplicates Automatically?

In pure relational theory a relation is a set, so it has no duplicates. Real SQL works with bags (multisets) instead, for two practical reasons:

  • Duplicates are often meaningful. Two employees earning 95000 are two different people. If SQL silently merged them, SELECT salary FROM employees would lose information, and totals would be wrong.

  • Removing duplicates costs work. The database has to compare rows with each other (by sorting or hashing). Doing that on every query would slow down queries that do not need it.

So SQL keeps duplicates unless you explicitly ask for them to be removed. The default is actually the keyword ALL, which you almost never write:

SELECT ALL city FROM employees LIMIT 3;   -- same as SELECT city ...
+-----------+
| city      |
+-----------+
| Bengaluru |
| Chennai   |
| Bengaluru |
+-----------+
3 rows in set (0.00 sec)

How DISTINCT Works (Conceptually)

It helps to know when DISTINCT happens in the life of a query. The logical order of processing is:

1. FROM       pick the table
2. WHERE      keep only matching rows
3. SELECT     compute the output columns
4. DISTINCT   throw away duplicate output rows
5. ORDER BY   sort what is left
6. LIMIT      cut the result down to N rows

Two important consequences follow from this order:

  • DISTINCT compares the output rows (after SELECT), not the original table rows. It only looks at the columns you selected.

  • DISTINCT happens before LIMIT. So SELECT DISTINCT ... LIMIT 3 gives you 3 different values, not "3 rows and then remove duplicates".

Physically, MySQL removes duplicates either by putting rows into a temporary table with a unique key (a hash-like approach) or by reading them in sorted order from an index, where duplicates sit next to each other. We will peek at this in the performance section.


DISTINCT on a Single Column

Which departments actually have employees? dept_id repeats once per employee:

SELECT DISTINCT dept_id FROM employees ORDER BY dept_id;
+---------+
| dept_id |
+---------+
|    NULL |
|      10 |
|      20 |
|      30 |
|      40 |
+---------+
5 rows in set (0.00 sec)

Department 50 (Research) is not in the list because no employee belongs to it. DISTINCT only works with values that are actually present in the rows you query.

Which order statuses exist in the system?

SELECT DISTINCT status FROM orders;
+-----------+
| status    |
+-----------+
| Delivered |
| Cancelled |
| Shipped   |
| Pending   |
+-----------+
4 rows in set (0.00 sec)

This is a very common real-world use: building a drop-down filter on a website ("Filter by status") from the values that exist in the data.

DISTINCT with WHERE

WHERE runs first, then DISTINCT. Which customers have had at least one order delivered?

SELECT DISTINCT status, customer_id
FROM orders
WHERE status = 'Delivered';
+-----------+-------------+
| status    | customer_id |
+-----------+-------------+
| Delivered |         101 |
| Delivered |         102 |
| Delivered |         103 |
+-----------+-------------+
3 rows in set (0.00 sec)

Customer 102 has two delivered orders (1002 and 1006), but appears once.

DISTINCT on an Expression

DISTINCT works on whatever the SELECT list produces, including computed values. In which years did we hire people?

SELECT DISTINCT YEAR(hire_date) AS hire_year
FROM employees
ORDER BY hire_year;
+-----------+
| hire_year |
+-----------+
|      2017 |
|      2018 |
|      2019 |
|      2020 |
|      2021 |
|      2022 |
|      2023 |
|      2024 |
+-----------+
8 rows in set (0.00 sec)

Ten hire dates, but 2019 and 2020 each appear twice, so we get 8 distinct years. (YEAR() is covered properly in the Date & Time Functions chapter.)


DISTINCT on Multiple Columns

This is where most beginners go wrong. When you list several columns, DISTINCT looks at the combination of all of them. A row is removed only if every column matches another row.

SELECT DISTINCT dept_id, city
FROM employees
ORDER BY dept_id, city;
+---------+-----------+
| dept_id | city      |
+---------+-----------+
|    NULL | NULL      |
|      10 | Bengaluru |
|      10 | Chennai   |
|      20 | Kolkata   |
|      20 | Mumbai    |
|      30 | Delhi     |
|      30 | Kochi     |
|      40 | Hyderabad |
+---------+-----------+
8 rows in set (0.00 sec)

Let's trace it. The raw (dept_id, city) pairs are:

(10, Bengaluru)  Aarav
(10, Chennai)    Priya
(10, Bengaluru)  Rohan    <-- same pair as Aarav, removed
(20, Mumbai)     Sneha
(20, Mumbai)     Vikram   <-- same pair as Sneha, removed
(20, Kolkata)    Ananya
(30, Delhi)      Karan
(30, Kochi)      Meera
(40, Hyderabad)  Arjun
(NULL, NULL)     Ishita

10 pairs, 2 repeats, 8 distinct pairs. Notice 10 still appears twice in the output, because (10, Bengaluru) and (10, Chennai) are different combinations.

Key idea: DISTINCT is not applied per column. It is applied to the whole output row.

DISTINCT is Not a Function

People often write DISTINCT(dept_id), city hoping to get distinct departments "plus a city". It does not work that way:

SELECT DISTINCT(dept_id), city
FROM employees
ORDER BY dept_id, city;
+---------+-----------+
| dept_id | city      |
+---------+-----------+
|    NULL | NULL      |
|      10 | Bengaluru |
|      10 | Chennai   |
|      20 | Kolkata   |
|      20 | Mumbai    |
|      30 | Delhi     |
|      30 | Kochi     |
|      40 | Hyderabad |
+---------+-----------+
8 rows in set (0.00 sec)

Exactly the same result as before. The parentheses are just ordinary brackets around the expression dept_id. MySQL reads it as SELECT DISTINCT (dept_id), city, and DISTINCT still applies to the whole row.

If you really want "one row per department, with some city", that is a grouping question: which city should be picked? You answer that with GROUP BY and an aggregate, which we will meet in Aggregate Functions and GROUP BY & HAVING.

Adding a Unique Column Defeats DISTINCT

If any selected column is unique (like a primary key, or here a name), every row is already different, so DISTINCT removes nothing:

SELECT DISTINCT name, city FROM employees WHERE dept_id = 10;
+--------------+-----------+
| name         | city      |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Priya Iyer   | Chennai   |
| Rohan Mehta  | Bengaluru |
+--------------+-----------+
3 rows in set (0.00 sec)

Bengaluru appears twice and that is correct: (Aarav, Bengaluru) and (Rohan, Bengaluru) are different rows.


DISTINCT and NULL

In the NULL Values chapter we learned that NULL = NULL is UNKNOWN, not TRUE. So you might expect DISTINCT to keep every NULL as a separate row. It does not.

For DISTINCT (and also GROUP BY), all NULLs are treated as one group. You get at most one NULL in the output per column combination.

SELECT DISTINCT customer_id FROM orders;
+-------------+
| customer_id |
+-------------+
|        NULL |
|         101 |
|         102 |
|         103 |
|         104 |
+-------------+
5 rows in set (0.00 sec)

Why the difference? DISTINCT does not ask "are these equal?" using =. It asks "are these two rows not distinct from each other?", and the SQL standard defines two NULLs as not distinct. It is the same idea as MySQL's null-safe operator <=>.

If you do not want NULL in the list, filter it out first:

SELECT DISTINCT customer_id
FROM orders
WHERE customer_id IS NOT NULL
ORDER BY customer_id;
+-------------+
| customer_id |
+-------------+
|         101 |
|         102 |
|         103 |
|         104 |
+-------------+
4 rows in set (0.00 sec)

Notice also: in the previous query the rows came out in a particular order even without ORDER BY. That order is an accident of how MySQL read the data (here, through an index). Never rely on it. If order matters, write ORDER BY.


DISTINCT with ORDER BY

Sorting a distinct list is normal:

SELECT DISTINCT city FROM employees ORDER BY city;
+-----------+
| city      |
+-----------+
| NULL      |
| Bengaluru |
| Chennai   |
| Delhi     |
| Hyderabad |
| Kochi     |
| Kolkata   |
| Mumbai    |
+-----------+
8 rows in set (0.00 sec)

(NULL sorts first in ascending order in MySQL, as we saw in ORDER BY & LIMIT.)

The ORDER BY Trap

Now try sorting distinct departments by salary:

SELECT DISTINCT dept_id FROM employees ORDER BY salary;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'company_db.employees.salary' which is not in SELECT list; this is incompatible with DISTINCT

MySQL refuses, and it is right to. Department 10 has three employees with salaries 150000, 95000 and 95000. After DISTINCT there is only one row for department 10. Which salary should be used to place it in the sort? The question has no single answer, so the query is meaningless.

Rule: with SELECT DISTINCT, every ORDER BY expression must be one of the selected columns (or computed only from them).

The fix depends on what you meant. "Sort departments by their highest salary" needs MAX(salary) with GROUP BY, which you will learn in the next few chapters.

DISTINCT with LIMIT

Because DISTINCT runs before LIMIT, this gives three different cities:

SELECT DISTINCT city
FROM employees
WHERE city IS NOT NULL
ORDER BY city DESC
LIMIT 3;
+---------+
| city    |
+---------+
| Mumbai  |
| Kolkata |
| Kochi   |
+---------+
3 rows in set (0.00 sec)

Worked Example: The Second-Highest Salary

"Find the second-highest salary" is one of the most asked SQL interview questions, and DISTINCT is the key to the simple version of it. First, the distinct salaries from high to low:

SELECT DISTINCT salary FROM employees ORDER BY salary DESC;
+-----------+
| salary    |
+-----------+
| 150000.00 |
| 110000.00 |
|  95000.00 |
|  82000.00 |
|  70000.00 |
|  62000.00 |
|  58000.00 |
|  55000.00 |
|  48000.00 |
+-----------+
9 rows in set (0.00 sec)

10 employees, 9 distinct salaries (95000 is shared by Priya and Rohan). Skip the first row and take one:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
+-----------+
| salary    |
+-----------+
| 110000.00 |
+-----------+
1 row in set (0.00 sec)

Why does DISTINCT matter here? Without it, the query works on our data only by luck, because the top salary is unique. Without DISTINCT the sorted list is 150000, 110000, 95000, 95000, 82000, ... Asking for the fourth-highest salary with OFFSET 3 would then wrongly return 95000 (the second copy) instead of 82000. With DISTINCT, each salary level appears once, so OFFSET n-1 really means "the n-th highest salary value". (Ranking functions later give an even more robust way.)


Case Sensitivity: When Are Two Strings "the Same"?

DISTINCT compares values using the column's collation. MySQL 8's default collation, utf8mb4_0900_ai_ci, is case-insensitive (ci) and accent-insensitive (ai). So these three spellings count as one value:

SELECT DISTINCT c
FROM (VALUES ROW('Mumbai'), ROW('mumbai'), ROW('MUMBAI')) AS t(c);
+--------+
| c      |
+--------+
| Mumbai |
+--------+
1 row in set (0.00 sec)

(VALUES ROW(...) builds a tiny temporary table inline; it is just a quick way to test.) MySQL keeps one of the spellings; which one you see is not something to rely on. If you need case-sensitive comparison, use a binary collation:

SELECT DISTINCT c COLLATE utf8mb4_bin AS c
FROM (VALUES ROW('Mumbai'), ROW('mumbai'), ROW('MUMBAI')) AS t(c);
+--------+
| c      |
+--------+
| Mumbai |
| mumbai |
| MUMBAI |
+--------+
3 rows in set (0.00 sec)

This matters in real data cleaning: a city column with "Mumbai", "mumbai" and "MUMBAI" will look clean under DISTINCT in MySQL, but may show three values in another database (PostgreSQL compares case-sensitively by default). Be careful with spaces, though: utf8mb4_0900_ai_ci is a NO PAD collation, so a trailing space is significant and 'mumbai ' (with a space at the end) counts as a different value from 'Mumbai'. Clean such data with TRIM() (String Functions chapter).


A Quick Look at COUNT(DISTINCT ...)

Often you do not want the list of distinct values, just how many there are. DISTINCT can also be used inside the COUNT function:

SELECT COUNT(city), COUNT(DISTINCT city) FROM employees;
+-------------+----------------------+
| COUNT(city) | COUNT(DISTINCT city) |
+-------------+----------------------+
|           9 |                    7 |
+-------------+----------------------+
1 row in set (0.00 sec)

Interesting: SELECT DISTINCT city returned 8 rows, but COUNT(DISTINCT city) is 7. The difference is NULL. DISTINCT keeps one NULL row, but COUNT never counts NULLs. We will explore this properly in the Aggregate Functions chapter.


DISTINCT vs GROUP BY

For "give me the unique values" both of these return the same rows:

SELECT DISTINCT dept_id FROM employees;
SELECT dept_id FROM employees GROUP BY dept_id;
+---------+
| dept_id |
+---------+
|    NULL |
|      10 |
|      20 |
|      30 |
|      40 |
+---------+
5 rows in set (0.00 sec)

Point

DISTINCT

GROUP BY

Purpose

Remove duplicate output rows

Form groups, usually to compute something per group

Aggregates (COUNT, SUM...) per group

No

Yes

Filter groups

No

Yes, with HAVING

Readability for "unique values"

Clearer

Works, but hides intent

Performance for the same job

Usually the same plan in MySQL

Usually the same plan in MySQL

Rule of thumb: use DISTINCT when you just want unique rows; use GROUP BY when you want to calculate something for each group (count, total, average). GROUP BY & HAVING has its own chapter.


Performance: DISTINCT is Not Free

To remove duplicates the database must compare rows. You can see the strategy with EXPLAIN (covered in the Indexes chapter). For city, which has no index:

EXPLAIN SELECT DISTINCT city FROM employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

Using temporary means MySQL scans the whole table and builds a temporary table of unique cities. For dept_id, which is a foreign key and therefore indexed:

EXPLAIN SELECT DISTINCT dept_id FROM employees;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | range | dept_id       | dept_id | 5       | NULL |    6 |   100.00 | Using index for group-by |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Here MySQL reads the index, where equal values are already next to each other, and jumps from one value to the next. No temporary table needed. On 10 rows you will never notice, but on 10 million rows the difference is huge.

Practical advice:

  • Do not add DISTINCT "just in case". If the data cannot have duplicates (e.g. you select the primary key), DISTINCT only adds work and confuses readers.

  • If you find yourself adding DISTINCT to fix duplicate rows after a join, stop and check the join. Duplicates usually mean a join condition is missing or wrong, and DISTINCT just hides the bug. (You will see this in SQL Joins.)


DISTINCT vs DISTINCTROW vs UNIQUE

  • DISTINCTROW is a MySQL synonym for DISTINCT. It works, but nobody uses it; stick with DISTINCT, which is standard SQL.

  • SELECT UNIQUE is Oracle-only syntax and does not work in MySQL. In MySQL, UNIQUE is a constraint keyword (see SQL Constraints).


Common Mistakes

Mistake

What happens

Fix

Thinking DISTINCT(a), b makes only a distinct

DISTINCT still applies to the whole row (a, b)

Select only the columns that define "unique", or use GROUP BY with an aggregate

Selecting a unique column (id, name) together with DISTINCT

Nothing is removed

Drop the unique column from the SELECT list

ORDER BY a column not in the SELECT DISTINCT list

Error 3065

Sort by a selected column, or use GROUP BY with an aggregate like MAX()

Expecting DISTINCT to drop NULLs

One NULL row stays

Add WHERE col IS NOT NULL

Using DISTINCT to hide duplicate rows from a bad join

Correct-looking but slow (and sometimes wrong) result

Fix the join condition

Writing SELECT name, DISTINCT city

Syntax error: DISTINCT must come right after SELECT

SELECT DISTINCT name, city

Relying on the order of DISTINCT output

Order may change between versions or plans

Always add ORDER BY


Interview Questions

What does DISTINCT do?

It removes duplicate rows from a SELECT result. Two rows are duplicates only if all selected columns are equal.

How does DISTINCT behave with multiple columns?

It applies to the combination of all selected columns, not to each column separately. SELECT DISTINCT dept_id, city returns unique (dept_id, city) pairs, so a dept_id can repeat.

Does DISTINCT treat NULLs as equal?

Yes, for the purpose of removing duplicates all NULLs are considered one value, so the output has at most one NULL (per combination). This differs from the = operator, where NULL = NULL is UNKNOWN.

Why does SELECT DISTINCT a FROM t ORDER BY b fail in MySQL?

After duplicates of a are removed, one output row may correspond to many different b values, so sorting by b is ambiguous. MySQL raises error 3065. ORDER BY must use selected columns.

DISTINCT vs GROUP BY?

Both can return unique values, often with the same execution plan. GROUP BY is meant for computing aggregates per group and supports HAVING; DISTINCT only removes duplicates.

What is the difference between COUNT(col) and COUNT(DISTINCT col)?

COUNT(col) counts non-NULL values, including repeats. COUNT(DISTINCT col) counts the number of different non-NULL values.

Find the second-highest salary using DISTINCT.

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; DISTINCT ensures ties at a salary level count once.

Is DISTINCT expensive?

It can be: the database must sort or hash the rows to find duplicates, often using a temporary table. An index on the column can let it skip through values without a temporary table. Avoid DISTINCT when duplicates are impossible.


Summary

  • SQL returns duplicates by default (ALL); SELECT DISTINCT removes duplicate output rows.

  • DISTINCT compares the whole output row. With several columns, it is the combination that must be unique. DISTINCT(a) is not a function.

  • All NULLs count as one value for DISTINCT, so you get one NULL row; filter with IS NOT NULL if needed.

  • Logical order: WHERE, then SELECT, then DISTINCT, then ORDER BY, then LIMIT.

  • With DISTINCT, ORDER BY may only use selected columns.

  • String comparison follows the collation; MySQL's default is case-insensitive.

  • COUNT(DISTINCT col) counts different non-NULL values.

  • DISTINCT costs a sort or temporary table; do not use it to cover up bad joins.

So far we have only read data. In the next chapter, UPDATE & DELETE, we will start changing existing rows, and learn why a missing WHERE clause is the most dangerous mistake in SQL.