DISTINCT

Overview

DISTINCT removes duplicate rows from the result set. It operates on the entire selected row — two rows are duplicates only if every column value matches. In the logical query flow: FROM → WHERE → SELECT → DISTINCT → ORDER BY. DISTINCT runs after SELECT has picked the columns, comparing complete rows. SELECT DISTINCT category FROM products ORDER BY category; Products has 6 rows spanning 3 categories. Without DISTINCT you would get 6 rows (Electronics three times, Furniture twice, Stationery once). With DISTINCT, you get 3 rows: Electronics, Furniture, Stationery. DISTINCT on multiple columns deduplicates based on the combination: SELECT DISTINCT country, status FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY country, status; This returns only the unique (country, status) pairs, not unique countries or unique statuses separately. DISTINCT treats NULLs as equal to each other — if two rows both have NULL in a column, DISTINCT considers them duplicates on that column: SELECT DISTINCT country FROM customers ORDER BY country; Returns 5 rows: AE, JP, MX, UK, US — plus one NULL row for Emma. Even if multiple customers had NULL country, only one NULL row would appear. Mental model: DISTINCT is a row-level deduplication filter that runs after SELECT. It answers 'what unique combinations exist?' — not 'how many of each?' (that's GROUP BY + COUNT).

DISTINCT is essential for extracting the set of unique values — populating a dropdown of countries, listing which categories exist, or checking data quality by finding unexpected duplicates. Note that sorting or hashing rows to find duplicates adds performance overhead.

Where used: populating filter dropdowns in a UI, data quality checks for unexpected duplicates, analytics queries needing unique value lists

Why learn this

Building filter dropdowns (unique countries, categories, statuses) requires DISTINCT to avoid showing duplicates to the user
Understanding that DISTINCT compares entire rows prevents confusion when using it with multiple columns

Query walkthrough

SELECT DISTINCT category
FROM products
ORDER BY category;

Focus: Watch DISTINCT collapse 6 product rows into 3 unique category values — the duplicates vanish between SELECT and ORDER BY.

Common mistakes

Using DISTINCT when GROUP BY is needed: DISTINCT removes duplicate rows, but if you need a count per group, you need GROUP BY + COUNT. Writing SELECT DISTINCT category, COUNT(*) is a syntax error — use GROUP BY category instead.
Thinking DISTINCT applies to only the first column: DISTINCT operates on the whole row. SELECT DISTINCT country, status deduplicates on the (country, status) pair, not just country.
Assuming NULL values are not deduplicated: Unlike comparison operators (where NULL = NULL is NULL), DISTINCT treats two NULLs as equal. Multiple NULL rows collapse into one.

Glossary

duplicate: An exact copy of a row or piece of data that appears more than once in the results.
overhead: The extra work or processing time required by the database system to perform a specific task, like sorting data to find duplicates.
unique: Being the only one of its kind; a value or row that does not appear anywhere else in the results.

Recall questions

When you write SELECT DISTINCT col_a, col_b, what determines whether two rows are duplicates?
How does DISTINCT treat NULL values — as equal or not equal?
Where does DISTINCT fit in the logical query flow?

Understanding checks

How many rows come back, and what are they?

3 rows: Electronics, Furniture, Stationery.

Products has 6 rows across 3 categories (Electronics x3, Furniture x2, Stationery x1). DISTINCT collapses duplicates, keeping one row per unique category value.

What distinct statuses appear?

3 rows: cancelled, paid, pending.

The 8 orders have statuses: paid (5 times), pending (1), cancelled (1). DISTINCT removes the 4 duplicate 'paid' rows, leaving 3 unique values sorted alphabetically.

Practice tasks

Find unique countries with orders

This query lists all customer countries including duplicates. Modify it to return only the distinct countries by adding the DISTINCT keyword. Keep the ordering.

Challenge

Unique order statuses per customer country

Write a query that returns the distinct (country, status) pairs by joining customers and orders. Order by country, then status. This shows which order statuses exist in each country.

Continue learning

Previous: SELECT, FROM & Columns

Previous: WHERE & Operators

Return to SQL Roadmap