SELECT DISTINCT in SQL: Removing Duplicate Rows from Results

DISTINCT removes duplicate rows from a query result. That sentence contains the one thing people get wrong about it: it works on rows, not on columns. Once that clicks, the surprising results stop being surprising.

This page covers the single-column case, the multi-column case that catches everyone, counting distinct values, and the two situations where you should reach for GROUP BY instead. Every example runs against the sample database.

One column: the easy case

The products table has seven rows but fewer categories, because several products share one:

SELECT category FROM Products;

Add DISTINCT immediately after SELECT and each category appears once:

SELECT DISTINCT category FROM Products;

Note where it goes. DISTINCT is not a function you wrap around a column — there is no DISTINCT(category) in SQL, even though it is often written that way in blog posts. It is a keyword that modifies the whole SELECT list, which is exactly why the next section behaves as it does.

Two columns: the case that trips everyone

Here is the mistake, in the form it usually takes. Someone wants one row per country and writes:

SELECT DISTINCT country, city FROM Customers;

Every row comes back. Not because DISTINCT failed, but because it did precisely what it says: it removed duplicate rows, and each country/city pair is unique even though the countries are not. Compare it with the single-column version:

SELECT DISTINCT country FROM Customers;

Fewer rows, because now the row is the country. The rule to carry away: DISTINCT de-duplicates the combination of everything in the SELECT list. Adding a column can only ever increase the number of rows it returns, never decrease it.

Counting distinct values

COUNT(DISTINCT column) counts how many different values exist, which is a different question from how many rows exist:

SELECT COUNT(*)                 AS customers,
       COUNT(DISTINCT country)  AS countries
FROM Customers;

This is one of the most useful things in reporting SQL. When a table has one row per event, COUNT(*) counts events and COUNT(DISTINCT customer_id) counts the people behind them — confusing the two is how “active users” numbers get overstated:

SELECT COUNT(*)                      AS orders,
       COUNT(DISTINCT customer_id)   AS customers_who_ordered
FROM Orders;

The aggregate functions guide covers the rest of the family, including why COUNT(*) and COUNT(column) disagree when NULLs are involved.

DISTINCT and NULL

For sorting and comparison, NULL is famously not equal to itself — but DISTINCT treats all NULLs as one value, so a column full of missing data yields a single NULL row:

SELECT DISTINCT manager_id FROM Employees;

One of those rows is NULL, standing in for every employee with no manager. That is the standard behaviour and it is what you almost always want, but it is worth knowing because it is inconsistent with =: two NULLs are “the same” to DISTINCT and “unknown” to a comparison. See IS NULL for the comparison side of that story.

DISTINCT with ORDER BY

Sorting works as usual, with one restriction:

SELECT DISTINCT country
FROM Customers
ORDER BY country;

You can only sort by something that survives the de-duplication. ORDER BY signup_date on that query is rejected by most databases, because after the duplicates are removed there may be several signup dates per country and no single value to sort by. If you need that, you need GROUP BY with an aggregate — which brings us to the last section.

DISTINCT or GROUP BY?

These two queries return exactly the same rows:

SELECT DISTINCT category FROM Products;

SELECT category FROM Products GROUP BY category;

On a modern database they usually produce the same plan too, so the choice is about intent. Use DISTINCT when you want a de-duplicated list and nothing else. Use GROUP BY the moment you want anything computed per group:

SELECT category,
       COUNT(*)          AS products,
       MIN(unit_price)   AS cheapest,
       MAX(unit_price)   AS dearest
FROM Products
GROUP BY category
ORDER BY products DESC;

That is the query DISTINCT can never write, and it answers the “which city?” problem from earlier too — pick one deliberately with an aggregate rather than hoping DISTINCT guesses:

SELECT country,
       COUNT(*)  AS customers,
       MIN(city) AS first_city_alphabetically
FROM Customers
GROUP BY country
ORDER BY customers DESC, country;

That last row matters: DISTINCT changes what a query returns, never what the table contains. To fix the data itself, see finding and deleting duplicate rows.

Performance, briefly

DISTINCT is not free. To know whether a row is a duplicate the database has to compare it against the rows it has already produced, which means sorting or hashing the whole result. Two practical consequences:

• Select fewer columns. Every extra column makes the comparison wider and duplicates rarer — often the reason a DISTINCT “stopped working” is that someone added a column.
• A DISTINCT that fixes a join is a warning sign. If a query needs DISTINCT to get sensible numbers, the join is probably multiplying rows. Fix the join, or aggregate deliberately; papering over it with DISTINCT hides a bug and will still be wrong once someone selects an extra column.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Which countries do we sell to?Easy
• Look at the whole product catalogueEasy
• How many customers do we have?Easy
• Revenue by product categoryHard

See all 42 exercises →

Frequently asked questions

Related reading

• The SELECT statement: a beginner’s guide
• GROUP BY vs HAVING: which filter runs when
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX
• Find and delete duplicate rows
• UNION vs UNION ALL: the other de-duplicator