DISTINCT
SQL · Limiting Rows
Theory
DISTINCT removes duplicate rows from the result. It looks at the whole selected row, not at one column: SELECT DISTINCT dept returns each department once, while SELECT DISTINCT dept, city returns each unique (dept, city) pair. Two rows are duplicates only when every selected value matches.
For DISTINCT, NULLs count as equal to each other, so several NULLs collapse into one NULL row. DISTINCT works by sorting or hashing the rows, so it costs time on large data. It also appears inside aggregates: COUNT(DISTINCT city) counts unique cities. If you find yourself adding DISTINCT to hide duplicates caused by a join, the join is usually wrong and is worth fixing instead.
Why we use it
Data often repeats values: many employees share a department, many orders share a customer. DISTINCT gives the list of unique values or combinations, which is what you need for dropdowns, checks and counts.
When to use it
Use DISTINCT to list unique values or count them with COUNT(DISTINCT col). If you also need a count or total per value, use GROUP BY instead. Do not use it to patch duplicate rows from a bad join.
How it works
- Rows come out of SELECT
- Build a key from all selected columns of the row
- Key seen already? Yes: Drop the row as a duplicate. No: Keep the row and remember its key.
- Repeat for every row
- Return the unique rows
Syntax
SELECT DISTINCT column
FROM table;Example
SELECT DISTINCT department FROM employees;Key points
- Applies to the full selected row, not just the first column
- Useful for finding unique category values
- NULLs are treated as equal, so they collapse into one row
- COUNT(DISTINCT col) counts unique non-NULL values
- Needing DISTINCT after a join often signals a wrong join condition