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 employeeswould 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 rowsTwo 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 3gives 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) Ishita10 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 DISTINCTMySQL 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
DISTINCTROWis a MySQL synonym forDISTINCT. It works, but nobody uses it; stick withDISTINCT, which is standard SQL.SELECT UNIQUEis Oracle-only syntax and does not work in MySQL. In MySQL,UNIQUEis a constraint keyword (see SQL Constraints).
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
Thinking | 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 |
Expecting DISTINCT to drop NULLs | One NULL row stays | Add |
Using DISTINCT to hide duplicate rows from a bad join | Correct-looking but slow (and sometimes wrong) result | Fix the join condition |
Writing | Syntax error: DISTINCT must come right after SELECT |
|
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 DISTINCTremoves 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 NULLif 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.