Chapter 20 · intermediate · 6 min
Set operations: UNION, INTERSECT, EXCEPT
JOINs combine tables side by side, matching on a key. Set operations combine query results on top of each other: stacking, overlapping, or diffing two result sets with the same columns. Useful whenever data about the same thing lives in two separate tables, like "online customers" and "in-store customers".
We'll compare two months of shopper lists.
The dataset
Two one-column tables: customers who shopped in January, and customers who shopped in February. Some names appear in both.
Schema
| name | TEXT |
| name | TEXT |
Example data
UNION: combine and de-duplicate
UNION stacks the results of two SELECTs into one list and removes duplicates. Both queries must return the same number of columns, in compatible types.
Before you run it — Bala and Chong shopped both months. How many total rows come back: 6, or fewer since UNION dedupes?
UNION ALL: keep the duplicates
If you want every row, duplicates included, useful when you plan to count occurrences afterward, use UNION ALL. It's also faster than UNION, since it skips the extra work of de-duplicating; if you know there's no overlap, or don't care, prefer it.
Before you run it — how many rows this time, now that duplicates aren't removed?
INTERSECT: only the overlap
INTERSECT returns rows that appear in both result sets: the customers who shopped in January and February.
Before you run it — which customers shopped in both January and February?
EXCEPT: only the difference
EXCEPT returns rows from the first query that don't appear in the second: a one-directional diff. Order matters here, unlike UNION/INTERSECT: swapping the two queries (February EXCEPT January) would return a different customer.
Before you run it — who shopped in January but not February? And would swapping the order change the answer?
The four operations, side by side
| Operation | Keeps | Order-sensitive? |
|---|---|---|
UNION | everything, deduplicated | no |
UNION ALL | everything, duplicates kept | no |
INTERSECT | only rows in both | no |
EXCEPT | only rows in the first, not the second | yes |
If you've used Excel or Google Sheets: these map to set logic you may already know from COUNTIF/MATCH combos across two ranges, but there's no single spreadsheet function that does it as cleanly. UNION is closest to stacking two ranges and running Remove Duplicates; INTERSECT and EXCEPT usually require a helper formula in spreadsheets (like MATCH returning not-found) rather than a single operator.
Ready to practice? DBS's online and branch customers question below uses this exact pattern.
Set operations are an underused alternative to JOINs whenever you're really asking "what's the overlap / difference between these two lists" rather than "match these two tables on a key". That wraps up combining and summarizing data; from here on, the tutorial moves into the techniques that separate strong candidates: how SQL actually executes a query, subqueries, and window functions.