intermediate~2h

Set Operators — UNION, INTERSECT, EXCEPT/MINUS

SQL's set operators combine the results of two queries by set logic rather than by joining columns — the right tool whenever the question is really "which rows are in either, both, or only one of these two result sets."

Learning objectives

State precisely what UNION, UNION ALL, INTERSECT, and EXCEPT each keep and discard.
Explain why UNION ALL is cheaper than UNION, in terms of what work the database skips.
Recognize when a set operator is the right tool versus when a JOIN actually answers the question.

Some real questions are fundamentally about combining or comparing two separate result sets as sets ("everyone in either list," "everyone in both") rather than about joining rows together on a shared key — set operators are the tool built specifically for that shape of question.

Without set operators, answering "who's in list A but not list B" would require an awkward NOT IN/NOT EXISTS subquery construction — technically possible, but it obscures the actual set-logic intent and is easy to get subtly wrong (especially with NOT IN and NULLs).

UNION — combines two result sets, removing duplicates. UNION ALL — combines them keeping every duplicate, cheaper since no dedup pass is needed. INTERSECT — keeps only rows present in both result sets. EXCEPT (or MINUS in Oracle) — keeps rows in the first result set that aren't in the second.

📖 Story

Picture two spreadsheets: one lists everyone who attended a product webinar, the other lists everyone who's made a purchase. Marketing wants three different answers from those two lists — everyone in either list (for a combined newsletter), everyone in both lists (webinar attendees who converted), and everyone who attended the webinar but never purchased (the group to re-target). Those three questions are exactly what SQL's set operators answer — combining two separate result sets by set logic, rather than by joining rows together.

The three set operators, precisely

UNION — every row that appears in either result set, with exact duplicate rows collapsed to one. UNION ALL keeps every duplicate, doing no deduplication work at all.
INTERSECT — only rows that appear in both result sets.
EXCEPT (called MINUS in Oracle) — rows that appear in the first result set but not in the second; order matters, A EXCEPT B is not the same as B EXCEPT A.

The one hard rule: column compatibility

Every set operator requires both queries to return the same number of columns, in a compatible order and type — SQL matches columns positionally, not by name. The final result's column names come from the first query alone; the second query's column aliases are simply ignored.

Operator	Keeps	Common use
`UNION`	Union of both, de-duplicated	Combining rows from similar tables (e.g., archived + active orders)
`UNION ALL`	Union of both, duplicates kept	Same as above when duplicates are known-impossible or desired — much cheaper
`INTERSECT`	Only rows in both	Finding overlap between two audiences/lists
`EXCEPT` / `MINUS`	Rows in first only	Finding what's missing from one list relative to another

Why UNION is more expensive than UNION ALL

UNION must deduplicate the combined result, which PostgreSQL implements by sorting the entire combined output (or hashing it) and discarding duplicate rows, an operation over the whole result set. UNION ALL skips this step entirely — it simply concatenates both result sets' rows as they're produced, with no sort or hash step at all, making it strictly cheaper whenever you already know duplicates can't occur or don't matter.

How INTERSECT and EXCEPT are actually executed

Both are implemented the same way as UNION's dedup step, but comparing rows across the two inputs rather than within one: PostgreSQL typically sorts (or hashes) both inputs and walks them looking for matching rows (INTERSECT) or rows present in the first but absent from the second (EXCEPT). EXPLAIN on either will typically show a HashSetOp or SetOp node performing exactly this comparison.

Set operators versus JOIN, structurally

A JOIN combines columns from two tables into wider rows, based on a matching condition. A set operator combines rows (not columns) from two queries that already return the same shape, based on whether the row itself matches. They solve genuinely different problems — reaching for a JOIN when you actually want row-level set logic (or vice versa) produces a fundamentally wrong query, not just a slower one.

Write both queries independently first, and confirm each returns the same number of columns with compatible types — this is a hard requirement, not a style preference.
Combine them with the operator matching your actual question: UNION/UNION ALL for "either," INTERSECT for "both," EXCEPT for "first but not second."
Default to UNION ALL unless you specifically need deduplication — measure whether duplicates can even occur before paying for the dedup step.
If you need to distinguish which side of the union a row came from, add a literal marker column to each query before combining: SELECT *, 'webinar' AS source FROM ... UNION ALL SELECT *, 'purchase' AS source FROM ....
Wrap the combined query in parentheses before applying ORDER BY/LIMIT to the overall result — a bare ORDER BY at the end applies to the whole combined set, not to either side individually.

