Removing duplicates with DISTINCT

How DISTINCT works across one column and several, and what it costs.

What you'll be able to do

Use DISTINCT on single and multiple columns
Explain DISTINCT across a row combination
Recognise when DISTINCT is masking a join problem

Single column

SELECT DISTINCT city FROM customers;

Returns each city once.

Multiple columns

SELECT DISTINCT city, state FROM customers;

This is where people are caught out. DISTINCT applies to the whole row combination, not to each column separately. You get each unique city-and-state pair, so a city name appearing in two states appears twice.

There is no way to make DISTINCT apply to only one of several selected columns — that needs GROUP BY or a window function.

COUNT(DISTINCT ...)

SELECT COUNT(DISTINCT customer_id) FROM orders;

Counts unique non-null values. It is noticeably more expensive than a plain COUNT on large tables, since the engine must track what it has already seen.

When DISTINCT hides a bug

If you reached for DISTINCT because a query started returning duplicates, stop and check the joins first.

Duplicates usually mean a one-to-many join fanned the rows out. DISTINCT removes the visible symptom but any SUM in that query is still double-counted — and no longer obviously wrong.

Key points

DISTINCT applies to the whole selected row, not per column
COUNT(DISTINCT ...) is expensive on large tables
Unexpected duplicates usually mean a join problem, not a DISTINCT problem

Check yourself