SQL DISTINCT Explained: How to Actually Remove Duplicate Rows

DISTINCT removes duplicate rows. GROUP BY collapses rows into groups so you can calculate something about each group. People use them as if they’re interchangeable, and most of the time the query still runs — it just quietly returns the wrong number of rows, or hides a duplication problem that’s still sitting there underneath. Sorting that distinction out is the whole point of this post.

Below is a plain Q&A walkthrough covering what DISTINCT actually checks, where people get tripped up, and which situations call for something else instead.

What does DISTINCT actually do?

DISTINCT looks at the full set of columns in your SELECT list and removes any row that is a complete duplicate of another row already in the result. Not “duplicate in one column” — duplicate across every column you selected, all at once.

Say a table has a hundred rows but only forty unique combinations of the columns you selected. SELECT DISTINCT on those columns returns forty rows. Every value in every selected column has to match another row exactly for one of them to be dropped.

This matters because DISTINCT isn’t evaluating a single field in isolation. It’s comparing entire rows, as defined by whatever columns show up in your SELECT statement — nothing more, nothing less.

Why did I add DISTINCT and still get duplicate-looking rows?

This is the single most common DISTINCT complaint, and it almost always comes down to one thing: you selected more columns than you meant to compare.

Picture a table of orders where you want a unique list of customer names. If your query is SELECT DISTINCT customer_name, order_date FROM orders, and a customer placed three separate orders on three separate dates, you’ll get three rows for that customer — one per date. DISTINCT did exactly what it was told; it just wasn’t told what you actually meant.

The fix is narrowing your SELECT list down to only the columns you want uniqueness across. SELECT DISTINCT customer_name FROM orders, with order_date removed entirely, collapses that same customer down to a single row.

So the “duplicates” aren’t a DISTINCT failure at all. They’re a mismatch between the columns in your SELECT list and the columns you actually want deduplicated.

Does DISTINCT apply to one column or the whole row?

The whole row, always — even when your query only lists a single column, which is the case where this rule is easiest to miss because it looks like DISTINCT is just working on that one field.

Once you add a second column, DISTINCT’s scope silently expands to cover both columns together. SELECT DISTINCT country FROM customers checks uniqueness on country alone. SELECT DISTINCT country, city FROM customers checks uniqueness on the combination of country and city — meaning the same city name could appear multiple times in the result, as long as it’s paired with a different country each time.

If you’re picturing DISTINCT as something that operates column-by-column, that mental model breaks the moment your query has more than one column in the SELECT list. It operates on the row as a whole, every time.

What’s the actual difference between DISTINCT and GROUP BY?

DISTINCT removes duplicate rows. GROUP BY organizes rows into groups so you can run an aggregate calculation — COUNT, SUM, AVG — separately for each group. They can sometimes produce similar-looking output, but they’re solving different problems.

SELECT DISTINCT city FROM customers gives you a list of unique cities. SELECT city, COUNT(*) FROM customers GROUP BY city gives you that same list of unique cities, plus a count of how many customer rows belong to each one. If all you need is the unique list with no calculation attached, DISTINCT is simpler and usually clearer to read. The moment you need a number attached to each group — a count, a total, an average — you need GROUP BY, since DISTINCT has no mechanism for calculating anything; it only removes duplicates.

A rough rule that holds up well in practice: reach for DISTINCT when the question is “what are the unique values here?” and reach for GROUP BY when the question is “what are the unique values here, and what do I need to calculate about each one?”

Can DISTINCT be used with an aggregate function like COUNT?

Yes, and this combination solves a specific, common problem: counting unique values rather than counting every row.

COUNT() counts every row in your result, duplicates included. COUNT(DISTINCT column_name) counts only the unique values in that column, ignoring repeats. If an orders table has five hundred rows but only eighty distinct customers placed those orders, COUNT() returns 500 and COUNT(DISTINCT customer_id) returns 80.

This shows up constantly in reporting: “how many orders did we process this month” is a COUNT(*) question, while “how many distinct customers ordered this month” is a COUNT(DISTINCT customer_id) question. Mixing the two up is an easy mistake, and it produces a wrong number that still looks entirely plausible on a dashboard — nothing about it will look obviously broken.

Does DISTINCT slow a query down?

It can, and it’s worth understanding why instead of just accepting DISTINCT as a free correctness fix you can sprinkle on any query.

To find duplicates, the database typically has to sort or hash the entire result set so it can compare rows against each other. On a small table this cost is negligible. On a large table, or in a query that’s already doing significant work — joining several tables, filtering across millions of rows — that extra sort or hash step adds real overhead, and it’s overhead you’re paying for every single time the query runs.

The detail that trips people up: DISTINCT is frequently added as a patch after a join produces more rows than expected, rather than as a deliberate choice. If a join between orders and order_items is fanning out one order into several rows because each order has multiple line items, slapping DISTINCT onto the outer SELECT can mask that fan-out — but it doesn’t fix the underlying join, and it forces the database to do extra deduplication work on every execution to hide a structural problem that would be better solved at the query’s join logic itself.

When is DISTINCT the wrong tool entirely?

Three situations come up often enough to call out directly.

First: when you need a calculation per group, not just a unique list — that’s GROUP BY’s job, as covered above. Second: when duplicate rows are showing up because of a flawed join rather than genuine duplicate data — DISTINCT will hide the symptom, but the join condition is the actual thing that needs fixing. Third: when you only want to keep one row per group but need to control which row gets kept — say, the most recent order per customer rather than just any one of their orders. DISTINCT has no way to express “keep this specific row out of the duplicates.” That’s a job for a window function like ROW_NUMBER paired with a filter, since DISTINCT can’t prioritize one duplicate over another.

If your duplication problem involves choosing a specific row rather than just discarding exact repeats, DISTINCT isn’t the right layer to solve it at.

A quick reference for choosing between the options

The takeaway

DISTINCT does one job: it strips out rows that are complete duplicates across whatever columns you’ve selected. It doesn’t calculate anything, it doesn’t understand which duplicate you’d prefer to keep, and it isn’t a substitute for fixing a join that’s producing more rows than it should. Knowing exactly where that boundary sits is what turns DISTINCT from a keyword you reach for out of habit into a tool you reach for on purpose.

Are you trying to get a unique list, count unique values, or keep just one row per group? Tell me what your query currently returns versus what you’re expecting, and I can help you figure out whether DISTINCT, GROUP BY, or a window function is the right fit.