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.
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:
| name |
|---|
| Aisyah |
| Devi |
| Bala |
| Chong |
Bala and Chong appear in both source tables, but only once in the result.
Everyone who shopped in either month
UNION ALL: keep the duplicates
If you want every row, duplicates included, useful when you plan to count occurrences afterward, use UNION ALL:
| name |
|---|
| Aisyah |
| Bala |
| Chong |
| Bala |
| Chong |
| Devi |
Bala and Chong now appear twice. UNION ALL is 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.
Bala and Chong now appear twice
INTERSECT: only the overlap
INTERSECT returns rows that appear in both result sets: the customers who shopped in January and February:
| name |
|---|
| Chong |
| Bala |
Shopped both months
EXCEPT: only the difference
EXCEPT returns rows from the first query that don't appear in the second: a one-directional diff:
| name |
|---|
| Aisyah |
Order matters here, unlike UNION/INTERSECT: swapping the two queries (February EXCEPT January) would return Devi instead of Aisyah.
Shopped in January but not February
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.