Chapter 09 · Subqueries, CTEs, and Set Operations
UNION, UNION ALL, INTERSECT, and EXCEPT
Set operators combine whole result rows vertically. Unlike joins, they do not match columns side by side; they apply union, intersection, or difference to compatible query results.
Learning outcomes
Set operators consume complete query results with compatible column counts and combine them vertically. They compare whole rows, so column order, data-type compatibility, duplicate rules, and operator precedence are central.
Distinguish UNION ALL from duplicate-eliminating UNION.
Use INTERSECT for common rows and EXCEPT for directional difference.
Make compound-query columns union-compatible.
Control ordering, precedence, and cross-dialect portability.
Set operators versus joins
| Operation | Combines | Output shape |
|---|---|---|
| JOIN | Columns from related rows | Usually wider than either input. |
| UNION / UNION ALL | Rows from either input | Same number of columns as each branch. |
| INTERSECT | Rows common to both inputs | Same number of columns as each branch. |
| EXCEPT | Rows in the left input but not the right | Same number of columns as each branch. |
UNION ALL preserves every row
SELECT email, 'web' AS sourceFROM web_leadUNION ALLSELECT email, 'event' AS sourceFROM event_leadORDER BY email, source;The source column makes web and event rows different even when the email matches. Duplicate rows inside each source are also retained.
UNION removes duplicate result rows
SELECT emailFROM web_leadUNIONSELECT emailFROM event_leadORDER BY email;Duplicate elimination applies to the complete result row. It usually requires comparison, sorting, hashing, or equivalent work, so use UNION ALL when duplicates are acceptable or meaningful.
INTERSECT returns common rows
SELECT emailFROM web_leadINTERSECTSELECT emailFROM event_leadORDER BY email;The result contains Omar and the shared address. SQLite removes duplicates for INTERSECT and does not implement INTERSECT ALL.
EXCEPT is directional difference
SELECT emailFROM web_leadEXCEPTSELECT emailFROM event_leadORDER BY email;Reversing the two branches produces event-only leads. EXCEPT is not commutative.
Union compatibility
SELECT customer_id AS entity_id, full_name AS display_name, 'customer' AS entity_typeFROM customerUNION ALLSELECT employee_id AS entity_id, employee_name AS display_name, 'employee' AS entity_typeFROM employeeORDER BY entity_type, entity_id;Each branch returns three columns in the same semantic order. Output column names are normally derived from the leftmost branch, while type-resolution rules differ by engine.
ORDER BY belongs to the compound result
SELECT email, 'web' AS source_rankFROM web_leadUNION ALLSELECT email, 'event' AS source_rankFROM event_leadORDER BY email, source_rank;In SQLite, only the rightmost simple SELECT may be followed by ORDER BY or LIMIT, and those clauses apply to the entire compound query.
Precedence differs across dialects
Left-to-right
Three or more compound SELECT terms group from left to right.
INTERSECT first
INTERSECT binds more tightly than UNION and EXCEPT.
INTERSECT first
Current MySQL follows a grammar where INTERSECT has higher precedence.
Use parentheses
Make intended grouping explicit instead of relying on dialect precedence.
SQLite supports UNION, UNION ALL, INTERSECT, and EXCEPT, but not INTERSECT ALL or EXCEPT ALL. Current PostgreSQL, MySQL, and Oracle offer broader ALL variants; SQL Server supports UNION, INTERSECT, and EXCEPT with its own syntax rules. Check the target version before using duplicate-preserving intersection or difference.
Set operations and NULL
SELECT NULL AS valueUNIONSELECT NULL AS value;For compound-query duplicate comparison, SQLite considers NULL equal to NULL. This is different from ordinary NULL = NULL, which evaluates to unknown.
Practice database
Run this setup once in a disposable SQLite database. Chapter 9 uses customer and sales data for subqueries, an employee hierarchy for recursion, and two lead lists for set operations.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS event_lead;DROP TABLE IF EXISTS web_lead;DROP TABLE IF EXISTS employee;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business')), email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), sold_at TEXT NOT NULL) STRICT;CREATE TABLE employee ( employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL, manager_id INTEGER REFERENCES employee(employee_id), job_title TEXT NOT NULL) STRICT;CREATE TABLE web_lead (email TEXT NOT NULL) STRICT;CREATE TABLE event_lead (email TEXT NOT NULL) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer', 'nadia@example.com'), (2, 'Omar Haddad', 'Berlin', 'business', 'omar@example.com'), (3, 'Lina Chen', NULL, 'consumer', 'lina@example.com'), (4, 'Ava Morgan', 'Berlin', 'consumer', 'ava@example.com'), (5, 'Noah Silva', 'Lisbon', 'business', 'noah@example.com'), (6, 'Marta Costa', NULL, 'business', 'marta@example.com');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, 'paid', '2026-08-01 09:15:00'), (101, 2, 11, 3, 'paid', '2026-08-01 10:45:00'), (102, 1, 12, 2, 'paid', '2026-08-02 11:30:00'), (103, 4, 10, 1, 'pending', '2026-08-03 13:05:00'), (104, 2, 13, 2, 'paid', '2026-08-03 15:20:00'), (105, 5, 15, 1, 'paid', '2026-08-04 16:40:00'), (106, 3, 14, 4, 'refunded', '2026-08-05 08:10:00'), (107, 5, 12, 5, 'paid', '2026-08-05 12:00:00'), (108, 2, 15, 1, 'pending', '2026-08-06 09:25:00'), (109, 4, 13, 3, 'paid', '2026-08-06 14:50:00');INSERT INTO employee VALUES (1, 'Amina Yusuf', NULL, 'Chief Data Officer'), (2, 'Jonas Weber', 1, 'Data Engineering Manager'), (3, 'Sara Kim', 1, 'Analytics Manager'), (4, 'Reza Nouri', 2, 'Data Engineer'), (5, 'Elena Rossi', 2, 'Platform Engineer'), (6, 'Maya Patel', 3, 'Analytics Engineer'), (7, 'Leo Martin', 3, 'BI Developer'), (8, 'Daria Novak', 4, 'Junior Data Engineer');INSERT INTO web_lead VALUES ('nadia@example.com'), ('omar@example.com'), ('new-web@example.com'), ('shared@example.com'), ('shared@example.com');INSERT INTO event_lead VALUES ('omar@example.com'), ('lina@example.com'), ('new-event@example.com'), ('shared@example.com'), ('shared@example.com');Each table exposes a different query-composition problem: one-to-many sales, customers without matches, a self-referencing hierarchy, and overlapping lists with duplicates.
Practice lab
- Return every lead occurrence with a source label using UNION ALL.
- Return unique email addresses across both sources using UNION.
- Find leads common to both sources with INTERSECT.
- Find event-only leads with EXCEPT.
- Combine customer and employee directories with aligned columns.
WITH all_emails AS ( SELECT email FROM web_lead UNION SELECT email FROM event_lead)SELECT a.email, CASE WHEN a.email IN (SELECT email FROM web_lead) AND a.email IN (SELECT email FROM event_lead) THEN 'both' WHEN a.email IN (SELECT email FROM web_lead) THEN 'web only' ELSE 'event only' END AS membershipFROM all_emails AS aORDER BY a.email;Checkpoint
Apply set algebra
- What duplicate behavior separates UNION from UNION ALL?
- Why must both branches return the same number of columns?
- Is EXCEPT commutative?
- Where does ORDER BY apply in a SQLite compound SELECT?
- Why should mixed set operators be parenthesized?
Review the answers
UNION removes duplicate rows; UNION ALL preserves them. Set operators compare aligned rows, so widths must match. EXCEPT is directional. SQLite applies the final ORDER BY to the whole compound. Parentheses avoid dialect-specific precedence surprises.
Chapter 9 summary
- Subqueries can act as scalar values, row values, or temporary tables.
- Correlation links inner logic to each outer row.
- EXISTS and NOT EXISTS express duplicate-safe membership and absence tests.
- Ordinary CTEs organize query stages; recursive CTEs expand hierarchies and sequences.
- Set operators combine compatible result rows with explicit duplicate semantics.
Chapter 10 moves from reading data to changing it safely with INSERT, UPDATE, DELETE, conflict handling, transactions, and verification workflows.