Joins combine tables side by side. Set operators combine query results on top of each other or compare them. They are simple once you see them on data, and they answer questions that are awkward with joins.
The examples use a fictional coaching chain, SkillSprint, with two tables of student emails: sql_batch and python_batch.
The rules for all set operators
- Each query must return the same number of columns.
- Matching columns must have compatible types.
- Column names come from the first query.
- ORDER BY goes once, at the very end.
UNION ALL: stack everything
SELECT email FROM sql_batch
UNION ALL
SELECT email FROM python_batch;Every row from both queries, duplicates included. A student in both batches appears twice.
Use it to append similar data, such as January and February sales tables, or orders from two regional systems.
UNION: stack and remove duplicates
SELECT email FROM sql_batch
UNION
SELECT email FROM python_batch;Each email appears once. Removing duplicates needs extra work (sorting or hashing), so UNION is slower than UNION ALL. If you know there are no duplicates, or you want to keep them, use UNION ALL.
Interview favourite: "What is the difference between UNION and UNION ALL?" Answer: UNION removes duplicates, UNION ALL keeps them and is faster.
INTERSECT: in both
SELECT email FROM sql_batch
INTERSECT
SELECT email FROM python_batch;Students enrolled in both batches.
EXCEPT: in the first but not the second
SELECT email FROM sql_batch
EXCEPT
SELECT email FROM python_batch;Students who took SQL but not Python, a natural list for a cross-sell campaign. Order matters: swapping the queries gives the opposite list. (Oracle calls this operator MINUS.)
Labelling where rows came from
SELECT email, 'SQL' AS batch FROM sql_batch
UNION ALL
SELECT email, 'Python' AS batch FROM python_batch
ORDER BY email;Adding a constant column keeps track of the source after stacking.
Set operators vs joins
| Question | Set operator | Join alternative |
|---|---|---|
| In both lists | INTERSECT | INNER JOIN on the key |
| In A, not in B | EXCEPT | LEFT JOIN … WHERE b.key IS NULL |
| Everything from both | UNION / UNION ALL | Not a join job |
Set operators compare whole rows, which is handy when there is no single key. Joins are better when you also need other columns from both tables.
A real reconciliation example
Finance wants transactions in the bank statement that are missing from the internal ledger:
SELECT txn_id, amount, txn_date FROM bank_statement
EXCEPT
SELECT txn_id, amount, txn_date FROM ledger;Because all three columns are compared, this also catches entries whose amount or date differs.
Practise
Our set operations practice set covers all four operators with instant checking. For the join-based versions of these questions, see SQL joins explained.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: