Chapter 09 · Subqueries, CTEs, and Set Operations
Common Table Expressions
A common table expression gives a query result a temporary name for one statement, turning deeply nested SQL into a readable sequence of relational transformations.
Learning outcomes
An ordinary common table expression is a named query result visible within one statement. It improves structure by separating stages, documenting grain, and allowing later stages to refer to earlier ones.
Refactor nested subqueries into named CTE stages.
Chain multiple CTEs in dependency order.
Define explicit CTE column names and result grain.
Understand statement scope and materialization differences across engines.
Basic WITH syntax
WITH paid_sales AS ( SELECT sale_id, customer_id, product_id, quantity, sold_at FROM sale WHERE status = 'paid')SELECT customer_id, COUNT(*) AS paid_sale_count, SUM(quantity) AS paid_unitsFROM paid_salesGROUP BY customer_idORDER BY customer_id;paid_sales behaves like a temporary view for this statement only. It is not stored in the schema and disappears after the statement completes.
Chain transformations
WITH paid_lines AS ( SELECT s.customer_id, s.quantity, p.unit_price, s.quantity * p.unit_price AS line_value FROM sale AS s JOIN product AS p ON p.product_id = s.product_id WHERE s.status = 'paid'),customer_totals AS ( SELECT customer_id, COUNT(*) AS paid_sale_count, SUM(quantity) AS paid_units, ROUND(SUM(line_value), 2) AS gross_value FROM paid_lines GROUP BY customer_id)SELECT c.full_name, t.paid_sale_count, t.paid_units, t.gross_valueFROM customer_totals AS tJOIN customer AS c ON c.customer_id = t.customer_idORDER BY t.gross_value DESC, c.customer_id;Each CTE has one responsibility: select paid line facts, aggregate to customer grain, then attach descriptive customer data.
Declare the CTE columns
WITH customer_activity( customer_id, sale_count, total_units) AS ( SELECT customer_id, COUNT(*), SUM(quantity) FROM sale GROUP BY customer_id)SELECT *FROM customer_activityORDER BY customer_id;An explicit column list can make the public contract clearer, especially when inner expressions are complex or reused by several downstream references.
CTE versus nested derived table
| Concern | Nested subquery | CTE |
|---|---|---|
| Reading order | Often inside-out | Usually top-to-bottom. |
| Reuse in one statement | Repeat or nest again | Reference the CTE name more than once. |
| Scope | Only its immediate query context | The statement following WITH. |
| Optimization | Engine-dependent | Also engine-dependent; readability does not guarantee materialization. |
Reuse one named result
WITH customer_activity AS ( SELECT customer_id, SUM(quantity) AS total_units FROM sale GROUP BY customer_id)SELECT c.full_name, a.total_units, ROUND((SELECT AVG(total_units) FROM customer_activity), 2) AS average_customer_unitsFROM customer_activity AS aJOIN customer AS c ON c.customer_id = a.customer_idWHERE a.total_units > ( SELECT AVG(total_units) FROM customer_activity)ORDER BY a.total_units DESC, c.customer_id;The CTE is referenced in the main FROM clause and in two scalar subqueries. The optimizer decides whether to inline, materialize, or otherwise transform the expression.
Materialization hints are not portable commands
WITH paid_sales AS MATERIALIZED ( SELECT * FROM sale WHERE status = 'paid')SELECT COUNT(*)FROM paid_sales;SQLite documents MATERIALIZED and NOT MATERIALIZED as non-binding planning hints. PostgreSQL has related syntax and its own optimization rules. MySQL and SQL Server use different behavior and do not accept this exact syntax.
Scope and naming discipline
Limited lifetime
A CTE name exists only for the statement immediately following WITH.
State row meaning
Names such as customer_totals communicate what one row represents.
Dependencies first
A later CTE may reference an earlier CTE in the same WITH list.
No persistence
Use a view or table when the result needs schema-level reuse or storage.
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
- Create a CTE containing paid sales only.
- Create a second CTE that aggregates paid units per customer.
- Join the result to customers and retain customers above the average paid-unit total.
- Add a descriptive activity band in the final SELECT.
- Rewrite the query as nested subqueries and compare readability.
WITH paid_sales AS ( SELECT customer_id, quantity FROM sale WHERE status = 'paid'),customer_units AS ( SELECT customer_id, SUM(quantity) AS paid_units FROM paid_sales GROUP BY customer_id),benchmark AS ( SELECT AVG(paid_units) AS average_paid_units FROM customer_units)SELECT c.full_name, u.paid_units, CASE WHEN u.paid_units > b.average_paid_units THEN 'above average' ELSE 'at or below average' END AS activity_bandFROM customer_units AS uCROSS JOIN benchmark AS bJOIN customer AS c ON c.customer_id = u.customer_idORDER BY u.paid_units DESC, c.customer_id;Checkpoint
Structure the pipeline
- How long does an ordinary CTE name remain in scope?
- Can one CTE reference an earlier CTE?
- Does CTE syntax guarantee materialization?
- Why should a CTE name communicate grain?
- When should a view replace a CTE?
Review the answers
A CTE lasts for one statement. Later CTEs can reference earlier ones. Materialization is optimizer- and dialect-dependent. Grain-oriented names make joins and aggregates safer. Use a view for schema-level reuse across statements.
Summary and references
- Ordinary CTEs name intermediate relations for one statement.
- Chained CTEs turn complex SQL into explicit stages.
- Column lists and grain-oriented names create readable contracts.
- CTEs are organizational constructs, not guaranteed temporary tables.