MySQL DISTINCT: remove duplicate rows

Quick answer

SELECT DISTINCT country FROM customers;

DISTINCT removes duplicate rows from the result. With one column that means unique values. With several columns it means unique combinations, which is the part that surprises people.

Setting up

Every example uses this table:

CREATE TABLE t (a int, b int);
INSERT INTO t VALUES (1,1), (1,1), (1,2), (2,2), (NULL,1), (NULL,1);

Six rows, with duplicates and NULLs.

DISTINCT on one column

SELECT DISTINCT a FROM t ORDER BY a;

a
NULL
1
2

Three rows out of six. Note that NULL appears, once.

DISTINCT applies to the whole row

This is the single most common misunderstanding. DISTINCT is not a function you apply to a column. It is a modifier on the entire SELECT list.

SELECT DISTINCT a, b FROM t ORDER BY a, b;

a       b
NULL    1
1       1
1       2
2       2

Four rows, not three. Column a has only three distinct values, but there are four distinct pairs. Adding a column to your select list can only ever increase the row count.

That is why "I added a column and now DISTINCT stopped working" is such a common complaint. It is working. You changed the question.

The parentheses trap

Because DISTINCT looks like it should be a function, people write this:

SELECT DISTINCT(a), b FROM t ORDER BY a, b;

a       b
NULL    1
1       1
1       2
2       2

Identical to the previous result. The parentheses did nothing. MySQL parsed them as grouping around the expression a, and DISTINCT still applied to the whole row.

This is dangerous precisely because it does not error. It reads like "distinct values of a, plus b", it returns plausible-looking data, and it is not doing what the author meant. If you see DISTINCT( in a query, treat it as a bug until proven otherwise.

There is no way to make DISTINCT apply to one column while selecting others. If that is what you want, you want GROUP BY and an aggregate, because you have to tell the database which b to keep.

NULL handling

DISTINCT treats all NULLs as equal to each other, which is the opposite of how NULL behaves everywhere else in SQL:

SELECT DISTINCT a FROM t;   -- one NULL row, not two

Two rows had a = NULL and the result has one. Compare with WHERE a = NULL, which matches nothing, because NULL = NULL is unknown rather than true.

So DISTINCT uses "not distinct from" semantics rather than =. It is an inconsistency in SQL itself, not a MySQL quirk, and it is worth knowing because it is one of the few places NULLs group rather than vanish.

COUNT(DISTINCT ...)

Here DISTINCT genuinely does apply to just the argument:

SELECT COUNT(*) AS total, COUNT(a) AS non_null, COUNT(DISTINCT a) AS distinct_a FROM t;

total   non_null   distinct_a
6       4          2

Three different numbers from the same column, and each answers a different question:

• COUNT(*) counts rows: 6.
• COUNT(a) counts non-NULL values of a: 4, because two rows are NULL.
• COUNT(DISTINCT a) counts distinct non-NULL values: 2, being 1 and 2.

That last one catches people out. SELECT DISTINCT a returned three rows including NULL, but COUNT(DISTINCT a) says 2. Both are right: aggregate functions ignore NULL, and COUNT(DISTINCT ...) is an aggregate.

If you need NULL counted as its own value, deduplicate in a subquery and count the rows:

SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t) x;

3

Two against three, from the same column, purely over whether NULL counts.

COUNT(DISTINCT a, b) counts distinct combinations, and skips any row where either column is NULL:

SELECT COUNT(DISTINCT a, b) FROM t;          -- 3
SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t) x;   -- 4

The (NULL, 1) pair is present in one and missing from the other. That asymmetry with SELECT DISTINCT a, b is worth remembering.

DISTINCT vs GROUP BY

For deduplication alone, they are equivalent:

SELECT DISTINCT country FROM customers;
SELECT country FROM customers GROUP BY country;

Same result, and MySQL produces effectively the same plan. Use DISTINCT, because it says what you mean.

GROUP BY earns its place the moment you want anything computed per group:

SELECT country, COUNT(*) AS customers, MAX(created_at) AS newest
FROM customers
GROUP BY country;

DISTINCT cannot do that. The rule of thumb: if you only want to remove duplicates, use DISTINCT. If you want one row per group with something calculated about it, use GROUP BY.

Picking one row per group

The question DISTINCT cannot answer is "give me one row per country, and I want the newest customer from each". That needs a window function:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at DESC) AS rn
  FROM customers
) x
WHERE rn = 1;

Window functions need MySQL 8.0 or later. On 5.7 this is genuinely awkward, which is part of why so much old code reaches for DISTINCT and hopes.

DISTINCT with ORDER BY

ORDER BY can only reference columns in the select list when DISTINCT is used:

SELECT DISTINCT a FROM t ORDER BY b;

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list,
references column 'd.t.b' which is not in SELECT list; this is incompatible with DISTINCT

The restriction makes sense once you see it: after deduplication, a single output row may correspond to several input rows with different b values, so there is no single b to sort by. The error is MySQL refusing to guess.

Performance

DISTINCT requires MySQL to compare rows, usually via a sort or a temporary table. Some notes worth knowing.

An index can eliminate the work entirely. If a B-tree index covers the distinct columns in order, MySQL can walk it and skip duplicates without sorting. EXPLAIN shows Using index for group-by in the good case.

Watch for Using temporary; Using filesort in EXPLAIN. On a large result set that means MySQL is buffering the whole thing to deduplicate, which is where DISTINCT queries fall over.

DISTINCT on a large SELECT * is almost always a mistake. Every column has to be compared, wide rows blow past tmp_table_size and spill to disk, and the reason for the duplicates is usually a join fanning out rows. Fix the join.

That last point is the important one. DISTINCT bolted onto a query that returns duplicates is often treating the symptom. If a join against a one-to-many table is producing duplicate parents, the honest fix is EXISTS:

-- Duplicates, then papered over
SELECT DISTINCT c.* FROM customers c JOIN orders o ON o.customer_id = c.id;

-- No duplicates in the first place
SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The second is clearer and usually faster, because it can stop at the first matching order rather than producing every match and then throwing them away.

Common problems

DISTINCT is not removing my duplicates. The rows differ in some column you are selecting, often an id. Check by selecting fewer columns. DISTINCT is exact, so a trailing space or a different case counts as different (subject to your collation).

I added a column and got more rows. Expected. More columns means more distinct combinations.

COUNT(DISTINCT x) and SELECT DISTINCT x disagree by one. NULL. The count ignores it, the select does not.

ERROR 3065 ... incompatible with DISTINCT. You sorted by a column you did not select. Add it to the select list, or use GROUP BY with an aggregate.

DISTINCT is slow. Check EXPLAIN for Using temporary. Consider whether a join is creating the duplicates you are removing.

Quick reference