Default to UNION ALL over UNION unless you specifically need the deduplication — it skips an entire sort/hash pass over the combined result.
Add an explicit marker column (a literal string per branch) whenever the combined result needs to distinguish which query a row came from, since the combined result itself carries no such information.
Keep column lists explicit and aligned by position on both sides of the operator — never rely on SELECT * across a set operator when the two tables' column orders could ever drift apart.
Reach for INTERSECT/EXCEPT directly instead of emulating them with NOT IN/NOT EXISTS subqueries when the logic really is "in both" or "in one but not the other" — it states the intent more clearly and lets the planner choose the best strategy.
Test set-operator queries against genuinely overlapping test data, not just disjoint sample rows — a lot of INTERSECT/EXCEPT bugs only show up once real overlap exists.

⚠️ Why this keeps happening

Set operators read almost like natural English ("union" sounds like "combine," "except" sounds like "exclude"), which makes it easy to reach for one without checking the column-shape requirement underneath — the query fails (or silently returns nonsense) the moment that assumption breaks.

Mismatched column counts or incompatible types between the two queries — this is a hard error, but a mismatched type that PostgreSQL can implicitly cast (e.g., int vs numeric) can silently coerce in a way you didn't intend, rather than failing loudly.
Assuming column names from the second query survive into the result. They don't — only the first query's column names appear in the output, which surprises anyone aliasing the second query expecting it to matter.
Reaching for UNION by habit when UNION ALL was actually correct, paying for a full deduplication pass on data that was already guaranteed unique (e.g., combining results from two mutually exclusive WHERE clauses on the same table).
Applying ORDER BY/LIMIT to only one side of the operator, expecting it to affect just that branch — a trailing ORDER BY always applies to the entire combined result, not to an individual query within it.
Using EXCEPT and getting the argument order backward. A EXCEPT B and B EXCEPT A are different queries entirely; swapping them is a silent logic bug, not something that throws an error.

Always prefer UNION ALL over UNION when you can prove duplicates are impossible or irrelevant — this alone removes an entire sort/hash step over the whole combined result.
Filter each side of the operator with its own WHERE clause as early as possible — pushing filters into each branch before combining is far cheaper than combining everything first and filtering the union afterward.
Ensure both sides of the operator can use an index for their own filtering — the set operator itself doesn't help either branch's underlying query run faster; each branch is optimized independently.
For very large combined sets, check whether the planner chose a hash-based or sort-based set-operation strategy (EXPLAIN ANALYZE) — a sort-based plan on a huge combined result can dominate total query time.
Consider whether a single query with OR conditions (instead of UNION-ing two near-identical queries against the same table) might let the planner produce a simpler, cheaper plan.

Set-operator queries built from concatenated user input carry the identical SQL injection risk as any dynamic SQL — parameterize both sides of the operator exactly as strictly as any standalone query.

Track execution time for each side of a UNION/UNION ALL independently when troubleshooting a slow combined query — one branch (say, a query against a large archive table) can dominate the total time while the other branch looks perfectly healthy.

Document, next to any UNION-based query, an explicit note on whether duplicates were considered and deliberately kept, removed, or assumed impossible — this intent is invisible from the SQL alone six months later.
Add the source-marker column pattern as a house convention for any report combining rows from multiple origins — it prevents a whole category of "wait, which table did this row come from" debugging sessions.
When combining live data with archived/cold-storage data via UNION ALL, monitor both branches' query times separately — a slow archive query can quietly dominate the combined query's total latency.
Re-verify set-operator queries whenever the underlying tables' schemas change — a column reordering on either side can silently misalign positional columns without necessarily producing an error if types happen to still be compatible.
Prefer INTERSECT/EXCEPT over a hand-rolled NOT EXISTS when the logic is genuinely row-set comparison — it's easier for the next engineer (and the query planner) to reason about directly.

Combine two tables with an overlapping row using UNION and UNION ALL, and confirm the row count differs by exactly the number of duplicates.
Build a query answering "customers who both attended a webinar and made a purchase" using INTERSECT, then rewrite it as an equivalent INNER JOIN and confirm both return the same customer IDs.
Build a query answering "webinar attendees who never purchased" using EXCEPT, then swap the operand order and confirm the result is different (and answers a different question).
Add a source-marker literal column to a UNION ALL query combining two tables, and confirm you can GROUP BY that marker in an outer query to count rows per source.

✓ Quick recap

UNION combines and deduplicates; UNION ALL combines and keeps every duplicate — always prefer ALL unless dedup is actually needed.
INTERSECT keeps rows in both sides; EXCEPT/MINUS keeps rows in the first side only, and side order matters.
Column count, order, and type-compatibility must match across both queries — matching is positional, never by name.
Only the first query's column names/aliases survive into the combined result.
A trailing ORDER BY/LIMIT always applies to the whole combined result, never to one branch alone.

Want a visual for this concept?

Generate a diagram tailored to “Set Operators — UNION, INTERSECT, EXCEPT/MINUS” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Advanced SQL — Stored Procedures, Functions, Triggers & Sequences →← Back to all SQL chapters