Chapter 05 · SQL Querying: Joins, Subqueries, CTEs, Set Operations, and LATERAL
CTEs, Recursive Queries, Materialization Control, and Graph/Hierarchy Traversal
Use PostgreSQL common table expressions for readable query layers and recursive graph traversal, reason about termination and cycles, and apply MATERIALIZED/NOT MATERIALIZED as planner controls rather than folklore.
Learning outcomes
ServiceHub now needs an organizational hierarchy report and a query pipeline with several readable stages. Common table expressions (CTEs) solve both problems, but two myths create trouble: “a CTE is always an optimization fence” and “WITH RECURSIVE automatically prevents cycles.” PostgreSQL 18 requires a more precise model.
Use nonrecursive CTEs as named relational steps without assuming they are always materialized.
Explain PostgreSQL folding rules for side-effect-free CTEs
and use MATERIALIZED/NOT MATERIALIZED
only when the tradeoff is understood.
Build recursive CTEs from an anchor term, recursive term, and termination rule.
Prevent graph cycles using explicit path logic or PostgreSQL
CYCLE syntax.
Inspect plan evidence only after preserving the intended relational result.
1. Chapter 05 lab bootstrap
All five lessons use the same small
ServiceHub dataset so row counts and edge cases
remain explainable. Run this as
servicehub_owner (or another role that owns the
disposable app schema) in
servicehub_lab. The objects are deliberately
prefixed ch05_. Before the drops, verify the
current database and inspect the matching objects; do not paste
this into a production database.
The DROP statements below target only chapter-scoped objects. If your database contains valuable objects with these names, stop and use another disposable database. No CASCADE is used.
\echo 'Verify target before recreating chapter-scoped lab objects'SELECT current_database(), current_user;\dt app.ch05_*DROP TABLE IF EXISTS app.ch05_work_order_event;DROP TABLE IF EXISTS app.ch05_work_order_tag;DROP TABLE IF EXISTS app.ch05_work_order;DROP TABLE IF EXISTS app.ch05_technician;DROP TABLE IF EXISTS app.ch05_customer;DROP TABLE IF EXISTS app.ch05_team;CREATE TABLE app.ch05_customer ( customer_id integer PRIMARY KEY, customer_name text NOT NULL, region text, active boolean NOT NULL DEFAULT true);CREATE TABLE app.ch05_technician ( technician_id integer PRIMARY KEY, technician_name text NOT NULL, specialty text NOT NULL, team_code text NOT NULL);CREATE TABLE app.ch05_work_order ( work_order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES app.ch05_customer(customer_id), assigned_technician_id integer REFERENCES app.ch05_technician(technician_id), status text NOT NULL CHECK (status IN ('queued','open','closed','cancelled')), priority smallint NOT NULL CHECK (priority BETWEEN 1 AND 4), city text NOT NULL, opened_at timestamptz NOT NULL, closed_at timestamptz, estimated_minutes integer NOT NULL CHECK (estimated_minutes >= 0), actual_minutes integer CHECK (actual_minutes >= 0), cost numeric(10,2) NOT NULL CHECK (cost >= 0));CREATE TABLE app.ch05_work_order_tag ( work_order_id integer NOT NULL REFERENCES app.ch05_work_order(work_order_id), tag text NOT NULL, PRIMARY KEY (work_order_id, tag));CREATE TABLE app.ch05_work_order_event ( event_id integer PRIMARY KEY, work_order_id integer NOT NULL REFERENCES app.ch05_work_order(work_order_id), event_at timestamptz NOT NULL, event_type text NOT NULL, detail text);CREATE TABLE app.ch05_team ( team_id integer PRIMARY KEY, team_name text NOT NULL UNIQUE, parent_team_id integer REFERENCES app.ch05_team(team_id));INSERT INTO app.ch05_customer VALUES(1,'Northwind Health','North',true),(2,'Alpine Manufacturing','North',true),(3,'City Library','Central',true),(4,'Delta Foods','South',true),(5,'Evergreen School','South',false);INSERT INTO app.ch05_technician VALUES(10,'Ada','electrical','A'),(11,'Linus','network','A'),(12,'Grace','mechanical','B'),(13,'Ken','network','B');INSERT INTO app.ch05_work_order VALUES(1001,1,10,'closed',1,'Baku','2026-08-01 08:00+00','2026-08-01 10:00+00',90,110,240.00),(1002,1,11,'open',2,'Baku','2026-08-02 09:00+00',NULL,60,NULL,80.00),(1003,2,10,'closed',2,'Sumqayit','2026-08-02 07:30+00','2026-08-02 09:00+00',120,85,150.00),(1004,2,NULL,'queued',3,'Sumqayit','2026-08-03 12:00+00',NULL,45,NULL,50.00),(1005,3,12,'closed',1,'Baku','2026-08-01 06:00+00','2026-08-01 08:30+00',120,150,300.00),(1006,4,13,'cancelled',4,'Ganja','2026-08-04 14:00+00','2026-08-04 14:10+00',30,10,0.00),(1007,4,13,'open',2,'Ganja','2026-08-05 11:00+00',NULL,90,NULL,120.00),(1008,1,10,'closed',1,'Baku','2026-08-06 05:00+00','2026-08-06 06:00+00',60,55,90.00),(1009,3,12,'closed',2,'Baku','2026-08-06 09:00+00','2026-08-06 12:00+00',90,180,400.00),(1010,2,11,'open',1,'Baku','2026-08-07 10:00+00',NULL,0,NULL,75.00);INSERT INTO app.ch05_work_order_tag VALUES(1001,'electrical'),(1001,'urgent'),(1002,'network'),(1003,'electrical'),(1005,'mechanical'),(1005,'safety'),(1007,'network'),(1007,'urgent'),(1008,'electrical'),(1009,'mechanical'),(1009,'urgent'),(1010,'network');INSERT INTO app.ch05_work_order_event VALUES(1,1001,'2026-08-01 08:00+00','created','portal'),(2,1001,'2026-08-01 09:30+00','arrived','Ada on site'),(3,1001,'2026-08-01 10:00+00','closed','restored'),(4,1002,'2026-08-02 09:00+00','created','portal'),(5,1002,'2026-08-02 09:10+00','assigned','Linus'),(6,1003,'2026-08-02 07:30+00','created','monitoring'),(7,1003,'2026-08-02 09:00+00','closed','breaker replaced'),(8,1007,'2026-08-05 11:00+00','created','phone'),(9,1007,'2026-08-05 11:20+00','assigned','Ken'),(10,1008,'2026-08-06 05:00+00','created','monitoring'),(11,1008,'2026-08-06 06:00+00','closed','reset'),(12,1009,'2026-08-06 09:00+00','created','portal'),(13,1009,'2026-08-06 10:00+00','diagnosed','pump wear'),(14,1009,'2026-08-06 12:00+00','closed','pump replaced'),(15,1010,'2026-08-07 10:00+00','created','portal');INSERT INTO app.ch05_team VALUES(1,'Field Operations',NULL),(2,'Electrical',1),(3,'Network',1),(4,'Mechanical',1),(5,'Night Shift',3);
After setup, app.ch05_work_order contains 10 work
orders, including one unassigned row, several NULL completion
values, and one zero-minute estimate. Those details are
intentional: they make outer joins, three-valued logic,
NOT IN, scalar errors, and evaluation-order
mistakes observable.
2. A CTE names a query result inside one statement
A nonrecursive CTE can make a query read like a pipeline: define active work, summarize it, then present the result. The name exists only for the statement. It is not a persisted view or temporary table.
WITH active_work AS ( SELECT work_order_id, customer_id, cost FROM app.ch05_work_order WHERE status IN ('queued','open')),customer_rollup AS ( SELECT customer_id, count(*) AS active_count, sum(cost) AS active_cost FROM active_work GROUP BY customer_id)SELECT c.customer_name, r.active_count, r.active_costFROM customer_rollup rJOIN app.ch05_customer c USING (customer_id)ORDER BY r.active_cost DESC, c.customer_name;customer_name | active_count | active_cost----------------------+--------------+------------Alpine Manufacturing | 2 | 125.00Delta Foods | 1 | 120.00Northwind Health | 1 | 80.00The semantic value is naming and composition. Whether PostgreSQL physically stores the CTE output is a separate planner decision.
3. CTEs are not unconditional optimization fences
For a nonrecursive, side-effect-free SELECT,
PostgreSQL can fold a singly referenced CTE into its parent so
predicates and access paths can be optimized together. A
multiply referenced CTE is normally evaluated once, but you can
request NOT MATERIALIZED to allow folding,
accepting possible duplicate computation.
MATERIALIZED forces a separate CTE result and can
intentionally block some pushdown.
EXPLAIN (COSTS ON)WITH w AS ( SELECT * FROM app.ch05_work_order)SELECT work_order_idFROM wWHERE work_order_id = 1008;EXPLAIN (COSTS ON)WITH w AS MATERIALIZED ( SELECT * FROM app.ch05_work_order)SELECT work_order_idFROM wWHERE work_order_id = 1008;EXPLAIN (COSTS ON)WITH w AS NOT MATERIALIZED ( SELECT * FROM app.ch05_work_order)SELECT work_order_idFROM wWHERE work_order_id = 1008;Do not label one form “faster” universally. Materialization can avoid repeated expensive work; folding can expose selective predicates and indexes. Volatile functions and data-modifying CTEs have additional semantics and are not interchangeable with an ordinary side-effect-free SELECT.
4. Recursive CTE = anchor + recursive term + termination
WITH RECURSIVE allows a CTE to refer to its own
accumulated result. Think in iterations: the anchor creates the
starting working set; the recursive term derives the next rows;
execution stops when the recursive term produces no new rows for
the working process.
WITH RECURSIVE org AS ( SELECT team_id, team_name, parent_team_id, 0 AS depth, ARRAY[team_id] AS path FROM app.ch05_team WHERE team_id = 1 UNION ALL SELECT child.team_id, child.team_name, child.parent_team_id, parent.depth + 1, parent.path || child.team_id FROM app.ch05_team child JOIN org parent ON child.parent_team_id = parent.team_id)SELECT team_id, team_name, depth, pathFROM orgORDER BY path;team_id | team_name | depth | path--------+------------------+-------+---------1 | Field Operations | 0 | {1}2 | Electrical | 1 | {1,2}3 | Network | 1 | {1,3}5 | Night Shift | 2 | {1,3,5}4 | Mechanical | 1 | {1,4}The array path is both explanatory and useful for ordering. It also gives you the raw material to detect revisiting a node.
5. Deliberately wrong recursion: no termination rule
This query continually generates another integer. Do not execute
unbounded recursion without a guard. In a disposable session, a
short local statement_timeout can demonstrate the
failure safely.
BEGIN;SET LOCAL statement_timeout = '500ms';WITH RECURSIVE bad(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM bad)SELECT max(n) FROM bad;ROLLBACK;-- Correct finite rule:WITH RECURSIVE ok(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM ok WHERE n < 5)SELECT * FROM ok;ERROR: canceling statement due to statement timeout-- after rollback, finite query returns 1,2,3,4,5A timeout is a safety net, not a business termination rule. Real recursion should be finite because the graph semantics and cycle policy make it finite.
6. Cycle-safe traversal with CYCLE
A parent-child table can still contain a cycle if constraints do
not forbid one. PostgreSQL supports a SQL-standard-style
CYCLE clause that tracks visited keys and exposes
both an is_cycle marker and a path column. The
rewritten recursion avoids continuing through rows already
identified as cycles.
BEGIN;-- Creates 1 -> 3 -> 5 -> 1 when traversing parent-to-child.UPDATE app.ch05_team SET parent_team_id = 5 WHERE team_id = 1;WITH RECURSIVE org(team_id, team_name, parent_team_id, depth) AS ( SELECT team_id, team_name, parent_team_id, 0 FROM app.ch05_team WHERE team_id = 1 UNION ALL SELECT child.team_id, child.team_name, child.parent_team_id, org.depth + 1 FROM app.ch05_team child JOIN org ON child.parent_team_id = org.team_id)CYCLE team_id SET is_cycle USING cycle_pathSELECT team_id, team_name, depth, is_cycle, cycle_pathFROM orgORDER BY depth, team_id;ROLLBACK;The exact path formatting is implementation-visible diagnostic output; the important contract is that the repeated team ID is marked as a cycle and recursion does not continue indefinitely through that branch. The rollback restores the original hierarchy.
7. SEARCH controls traversal ordering metadata, not recursion semantics
PostgreSQL also supports SEARCH DEPTH FIRST and
SEARCH BREADTH FIRST clauses that synthesize an
ordering column. They are useful when presentation order
matters, but they do not change which rows logically belong to
the recursive result. Ordering is applied when you sort by the
generated key.
WITH RECURSIVE org(team_id, team_name, parent_team_id) AS ( SELECT team_id, team_name, parent_team_id FROM app.ch05_team WHERE team_id = 1 UNION ALL SELECT c.team_id, c.team_name, c.parent_team_id FROM app.ch05_team c JOIN org p ON c.parent_team_id = p.team_id)SEARCH DEPTH FIRST BY team_id SET order_pathSELECT team_id, team_name, order_pathFROM orgORDER BY order_path;8. Hands-on lab: separate readability, recursion, and planner control
-- A. Readable nonrecursive layer.WITH closed AS ( SELECT customer_id, cost FROM app.ch05_work_order WHERE status='closed')SELECT customer_id, count(*) AS n, sum(cost) AS totalFROM closedGROUP BY customer_idORDER BY customer_id;-- B. Hierarchy descendants of Network (team 3).WITH RECURSIVE descendants AS ( SELECT team_id, team_name, parent_team_id, 0 AS depth FROM app.ch05_team WHERE team_id=3 UNION ALL SELECT c.team_id, c.team_name, c.parent_team_id, d.depth+1 FROM app.ch05_team c JOIN descendants d ON c.parent_team_id=d.team_id)SELECT * FROM descendants ORDER BY depth, team_id;-- C. Compare plan shape only after result correctness.EXPLAIN (COSTS ON)WITH w AS MATERIALIZED (SELECT * FROM app.ch05_work_order)SELECT * FROM w WHERE work_order_id=1001;Check your understanding
- Why is “CTEs are always optimization fences” inaccurate in current PostgreSQL?
- What are the anchor and recursive terms responsible for?
- Why is statement_timeout not a substitute for a recursion termination rule?
- What does the CYCLE clause add to recursive traversal?
- When might MATERIALIZED help, and when might NOT MATERIALIZED help?
Review the answers
Current PostgreSQL can fold eligible side-effect-free CTEs
into the parent query. The anchor seeds the working set;
the recursive term derives later rows. A timeout only
aborts runaway work, whereas correct recursion needs
finite semantics. CYCLE tracks visited keys,
marks repeated keys, and prevents endless traversal of
that cycle. Materialization can avoid repeated expensive
work or create a deliberate optimization boundary; NOT
MATERIALIZED can expose parent predicates but may repeat
computation.
9. Production judgment and bridge
Use CTEs to make relational stages explicit and recursive CTEs
when the problem really is iterative graph/hierarchy expansion.
Treat materialization keywords as targeted planner controls
backed by evidence, not style preferences. The final lesson of
this chapter composes complete result sets with
UNION/INTERSECT/EXCEPT, PostgreSQL-specific
DISTINCT ON, and row constructors.