What does SELECT DISTINCT do when you list more than one column?
DISTINCT removes duplicate rows, not duplicate values in one column. Two rows are duplicates only if every selected column is equal, and NULLs are treated as equal to each other.
Explain it simply
DISTINCT looks at the whole row you asked for. Imagine a list of (city, course) pairs from a fest registration. Two students from Pune who chose Robotics make one pair repeated. A student from Pune who chose Quiz is a different pair, so both stay.
Example
Table registrations:
| city | course |
|---|---|
| Pune | Robotics |
| Pune | Robotics |
| Pune | Quiz |
| Delhi | Quiz |
SELECT DISTINCT city FROM registrations;
-- Pune, Delhi (2 rows)
SELECT DISTINCT city, course FROM registrations;
-- Pune/Robotics, Pune/Quiz, Delhi/Quiz (3 rows)DISTINCT applies to the full column list. It is not DISTINCT(city), course; the brackets change nothing.
For DISTINCT, two NULLs count as the same value, even though NULL = NULL is not true in a WHERE clause.
What to say in the interview
"DISTINCT deduplicates entire result rows across all selected columns. It usually costs a sort or hash, so I use it deliberately and not to hide an accidental join duplicate."
Common mistakes
- Adding DISTINCT to fix duplicates caused by a wrong join condition, which hides the real bug.
- Expecting DISTINCT to apply only to the first column.