SQL DISTINCT: How to Remove Duplicate Rows (and Its Traps)
SQL DISTINCT removes duplicate rows from a query result. Learn how it works on one and many columns, how it treats NULLs, COUNT DISTINCT, DISTINCT vs GROUP BY, and the traps that make it hide real bugs, with every query run and its output shown.
- SQL DISTINCT removes duplicate rows from the result, comparing every selected column together.
DISTINCTtreats all NULLs as one value, butCOUNT(DISTINCT col)ignores NULLs completely.- If you need DISTINCT to fix a join, the join is usually wrong.
EXISTSis often the cleaner fix. - An index on the column made DISTINCT about 6 times faster on 500,000 rows in our test.
You run a report, see the same customer listed three times, and type SELECT DISTINCT. The duplicates vanish and the report looks right. Sometimes that is the correct fix. Often it is a bandage over a bad join or a misunderstanding of the data. This guide shows exactly what SQL DISTINCT does, where it shines, and the traps that make it return confidently wrong answers.
Every query below was run on SQLite 3.50.4 and the output is copied from the run. DISTINCT is standard SQL, so the same queries work in MySQL, PostgreSQL, SQL Server and Oracle; we flag the few places where databases differ.
What does DISTINCT do in SQL?
DISTINCT goes right after SELECT and tells the database to return each unique row only once:
SELECT DISTINCT column1, column2
FROM table_name
WHERE condition;
It runs late in the query: after FROM, WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. That order explains most of its behavior: it de-duplicates whatever rows the rest of the query produced, using only the columns you selected.
The sample data
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, city TEXT,
product TEXT, amount INT);
INSERT INTO orders (customer, city, product, amount) VALUES
('Asha','Pune','Laptop',900), ('Ravi','Delhi','Mouse',20),
('Asha','Pune','Mouse',20), ('Meera','Pune','Laptop',900),
('Ravi','Delhi','Mouse',20), ('Kabir',NULL,'Desk',300),
('Lina',NULL,'Lamp',40), ('Meera','pune','Lamp',40);
Eight orders, with deliberate problems: a repeated order from Ravi, two missing cities, and one city typed in lower case.
DISTINCT on one column
SELECT DISTINCT city FROM orders;
-- city
-- Pune
-- Delhi
-- NULL
-- pune
Eight rows became four. Notice two things. First, the two NULLs collapsed into one row. Second, Pune and pune are different values here, because SQLite compares text case-sensitively by default. MySQL with its usual case-insensitive collation would treat them as one. Clean the data, or use DISTINCT LOWER(city), when case should not matter.
DISTINCT on multiple columns
SELECT DISTINCT customer, city FROM orders ORDER BY customer;
-- Asha | Pune
-- Kabir | NULL
-- Lina | NULL
-- Meera | Pune
-- Meera | pune
-- Ravi | Delhi
With several columns, DISTINCT looks at the combination. Asha appears once because both orders from Asha are Asha plus Pune. Meera appears twice because the city spelling differs. A row is removed only when every selected column matches another row.
The DISTINCT(column) myth
Many people write SELECT DISTINCT(city), amount and expect unique cities. We tested it on a small table:
SELECT DISTINCT(city), amount FROM t;
-- Pune | 5
-- Delhi | 9
-- Pune | 1
Pune still appears twice. DISTINCT is not a function; the parentheses just wrap the first column, and de-duplication still applies to the whole row. To get one row per city with an amount, decide which amount you want and use GROUP BY city with MAX(amount) or similar.
How DISTINCT handles NULL
In most SQL comparisons, NULL is not equal to NULL. DISTINCT is an exception: for de-duplication, all NULLs count as the same value, so you get one NULL row, as our city query showed. GROUP BY behaves the same way. If NULL in your result is noise, filter it out with WHERE city IS NOT NULL. Our SQL NULL values guide covers the other places NULL behaves differently.
COUNT DISTINCT
SELECT COUNT(*) AS all_rows,
COUNT(city) AS non_null,
COUNT(DISTINCT city) AS distinct_cities,
COUNT(DISTINCT LOWER(city)) AS distinct_ci
FROM orders;
-- all_rows | non_null | distinct_cities | distinct_ci
-- 8 | 6 | 3 | 2
This one query shows four different answers to “how many cities?”. COUNT(DISTINCT city) returned 3, not 4, because unlike SELECT DISTINCT, aggregate functions skip NULLs entirely. Folding case brings it down to 2, the true number of cities. Be precise about which count your report means.
DISTINCT vs GROUP BY
SELECT city, COUNT(*) AS n FROM orders GROUP BY city;
-- NULL | 2
-- Delhi | 2
-- Pune | 3
-- pune | 1
SELECT DISTINCT city and SELECT city ... GROUP BY city return the same set of values, and many optimizers plan them alike, although in our SQLite timing test below GROUP BY was slower without an index. The main difference is intent. Use DISTINCT when you only want unique values. Use GROUP BY when you also need an aggregate per group, such as a count or a total. Our GROUP BY and HAVING guide goes deeper.
The SUM DISTINCT trap
SELECT SUM(amount) AS sum_all, SUM(DISTINCT amount) AS sum_distinct
FROM orders;
-- sum_all | sum_distinct
-- 2240 | 1260
SUM(DISTINCT amount) adds each unique amount once: 900 + 20 + 300 + 40. It does not remove duplicate orders. Two different customers who both spent 900 get merged, and your revenue drops by almost half. If you want to exclude duplicate orders, de-duplicate the rows first, then sum.
The join trap: DISTINCT hiding a bug
Add a tags table where one product has two tags:
CREATE TABLE tags (product TEXT, tag TEXT);
INSERT INTO tags VALUES ('Laptop','tech'), ('Laptop','sale'), ('Mouse','tech');
SELECT o.customer FROM orders o
JOIN tags t ON t.product = o.product
WHERE t.tag IN ('tech','sale') ORDER BY 1;
-- Asha, Asha, Asha, Meera, Meera, Ravi, Ravi
The join multiplies rows: every laptop order matches two tags. Adding DISTINCT makes the list look right, but any SUM or COUNT built on that join is still inflated. When you only need to know whether a match exists, use EXISTS, which never multiplies rows:
SELECT DISTINCT o.customer FROM orders o
WHERE EXISTS (SELECT 1 FROM tags t
WHERE t.product = o.product AND t.tag IN ('tech','sale'))
ORDER BY 1;
-- Asha, Meera, Ravi
Here DISTINCT only removes customers with several orders, which is what we actually want. See EXISTS vs IN and SQL joins explained for more.
Tip: when you reach for DISTINCT, first ask why duplicates appeared. If the answer is a join, fix the join.
Keeping one full row per duplicate
DISTINCT can only return the columns it compares. To delete or report true duplicates while keeping an ID, number the copies with a window function:
SELECT id, customer, product
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer, product
ORDER BY id) AS rn
FROM orders) AS d
WHERE rn = 1 ORDER BY id;
-- 1 Asha Laptop | 2 Ravi Mouse | 3 Asha Mouse | 4 Meera Laptop
-- 6 Kabir Desk | 7 Lina Lamp | 8 Meera Lamp
Ravi’s repeat order, id 5, is gone, and every kept row still has its ID. That is the starting point for deleting duplicate rows in SQL; ROW_NUMBER explains the window function. PostgreSQL also offers DISTINCT ON (customer, product) for the same job; we did not run that one.
DISTINCT and ORDER BY
Sorting by a column you did not select is ambiguous with DISTINCT: if Pune has amounts 5 and 1, which one decides its position? SQLite accepted SELECT DISTINCT city FROM t ORDER BY amount and simply picked one. PostgreSQL rejects it with an error that ORDER BY expressions must appear in the select list (not run here; check your database). Sort by a selected column or an aggregate to keep results predictable.
DISTINCT performance
We loaded 500,000 events for 5,000 users and timed each query (best of three):
| Query | No index | Index on user_id |
|---|---|---|
SELECT DISTINCT user_id |
86.6 ms | 13.8 ms |
SELECT user_id ... GROUP BY user_id |
143.8 ms | 19.0 ms |
SELECT COUNT(DISTINCT user_id) |
85.3 ms | 12.7 ms |
Without an index, SQLite scanned the table and built a temporary B-tree to track seen values. With one, the plan switched to a covering-index scan and DISTINCT ran about 6 times faster. On large tables, de-duplicating needs sorting or hashing, so select only the columns you need and index the ones you de-duplicate often. Our SQL indexes guide explains why.
SQL DISTINCT checklist
- Select only the columns that define a duplicate.
- Decide whether NULL should appear, and whether case matters.
- Never use
SUM(DISTINCT ...)to remove duplicate rows. - If a join caused the duplicates, fix it with
EXISTSor a pre-aggregated subquery. - Use
ROW_NUMBERwhen you need to keep a full row or an ID. - Prefer
UNION ALLoverUNIONwhen duplicates are impossible;UNIONruns an implicit DISTINCT (see UNION vs UNION ALL).
Final thoughts
SQL DISTINCT is simple: it removes rows that match on every selected column. The skill is knowing when that is the right fix. Use it for unique value lists and COUNT DISTINCT, watch how it treats NULLs and letter case, and treat a DISTINCT added to make a join look right as a warning sign. Check the cause of duplicates first, and your reports will be both clean and correct.
Frequently asked questions
Does DISTINCT apply to all columns in SQL?
Yes. DISTINCT compares the full row of selected columns. A row is removed only when every selected column matches another row, even if you wrap one column in parentheses.
Does SELECT DISTINCT include NULL values?
Yes. All NULLs are treated as one value, so the result contains a single NULL row. COUNT DISTINCT is different and ignores NULLs completely.
Is DISTINCT faster than GROUP BY?
Often they perform about the same. In our SQLite test DISTINCT took 86.6 ms and GROUP BY 143.8 ms on 500,000 rows, but adding an index made both several times faster, a much bigger win.
How do I count distinct values in SQL?
Use COUNT(DISTINCT column). It counts unique non-NULL values. To count unique combinations, count rows from a subquery that selects DISTINCT on those columns.
Why does DISTINCT still show duplicates?
Usually another selected column differs, such as an ID, a timestamp or a value with different letter case or spaces. Select only the columns that define a duplicate, or clean the data first.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.