Set operations combine result sets from multiple SELECTs with compatible columns. UNION merges rows; INTERSECT and EXCEPT compute overlap and difference.
UNION ALL
SELECT email FROM customers
UNION ALL
SELECT contact_email FROM suppliers;Practice: Seed customers and orders sample data, then run each query. Verify row counts manually.
UNION ALL keeps duplicates; faster when you know duplicates are impossible or acceptable.
UNION dedupe
SELECT city FROM customers
UNION
SELECT city FROM suppliers;UNION (without ALL) removes duplicate rows across branches—extra sort/hash cost.
INTERSECT and EXCEPT
SELECT customer_id FROM orders
INTERSECT
SELECT id FROM customers WHERE email LIKE '%@example.com';
SELECT customer_id FROM orders
EXCEPT
SELECT id FROM customers WHERE name = 'Ada';Support is broad in Postgres and SQLite 3.39+; MySQL historically used workarounds—see MySQL track.
Column alignment
Each SELECT must return the same number of columns with compatible types. Names come from the first SELECT.
Important interview questions and answers
- Q: UNION vs UNION ALL?
A: UNION removes duplicates; UNION ALL concatenates all rows. - Q: UNION vs JOIN?
A: UNION stacks rows vertically; JOIN combines columns horizontally.
Self-check
- When prefer UNION ALL over UNION?
- Must column counts match across UNION branches?
Tip: Prefer UNION ALL when duplicates are impossible—it skips dedup work.
Interview prep
- UNION vs UNION ALL?
UNION deduplicates; UNION ALL concatenates all rows.
- UNION vs JOIN?
UNION stacks rows vertically; JOIN combines columns horizontally.