DISTINCT goes right after SELECT and removes duplicate result rows. In the logical order it runs after SELECT has computed the columns and before ORDER BY sorts them. The opposite, SELECT ALL, is the default and is never written.

DISTINCT applies to the whole row

DISTINCT is not a function of the first column; it compares entire result rows:

SELECT DISTINCT city, country FROM customers ORDER BY country, city;

returns each (city, country) pair once. Pune, India appears once however many customers live there; if another country also had a Pune, that pair would be a second row. A common mistake is writing SELECT DISTINCT city, name and expecting one row per city — every different name makes a different row, so the duplicates come back.

The parenthesised form is misleading:

SELECT DISTINCT(city), name FROM customers;

looks as if it applies to city only. It does not: the parentheses just surround the expression city, and DISTINCT still applies to (city, name). Never write it that way. (PostgreSQL has DISTINCT ON (col) for "one row per value of col", which MySQL lacks; there you would use GROUP BY or a window function, both later in the course.)

DISTINCT on expressions

DISTINCT compares the computed columns, so you can de-duplicate a derived value:

SELECT DISTINCT YEAR(order_date) AS order_year
FROM orders
ORDER BY order_year;

NULLs collapse into one

For DISTINCT, two NULLs count as the same value — the one place in SQL where NULLs are treated as equal. A column with NULL in five rows contributes a single NULL row:

SELECT DISTINCT dept FROM employees ORDER BY dept;

In MySQL, NULL sorts first in ascending order (and last in descending), which is why it heads the list. PostgreSQL and Oracle do the opposite by default — NULLs last ascending — and offer NULLS FIRST/NULLS LAST to choose; MySQL does not have that syntax.

Counting distinct values

How many different cities do customers come from? COUNT(DISTINCT col):

SELECT COUNT(*)             AS customers,
       COUNT(city)          AS with_city,
       COUNT(DISTINCT city) AS cities
FROM customers;

Three different counts, three different questions:

• COUNT(*) — rows, duplicates and NULLs included;
• COUNT(city) — rows where city is not NULL;
• COUNT(DISTINCT city) — different non-NULL values. NULL is not counted, even though SELECT DISTINCT city shows it as a row.

That last difference — SELECT DISTINCT shows one NULL row, COUNT(DISTINCT) ignores NULL — is a favourite interview "what does this return?". MySQL also accepts several columns, COUNT(DISTINCT city, country), counting distinct pairs that have no NULL in them; that form is not standard.

DISTINCT and ORDER BY

Because DISTINCT merges rows, ORDER BY after it may only use what survived — the selected columns (or expressions of them):

SELECT DISTINCT city FROM customers ORDER BY joined;   -- refused

Which joined should Pune be sorted by, when forty customers in Pune joined on forty dates? MySQL 8 refuses the query (Expression #1 of ORDER BY clause is not in SELECT list), as do PostgreSQL and SQL Server. Order by a selected column, or use GROUP BY city ORDER BY MIN(joined) to say which date you mean.

DISTINCT versus GROUP BY

These return the same rows:

SELECT DISTINCT city FROM customers;
SELECT city FROM customers GROUP BY city;

and MySQL usually runs them the same way — by sorting or hashing the values. The difference is purpose. GROUP BY exists to compute something per group (COUNT(*), SUM(amount)); DISTINCT only removes duplicate output rows. Use DISTINCT to list unique values, GROUP BY to summarise; the aggregation module takes GROUP BY from there.

DISTINCT is not free, and not a fix

Removing duplicates means sorting or hashing the whole result — on a large result that is real work and memory. It is worth it when the question is "which different values?". It is a smell when it is added because "the query returned duplicates": usually a join matched more rows than intended, and DISTINCT hides the symptom while the query still does the extra work and may hide wrong totals. When you reach for DISTINCT to fix a result, first ask why the duplicates are there.

Another subtle case: DISTINCT is applied before LIMIT, so SELECT DISTINCT city … ORDER BY city LIMIT 3 gives three different cities.

Key takeaways

• SELECT DISTINCT removes duplicate result rows; it runs after SELECT, before ORDER BY and LIMIT.
• It applies to all selected columns together; DISTINCT(col) is just DISTINCT col.
• NULLs collapse into one row; MySQL sorts NULL first ascending.
• COUNT(*) rows, COUNT(col) non-NULL values, COUNT(DISTINCT col) different non-NULL values.
• After DISTINCT, ORDER BY may only use selected columns.
• Same rows as GROUP BY on those columns; GROUP BY is for aggregates.
• Don't use DISTINCT to hide duplicates a join created — find the cause.