UNION, INTERSECT, EXCEPT: stacking query results on top of each other
A JOIN adds columns side by side. These stack the rows of two queries into one list: combine them, keep only the overlap, or subtract one from the other.
A JOIN widens your data: it adds more columns side by side. UNION, INTERSECT, and EXCEPT do
something different: they take the rows of two queries and stack them into one list. The two
queries just need the same number of columns, of compatible types.
🎯 Explain Like I'm Hired Think of two guest lists.
UNIONmerges them into one list and removes duplicate names.INTERSECTkeeps only the names that appear on both lists.EXCEPTkeeps the names on the first list that are not on the second. Example: "which users were active last month but not this month?" isEXCEPT: last month's active users, minus this month's active users.
-- Everyone from India OR who spent over 1000, as one de-duplicated list
SELECT id FROM customers WHERE country = 'IN'
UNION
SELECT id FROM customers WHERE lifetime_spend > 1000;
UNION vs UNION ALL: the difference interviewers ask about
UNION removes duplicates, which costs extra work. UNION ALL just glues the two lists together
without checking for duplicates, so it's faster:
SELECT id FROM table_a
UNION ALL -- keeps duplicates, cheaper
SELECT id FROM table_b;
If you know the two lists can't overlap, or you don't mind duplicates, use UNION ALL. Reaching for
plain UNION out of habit does needless work.
The interview tip: comparing two snapshots
EXCEPT is the cleanest way to answer "what changed between yesterday and today?":
-- IDs that were there yesterday but are gone today
SELECT id FROM snapshot_yesterday
EXCEPT
SELECT id FROM snapshot_today;
No join, no blank-value juggling, just subtract one list from the other.