Chapter 09 · Subqueries, CTEs, and Set Operations
Recursive CTE Fundamentals
Recursive CTEs repeatedly feed newly produced rows back into a query. They are the standard SQL tool for trees, graphs, paths, sequences, and transitive relationships.
Learning outcomes
A recursive CTE contains a non-recursive anchor and a recursive member that refers to the CTE itself. Execution repeatedly expands a working set until the recursive member produces no new rows.
Identify anchor, recursive member, and termination predicate.
Generate bounded sequences with recursive SQL.
Traverse an adjacency-list hierarchy and calculate depth and paths.
Prevent cycles and avoid relying on accidental output order.
Recursive CTE anatomy
The syntax is recursive, but engines commonly evaluate it iteratively using a working table.
Generate a bounded sequence
WITH RECURSIVE numbers(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM numbers WHERE n < 10)SELECT nFROM numbersORDER BY n;The anchor creates 1. The recursive member advances by one. The predicate n < 10 is the termination condition; without a valid stopping rule, the query can run until an engine limit or resource failure.
Traverse the entire organization
WITH RECURSIVE org AS ( SELECT employee_id, employee_name, manager_id, job_title, 0 AS depth, employee_name AS path FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, e.job_title, org.depth + 1, org.path || ' > ' || e.employee_name FROM employee AS e JOIN org ON e.manager_id = org.employee_id)SELECT employee_id, employee_name, job_title, depth, pathFROM orgORDER BY path;The table stores only direct manager relationships. Recursion derives indirect ancestry and a complete path from the root.
Start from a selected subtree
WITH RECURSIVE team AS ( SELECT employee_id, employee_name, manager_id, 0 AS distance_from_manager FROM employee WHERE employee_id = 2 UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, team.distance_from_manager + 1 FROM employee AS e JOIN team ON e.manager_id = team.employee_id)SELECT *FROM teamORDER BY distance_from_manager, employee_id;The anchor controls the starting point. The result includes employee 2 at distance zero and all direct and indirect reports below that node.
UNION ALL versus UNION
| Operator inside recursion | Effect | Tradeoff |
|---|---|---|
UNION ALL | Keeps every produced row. | Usually faster; requires explicit cycle prevention when cycles are possible. |
UNION | Removes duplicate rows between iterations. | Can stop some repeated states, but duplicate comparison adds work and may not detect cycles when depth or path changes. |
Cycle-safe path tracking
WITH RECURSIVE org AS ( SELECT employee_id, employee_name, manager_id, 0 AS depth, printf('/%d/', employee_id) AS visited_ids FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, org.depth + 1, org.visited_ids || printf('%d/', e.employee_id) FROM employee AS e JOIN org ON e.manager_id = org.employee_id WHERE instr( org.visited_ids, printf('/%d/', e.employee_id) ) = 0)SELECT employee_id, employee_name, depthFROM orgORDER BY depth, employee_id;The delimited path avoids false matches such as ID 1 inside ID 11. On systems with arrays or dedicated graph features, use their native cycle-detection facilities.
Ordering is a presentation decision
Recursive evaluation order is not a stable business order. Store columns such as depth, sortable path, or explicit sibling position, then apply a final ORDER BY.
WITH RECURSIVE org AS ( SELECT employee_id, employee_name, manager_id, 0 AS depth, printf('%04d', employee_id) AS sort_path FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id, org.depth + 1, org.sort_path || '.' || printf('%04d', e.employee_id) FROM employee AS e JOIN org ON e.manager_id = org.employee_id)SELECT printf('%.*c%s', depth * 2, ' ', employee_name) AS organizationFROM orgORDER BY sort_path;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
- Generate dates from 2026-08-01 through 2026-08-07.
- Return the organization with depth and full path.
- Return all descendants of the Analytics Manager.
- Add cycle detection using a visited-ID path.
- Explain why final ordering must be explicit.
WITH RECURSIVE calendar(day) AS ( VALUES (DATE('2026-08-01')) UNION ALL SELECT DATE(day, '+1 day') FROM calendar WHERE day < DATE('2026-08-07'))SELECT dayFROM calendarORDER BY day;Checkpoint
Control recursion
- What does the anchor member produce?
- What must the recursive member reference?
- Which condition stops the number sequence?
- Why does UNION alone not guarantee cycle safety?
- How should hierarchy output be ordered?
Review the answers
The anchor produces the initial working rows. The recursive member references the CTE. The n < 10 predicate stops expansion. Rows can remain distinct because depth or path changes even in a cycle. Store ordering metadata and apply a final ORDER BY.
Summary and references
- Recursive CTEs combine an anchor with a self-referencing recursive member.
- Termination and cycle safety are correctness requirements.
- Adjacency lists become full hierarchies through recursive joins.
- Depth and path columns support analysis and deterministic presentation.