âš¡IntermediateSet Operators
UNION, INTERSECT, and EXCEPT
Combine result sets from multiple SELECT statements using set operators.
Overview
Set operators combine the results of two SELECT statements. UNION returns all rows from both queries, removing duplicates (UNION ALL keeps duplicates and is faster). INTERSECT returns only rows that appear in both results. EXCEPT returns rows from the first result that don't appear in the second.
All set operators require both queries to have the same number of columns with compatible types. Column names come from the first query. ORDER BY applies to the final combined result and must appear at the end, after all UNIONed queries.
Key Points
- •UNION deduplicates; UNION ALL is faster (keeps duplicates)
- •INTERSECT returns rows present in both query results
- •EXCEPT returns rows in first result but NOT in second
- •All queries must have the same number of columns and compatible types
- •ORDER BY goes at the very end, after all UNION/INTERSECT/EXCEPT
Examples
UNION, INTERSECT, EXCEPT
-- UNION: all customers from both tables (deduped)
SELECT email FROM customers_eu
UNION
SELECT email FROM customers_us;
-- UNION ALL: all rows, faster (no deduplication)
SELECT 'eu' AS source, email FROM customers_eu
UNION ALL
SELECT 'us', email FROM customers_us;
-- INTERSECT: emails in both regions
SELECT email FROM customers_eu
INTERSECT
SELECT email FROM customers_us;
-- EXCEPT: emails in EU but not US
SELECT email FROM customers_eu
EXCEPT
SELECT email FROM customers_us;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4