SQL DISTINCT: Remove Duplicate Rows
SELECT DISTINCT keeps one copy of each value. Turn forty customer rows into a unique list of cities, sort it, and find which customers ordered in December, on a sample online shop database.
Step by step
Unique values
- Each value once. To see which cities your customers live in, selecting the city column isn't enough. You get the same city again for every customer who lives there. DISTINCT keeps one copy of each value.
- The customers table. The sample online shop database has a customers table, with one row per customer and a city column.
Every city
- Every row. Start with a plain query: SELECT city FROM customers; Run it and you get 40 rows, one per customer. Norwich alone appears 7 times, so this is not a list of cities.
SELECT city FROM customers;
DISTINCT
- DISTINCT. Add DISTINCT straight after SELECT, so the first line reads SELECT DISTINCT city. Run it again and you get 13 rows: 12 cities plus 1 empty row, because one customer has no city. DISTINCT keeps one copy of the empty value too.
SELECT DISTINCT city
Sort it
- Sort the list. To sort the list, add ORDER BY city on a new line, before the semicolon. The cities now come back in alphabetical order. Here the empty value comes first, so it's easy to spot. Some databases put NULL values last instead.
ORDER BY city;
Who ordered
- Who ordered?. DISTINCT also answers questions like: which customers ordered in December? Start by selecting customer_id from the orders table, with WHERE order_date >= '2025-12-01' to keep only orders from 1 December 2025 onwards. That returns 12 rows, one per order. Some customers ordered more than once, so this is not a count of people. Add DISTINCT again, so the first line reads SELECT DISTINCT customer_id. Now there are 10 rows. The 12 December orders came from 10 different customers.
SELECT customer_id FROM orders WHERE order_date >= '2025-12-01';SELECT DISTINCT customer_id
Recap
- DISTINCT. DISTINCT goes right after SELECT and keeps one copy of each value. Empty values are grouped together as one. Use it to turn a long column into a clean list.
Common mistakes
- Expecting DISTINCT to apply to one column only. DISTINCT works on the whole row of the columns you select. SELECT DISTINCT customer_id, order_date returns one row for each customer and date pair, so a customer who ordered on 2 dates shows up twice.
- Counting rows instead of people. COUNT(*) on the December orders gives 12, the number of orders. Use COUNT(DISTINCT customer_id) to count different customers. Note that COUNT(DISTINCT) skips NULL values, so it doesn't count the empty one.
- Assuming DISTINCT sorts the result. DISTINCT doesn't guarantee any order, and the order can change between runs or databases. Add ORDER BY city whenever the order matters.
- Near-duplicates that DISTINCT keeps. Values like Norwich and norwich, or a city with an extra space, can count as different values, depending on the database's settings. Clean them in the query, for example with TRIM(city), or fix the data at the source.
When a business analyst uses this
Before a sign-off workshop, run SELECT DISTINCT requested_by FROM requirements ORDER BY requested_by; on the requirements log. You get a clean list of everyone who raised a requirement, which becomes your invite list, with no names repeated.
Questions people ask
Does SQL DISTINCT apply to all columns?
Yes. DISTINCT applies to the whole combination of columns after SELECT, not just the first one. A row is only removed when every selected column matches another row.
Does SQL DISTINCT include NULL values?
Yes. SELECT DISTINCT treats all NULLs as one value and shows them as a single empty row, like the customer with no city here. COUNT(DISTINCT column) is different: it leaves NULLs out of the count.
Does SQL DISTINCT keep the original order?
No. DISTINCT doesn't promise any order. Add ORDER BY, such as ORDER BY city, to get a sorted list.
