UNION, INTERSECT, EXCEPT
Combine compatible result sets with UNION, find common rows with INTERSECT, and find the difference with EXCEPT.
UNION, INTERSECT, EXCEPT is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Set Operations Overview
Three set operators combine result sets row-by-row:
UNION— rows in A OR B (deduplicated)INTERSECT— rows in A AND BEXCEPT— rows in A but NOT in B
UNION
Combine two result sets, removing duplicates:
SELECT email FROM users
UNION
SELECT email FROM newsletter_subscribers;Column Compatibility
Both queries must have the same number of columns and compatible types:
-- OK:
SELECT email FROM users UNION SELECT email FROM customers;
-- ERROR — different column count:
SELECT email FROM users UNION SELECT email, name FROM customers;INTERSECT
Rows that appear in both queries:
SELECT product_id FROM orders WHERE user_id = 1
INTERSECT
SELECT product_id FROM orders WHERE user_id = 2;
-- Products bought by BOTH users.EXCEPT
Rows in the first query but not the second:
SELECT product_id FROM products
EXCEPT
SELECT product_id FROM order_items;
-- Products never ordered.ORDER BY with Set Operators
ORDER BY applies to the whole combined result. Put it at the end:
SELECT email FROM users
UNION
SELECT email FROM customers
ORDER BY email;Set Operators and NULLs
For set ops, two NULLs are treated as equal — UNION dedupes them, INTERSECT matches them. (Different from NULL = NULL in WHERE.)
Set Operators on Subqueries
Parenthesise subqueries for clarity and to combine with LIMIT:
(SELECT id FROM users ORDER BY created_at DESC LIMIT 10)
UNION
(SELECT id FROM users ORDER BY total DESC LIMIT 10);Operator Precedence
INTERSECT binds tighter than UNION/EXCEPT. Use parens:
SELECT id FROM a
UNION
SELECT id FROM b
INTERSECT
SELECT id FROM c;
-- means: a UNION (b INTERSECT c)
-- Use parens to force order:
(SELECT id FROM a UNION SELECT id FROM b) INTERSECT SELECT id FROM c;Performance
UNION sorts to dedupe. INTERSECT and EXCEPT do similar work. For large sets, prefer JOIN/EXISTS patterns and only use set ops where the semantics fit clearly.
Standard SQL vs Vendor Quirks
All three exist in PostgreSQL. MySQL only added INTERSECT and EXCEPT in 8.0.31; older versions emulate with JOIN/NOT IN.
Recap
Set operators express OR/AND/MINUS over rows.
- UNION — combine + dedupe
- INTERSECT — common rows
- EXCEPT — A minus B
Quick Check
Which set operator returns "rows in A that are NOT in B"?
Frequently asked questions
Is the “UNION, INTERSECT, EXCEPT” lesson free?
Yes — the full text of “UNION, INTERSECT, EXCEPT” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “UNION, INTERSECT, EXCEPT”?
Combine compatible result sets with UNION, find common rows with INTERSECT, and find the difference with EXCEPT. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “UNION, INTERSECT, EXCEPT” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.