Lessons · SQL · why DISTINCT is slow
DISTINCT collapses duplicates
SELECT DISTINCT col returns each value once. It has to sort or hash the whole result to do it.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Filter dropdowns, 'which cities do we ship to', deduplicated exports.
How to think about it
Are the duplicates real, or did a join make them? Use it when duplicates are genuinely unwanted. If you find yourself adding DISTINCT to fix a join that produced duplicates, fix the join.
Worked example
SELECT DISTINCT rating FROM films ORDER BY rating;One row per rating.
Your turn
Each year once.
SELECT year FROM films;
Run a query against real tables
The trap
DISTINCT applies to the whole row, not one column: SELECT DISTINCT city, name keeps every distinct pair.
Practise why DISTINCT is slow on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.