Lesson 6 of 20
In progress
DISTINCT: unique values
Remove duplicate rows from a result, understand what DISTINCT acts on, and know when GROUP BY is the better tool.
DISTINCT answers a simple question: which values appear, without repeats? It is one keyword and it has three ways to surprise you.
What you will learn
- Use
DISTINCTon one column and on several. - Understand what it compares.
- Recognise the cases where
GROUP BYis clearer.
The basic form
Editable. Runs in your browser.
INSERT INTO visits (id, visitor, page, day) VALUES
(1, 'ana', '/pricing', '2025-04-01'),
(2, 'ravi', '/pricing', '2025-04-01'),
(3, 'ana', '/docs', '2025-04-01'),
(4, 'meera', '/pricing', '2025-04-02'),
(5, 'ana', '/pricing', '2025-04-02');
SELECT DISTINCT page FROM visits;
Press Run to see the output.
Three rows become two: /pricing and /docs. The result is a set of values, and by itself it has no meaningful order; add ORDER BY if you need one.
What DISTINCT compares
DISTINCT applies to the whole selected row, not to the first column. Add a second column and the combinations change:
Editable. Runs in your browser.
Press Run to see the output.
Now ana, /pricing and ana, /docs are different rows, so the result has four combinations. If you expected two visitors, you asked the wrong question — and DISTINCT will not tell you.
That is the most common misuse: writing SELECT DISTINCT a, b, c and expecting one row per a. What you want is a grouping decision.
DISTINCT and NULL
DISTINCT treats all NULLs as equal, so a column with five NULLs contributes one NULL row:
Editable. Runs in your browser.
Press Run to see the output.
No rows, because no visit happened that day. Add a nullable column and the rule shows up: one NULL survives no matter how many rows hold it.
Counting distinct values
Editable. Runs in your browser.
Press Run to see the output.
COUNT(DISTINCT column) counts unique non-null values. It is the standard way to answer "how many different customers ordered" rather than "how many orders".
COUNT(*) with a subquery gives the same number and is sometimes clearer:
Editable. Runs in your browser.
Press Run to see the output.
DISTINCT versus GROUP BY
Editable. Runs in your browser.
Press Run to see the output.
GROUP BY visitor produces one row per visitor and allows an aggregate next to it. DISTINCT cannot: the moment you add COUNT(*) the query becomes an aggregate query and DISTINCT stops meaning what you thought.
Two rules that keep this straight:
- Need the list of unique values?
DISTINCT. - Need a number per group?
GROUP BY.
GROUP BY also wins on performance in many engines because the optimizer recognises the grouping, while DISTINCT can force a sort or a temporary table.
A practical use: filter options
Editable. Runs in your browser.
Press Run to see the output.
This is the query behind a filter dropdown: the pages that were actually visited, alphabetically. The ORDER BY matters for the interface; a list that shuffles between loads looks broken.
When DISTINCT hides a bug
If a query returns duplicates and adding DISTINCT makes the numbers look right, the duplicate is usually a join multiplying rows. Removing the symptom hides a modelling problem: a one-to-many join counted twice.
Before reaching for DISTINCT, ask whether the duplicates are real. If two orders exist, two rows are correct, and DISTINCT would undercount them. If a join produced two identical rows, fix the join.
The cost of deduplicating
Most engines implement DISTINCT by sorting or hashing the selected columns, so the work grows with the result size. On a large table that is often the slowest part of the query, and it is invisible in the SQL because one keyword looks cheap. On a small table the difference is unmeasurable, which is why the cost only appears in production.
Before adding it, check whether the duplicates are avoidable: a join that multiplies rows, a missing WHERE, or a table that repeats values where a reference table belongs. If the duplicates are real, DISTINCT is doing its job and the cost is the price of the answer. If they are not, removing the cause is faster and more correct than deduplicating afterwards.
Assuming SELECT DISTINCT city, country gives one row per country. It gives one row per distinct pair, so two cities in the same country produce two rows.
List the pages visited on or after 2025-04-02 in alphabetical order, then count how many different visitors appear overall.
Show solution
Editable. Runs in your browser.
SELECT COUNT(DISTINCT visitor) AS visitors
FROM visits;
Press Run to see the output.
The first query returns each page once, filtered to the later dates. The second counts unique visitors with COUNT(DISTINCT ...), which ignores repeats and nulls.
Recap
DISTINCTdeduplicates the whole selected row, not the first column.COUNT(DISTINCT column)counts unique non-null values.- Use
GROUP BYwhen you need an aggregate; useDISTINCTfor a plain list.
Next, the aggregate functions that turn rows into a single number.
Is DISTINCT faster than GROUP BY?
Does DISTINCT preserve order?
Progress is saved in this browser only. No account, no email.