UNION, INTERSECT and EXCEPT
Two separate SELECTs, stacked or compared row by row. Pick an operator and watch which names survive.
Operator
A = 2024 conference attendees, B = 2025 attendees
Legend
A only
B only
in both A and B
Every Name From Both Queries
—Query
Result
Rows Returned
Set Operators: A Practical Guide
Combining answers, not tables.
Quick Context
A JOIN combines two tables sideways, matching rows on a key and producing wider rows. A set operator combines two result sets vertically — both SELECTs must return the same number of columns, in compatible types, and the output is one column of rows, not a wider table.
The four operators
- UNION — every row from either query, duplicates removed.
- UNION ALL — every row from either query, duplicates kept. Cheaper, because it skips the dedup step, and the right choice whenever you know there is no overlap.
- INTERSECT — only rows present in both.
- EXCEPT (MINUS in Oracle) — rows in the first query that are not in the second. Order matters: A EXCEPT B is not B EXCEPT A.
Interactive Exploration Guide
- Start with UNION. Everyone who attended either year, once each — Grace attended both years and appears only once.
- Switch to UNION ALL. The row count jumps by exactly one — Grace now appears twice, because ALL keeps the duplicate UNION silently dropped.
- Switch to INTERSECT. Only Grace remains — the one name both queries returned.
- Switch to EXCEPT. Everyone who came in 2024 but not 2025 — Grace is excluded because she is in B too.
Key Takeaway
UNION, INTERSECT and EXCEPT combine the rows two queries return rather than the tables they read from, and every column has to line up in count and type. UNION removes duplicates and costs a sort or hash to do it; UNION ALL keeps them and is cheaper whenever you already know the two sides do not overlap. INTERSECT and EXCEPT are comparisons across the whole row, and EXCEPT is not symmetric — which side you subtract from matters.