What Does DISTINCT Deduplicate?

DISTINCT removes duplicate rows from the result set, and a row here means the whole projected row. Two rows are duplicates if every column in the projection has the same value in both. That means the comparison is over the entire select list, not over the column you happen to be looking at.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL,
  tier VARCHAR(20) NOT NULL
);

INSERT INTO customers (id, name, city, tier) VALUES
  (1, 'Ada Lovelace',   'London',    'gold'),
  (2, 'Grace Hopper',   'New York',  'gold'),
  (3, 'Alan Turing',    'London',    'gold'),
  (4, 'Katherine Johnson', 'Hampton', 'gold');

-- These four rows are all distinct: the names differ.
SELECT name, city FROM customers;

-- These two rows are the same pair, so one survives.
SELECT DISTINCT city, tier FROM customers ORDER BY city;

The second query returns three rows: London with gold, New York with gold, and Hampton with gold. The two London-and-gold rows collapsed into one, while New York and Hampton appeared once each already, so they survived unchanged. Note what the comparison is over: the pair, not the city alone. Had London appeared once with silver and once with gold, both would have been kept, because the full rows differ.

Two consequences follow. First, DISTINCT on a projection containing id does nothing at all, because a primary key is already unique — a common confusion when someone expects DISTINCT to dedupe by name. Second, DISTINCT compares NULLs as equal to each other, so rows that differ only in a nullable column collapse into one, and the surviving row keeps one of the NULLs.

The other thing to know is cost. DISTINCT forces the engine to compare rows, which usually means a sort or a hash aggregate over the whole result, and it happens after the projection. If a query is unexpectedly slow, the usual culprit is a DISTINCT added to silence a join that was producing duplicates it should have been aggregating instead.

Project only the column you care about, then apply DISTINCT. If you leave other columns in the projection, DISTINCT stops collapsing rows because the full row has to match.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Ada Lovelace', 'London'),
  (2, 'Grace Hopper', 'New York'),
  (3, 'Alan Turing',  'London'),
  (4, 'Katherine Johnson', 'Hampton'),
  (5, 'Joan Clarke', 'New York');

SELECT DISTINCT city
FROM customers
ORDER BY city;

Three rows come back — Hampton, London and New York — from five customer rows. That is the whole point of the projection: SELECT DISTINCT city compares only the city column, so the three London and New York duplicates collapse.

If you had written SELECT DISTINCT * instead, every row would be unique because of the id, and you would get five rows back with no deduplication at all. This is the most common reason somebody reports that DISTINCT "does not work".

Two extra notes. The ORDER BY column has to appear in the select list when the engine insists on it, and city does, so no problem here — but SELECT DISTINCT city FROM customers ORDER BY name is rejected by several engines for exactly that reason. And if the city column can contain NULL, the NULL forms its own group and appears once in the output; add WHERE city IS NOT NULL if you do not want it there.

Almost always because the rows differ in a column you are not displaying. A report that projects three of five columns cannot see a difference in the other two, so two rows that look the same on screen are genuinely distinct rows to DISTINCT.

The debugging technique is to widen the projection one step at a time. Run the query without DISTINCT and compare the raw rows. If they differ in a column you did not select, the fix is either to add that column to the output or to include it in the grouping.

The other common causes are specific. A join that is one-to-many produces more rows than you expected — a customer with three orders becomes three customer rows — and adding DISTINCT hides the symptom while the aggregate you wanted was the real answer. A UNION of two sets with overlapping rows keeps the duplicates unless you use UNION ALL deliberately. A window function with no PARTITION BY collapses nothing and duplicates nothing, but a correlated subquery in the select list can multiply rows if it is not aggregated. And NULL values in a nullable column will not match with an equality comparison, which looks like duplication from the other direction.

The disciplined way to find it is to write the query in two stages. First, select the full row without DISTINCT and look at what differs. Second, decide deliberately what defines identity for this result: if it is a business key, group by that key; if it is genuinely a whole-row comparison, then DISTINCT is right and the duplicates you are seeing contain a difference worth showing.

One more caution: do not reach for DISTINCT as a performance or correctness band-aid. It hides fan-out from a join, and the fan-out is usually the real bug. Removing it after fixing the join often makes the query both correct and faster.