DISTINCT Clause

DISTINCT de-duplicates a result set by first grouping rows that are equal on the selected columns — and the engine can only group efficiently by sorting the rows (so equals land next to each other) or by hashing them into buckets, which is why DISTINCT is never free: it forces an extra blocking operator that must see every row before it can emit the first unique one.

The keyword is logically applied to the entire projected row, after FROM/WHERE/GROUP BY have produced rows but before ORDER BY and LIMIT. So SELECT DISTINCT a, b never means "distinct a, any b" — it collapses rows that match on the full tuple (a, b). There is no such thing as DISTINCT on one column inside a multi-column select.

Worked example: trace the hash-aggregate

Take a sales table and ask for the distinct regions a product sold in. The engine reads rows one at a time, computes a hash of the projected tuple, and probes a hash table; a tuple it has seen before is discarded, a new one is inserted and emitted.

Source rows (in physical/heap order — note they are not sorted):

Running SELECT DISTINCT product, region FROM sales; the hash-aggregate processes each row against the hash table of tuples seen so far:

Final result: 4 rows — (Pen,EU), (Pen,US), (Ink,US), (Ink,NULL). The two (Ink, NULL) rows collapsed into one: for grouping/DISTINCT, two NULLs are treated as equal, even though NULL = NULL is UNKNOWN in a WHERE clause. This is the single most surprising rule on this page.

DISTINCT vs GROUP BY: the same operator

These two queries are logically equivalent and almost always produce the identical physical plan, because the optimizer reduces both to "group by all selected columns":

SELECT DISTINCT product, region FROM sales;
SELECT product, region FROM sales GROUP BY product, region;

The difference is expressive power, not cost. GROUP BY lets you keep aggregates (COUNT(*), MAX(...)) per group; DISTINCT cannot. Reach for GROUP BY the moment you need a count per group:

-- "how many sales per (product, region)?" — DISTINCT cannot express this
SELECT product, region, COUNT(*) AS n
FROM sales
GROUP BY product, region;

Why the naive instinct is wrong

A common buggy mental model is that DISTINCT applies to the column it sits next to. People write this expecting "one row per product, with whatever region":

-- WRONG mental model: this does NOT mean "distinct product"
SELECT DISTINCT product, region FROM sales;

It de-duplicates the pair, so Pen still appears twice (once per region). DISTINCT is a row-level operator — it binds to the whole projection, not to the adjacent identifier. The other classic error is treating COUNT(DISTINCT col) as cheap; it forces its own de-duplication pass over col and is one of the most common causes of a query that is fast in dev and times out in prod once col has millions of values that no longer fit in the work-memory hash table.

Pitfalls

• DISTINCT defeats early termination. SELECT DISTINCT … LIMIT 10 still scans and de-duplicates the entire input first — the de-dup operator is blocking, so LIMIT only trims the already-materialized result. It is not a cheap "first 10 distinct values".
• It silently hides a sloppy join. Sprinkling DISTINCT to make duplicate rows from a fan-out join disappear masks the real bug — a missing join predicate or a one-to-many relationship you forgot about. The right fix is usually a correct join or an EXISTS subquery, not DISTINCT.
• NULL semantics flip. In WHERE, NULL = NULL is UNKNOWN and filters the row out; under DISTINCT/GROUP BY, all NULLs are folded into a single group. Same value, opposite behavior depending on the clause.
• Memory spills. The hash-aggregate needs RAM proportional to the number of distinct tuples. Exceed the engine's work memory (work_mem in Postgres, the join/sort buffer in MySQL) and it spills to a temp file on disk — a sudden 10–100× slowdown that does not show up on small test data.
• DISTINCT + ORDER BY on a non-selected column is illegal. You can only ORDER BY expressions that appear in the SELECT DISTINCT list, because after de-duplication the engine no longer has the dropped columns to sort on.

Takeaways

• DISTINCT is a blocking de-duplication operator implemented as a hash-aggregate or a sort + unique pass — it always reads the full input and costs CPU and memory; it is never a free annotation.
• It binds to the entire projected tuple, not the adjacent column; SELECT DISTINCT a, b means distinct (a, b) pairs.
• DISTINCT and GROUP BY on the same columns are the same plan; choose GROUP BY the instant you need per-group aggregates.
• Under de-duplication, NULLs group as equal — the opposite of WHERE three-valued logic. And reaching for DISTINCT to silence duplicate rows is usually a sign of a wrong join.

Sources: PostgreSQL documentation — SELECT (DISTINCT / DISTINCT ON) and the HashAggregate/Unique plan nodes; MySQL 8.0 Reference Manual — "DISTINCT Optimization" and "GROUP BY Optimization"; the SQL:2016 standard's treatment of NULL grouping in §grouping operations. Re-authored and deepened for this guide: replaced the definition-plus-trivial-example page and the opaque image placeholders with the sort/hash mechanism, a step-by-step de-duplication trace, the DISTINCT↔GROUP BY equivalence, multi-column and NULL-grouping behavior, and engineer-facing cost pitfalls.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — DISTINCT Clause

Why this concept exists (judgment chain)

DISTINCT is a blocking de-duplication operator (hash aggregate or sort+unique) over the full projected tuple — not “distinct on the adjacent column.” It costs memory proportional to cardinality and is often a bandage over a wrong join fan-out.

Worked example with numbers or traced steps

sales rows: (Pen,EU)×2, (Pen,US)×2, (Ink,US), (Ink,NULL)×2
SELECT DISTINCT product, region → 4 rows:
  (Pen,EU),(Pen,US),(Ink,US),(Ink,NULL)
NULLs collapse under DISTINCT (unlike WHERE NULL=NULL).
Plan: HashAggregate reads ALL rows before LIMIT can help.
Equivalent: GROUP BY product, region (same plan; GROUP BY allows COUNT).

When NOT to use / named alternative

Do not use DISTINCT to hide cartesian/join fan-out — fix the join or use EXISTS/semi-join. Prefer GROUP BY when you need aggregates. Prefer UNIQUE constraints to prevent dups at write time. Skip DISTINCT on already-unique keys (PK) — pure waste.

Failure / ops fingerprint

Fingerprint: SELECT DISTINCT after multi-join with inflated COUNT; work_mem spills on large DISTINCT; LIMIT 10 still scans full table. Ops: EXPLAIN for HashAggregate/Unique; raise work_mem carefully; replace DISTINCT with EXISTS where intent is “has any related row.”

Hostile-panel drills (defend the decision)

Q1. Does DISTINCT product, region mean one row per product?
Model answer: No — distinct (product, region) pairs. Pen can appear once per region.

Q2. NULL under DISTINCT vs WHERE?
Model answer: DISTINCT/GROUP BY treat NULLs as equal in one group; WHERE NULL=NULL is UNKNOWN so rows filter out.

Q3. Why can LIMIT not short-circuit DISTINCT?
Model answer: De-dup is blocking: must see all input to know uniqueness before emitting; LIMIT applies after.