DISTINCT, DISTINCT ON, and set operations

DISTINCT removes duplicate rows — by sorting or hashing

SELECT DISTINCT collapses identical output rows into one. “Identical” means all selected columns match. The mechanism matters: to find duplicates, Postgres must either sort the result (then adjacent equal rows are obvious) or build a hash of every row. Both are O(n) work over the full result set, plus memory; if the set is larger than work_mem, the sort or hash spills to disk, which is where the latency cliffs come from.

-- one row per distinct (country, plan) pair seen in users
SELECT DISTINCT country, plan FROM users;

So DISTINCT is not a free “clean up” — it’s a sort-or-hash over everything you selected. If you’re reaching for it to paper over duplicate rows that a missing join condition produced, fix the join instead; DISTINCT hides the bug and adds cost.

DISTINCT ON: Postgres’s “latest row per group”

A Postgres-specific extension solves a question plain SQL handles clumsily: “give me one whole row per group — specifically the latest one.” DISTINCT ON (expr) keeps the first row for each distinct value of expr, and “first” is decided by ORDER BY. The rule that trips everyone: the ORDER BY must start with the same expression(s) as DISTINCT ON, then add the tiebreaker that picks which row wins.

-- latest order per user: one row each, the most recent by created_at
SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC;   -- user_id first (required), then newest wins

This is dramatically cleaner than the portable alternatives (a correlated subquery, or a window-function ROW_NUMBER() filtered to = 1, which you’ll meet in the window-functions unit). For “newest/best row per key”, DISTINCT ON is the idiomatic Postgres tool.

UNION vs UNION ALL: the dedup tax

Set operators stack two result sets vertically. The pivotal distinction:

• UNION combines both sides and removes duplicate rows — which means it sorts or hashes the entire combined result, exactly like DISTINCT.
• UNION ALL combines both sides and keeps everything, including duplicates. No dedup step, no sort, no hash — it just concatenates the streams.

The production rule: default to UNION ALL; use UNION only when duplicates are actually possible and you actually want them removed. When the two inputs are disjoint by construction (different statuses, different date ranges, different tables that can’t share a row), UNION is pure wasted sorting. The opening incident was precisely this — disjoint sources, a needless dedup of 9 million rows.

INTERSECT, EXCEPT, and the column rules

Two more set operators, both of which also dedup by default:

• INTERSECT returns rows present in both sides.
• EXCEPT returns rows in the first side that are not in the second (set difference; other dialects call it MINUS).

All set operators share strict structural rules: each side must have the same number of columns, and corresponding columns must have compatible types. The output column names come from the first query; the others are ignored for naming. And one NULL nuance that ties back to the previous lesson — for set-operator deduplication, two NULLs are treated as equal (so UNION collapses duplicate NULL rows), which is the opposite of how = treats them. That’s an intentional special case for grouping/dedup contexts.

When you see a set operation failing with a type mismatch, the column count check and a CAST on the mismatched column in one branch fixes it. If the NULL behaviour surprises you in a UNION result, remember: dedup contexts treat NULLs as equal — the same rule applies to GROUP BY and DISTINCT.