CTEs — Common Table Expressions & Recursive CTEs

Complex queries built as deeply nested subqueries become unreadable fast — CTEs exist to let you name and structure intermediate query steps clearly, the same way naming a variable clarifies a piece of application code instead of inlining it everywhere.

Hierarchical data (an org chart, a category tree, a comment thread) has a variable, unknown depth — a fixed number of JOINs can't traverse "however many levels deep this happens to go." Recursive CTEs are SQL's built-in mechanism for traversing exactly that kind of structure, however deep it turns out to be.

CTE (Common Table Expression) — a named, temporary result set defined with WITH, usable within the rest of that single query, as if it were a table. Recursive CTE — a CTE that references itself, with a base case (the starting rows) and a recursive case (rows built from the previous iteration), continuing until the recursive case returns no more rows.

A Common Table Expression (CTE) is a named temporary result set defined within a WITH clause, available for the duration of the query. CTEs improve readability, enable recursion, and can replace complex subqueries.

CTE syntax

WITH cte_name AS (

SELECT ...

)

SELECT * FROM cte_name;

Multiple CTEs

WITH cte1 AS (...), cte2 AS (...), cte3 AS (...)

SELECT ...

CTE types

Regular CTE — improves readability; often (not always) inlined by optimizer.
Recursive CTE — enables hierarchical/graph traversal without application-level loops.
Materialized CTE — WITH ... AS MATERIALIZED forces evaluation once (PostgreSQL 12+).
Non-Materialized CTE — WITH ... AS NOT MATERIALIZED inlines the CTE (optimizer choice).
Writeable CTE — CTE can contain INSERT, UPDATE, DELETE (PostgreSQL). Very powerful.

Recursive CTE structure

WITH RECURSIVE cte AS (

-- Anchor member (starting point, non-recursive)

SELECT ... FROM base_table WHERE condition

UNION ALL

-- Recursive member (references the CTE itself)

SELECT ... FROM other_table

JOIN cte ON joining_condition

)

SELECT * FROM cte;

Regular CTE execution

PostgreSQL optimizer decides to inline or materialize the CTE.
If inlined: CTE SQL is substituted inline — optimizer sees through it and optimizes.
If materialized: CTE evaluated once, result stored in a temporary working table.
Main query uses the CTE result.

Recursive CTE execution

Anchor member evaluated — produces initial rows (working table T0).
Recursive member evaluates with T0 as input — produces T1.
Recursive member evaluates with T1 as input — produces T2.
Continues until recursive member produces 0 rows (no new rows to add).
All rows from T0, T1, T2... are UNION ALLed together.
Main query receives the accumulated result.
Safety: CYCLE detection (PostgreSQL 14+) or manual depth limit prevents infinite loops.

Step 1: Write the anchor SELECT — identifies starting rows (WHERE manager_id IS NULL for root).

Step 2: Write UNION ALL and the recursive SELECT.

Step 3: In recursive SELECT: JOIN the CTE to get children of current level.

Step 4: Add a depth/level column to track hierarchy level (depth + 1).

Step 5: Build path string for visualization: path || emp_name.

Step 6: Add cycle detection with CYCLE clause (PostgreSQL 14+) or manual array check.

Step 7: Add LIMIT on recursion depth if needed: WHERE depth < 10.

Use CTEs to break complex queries into readable named steps — each CTE has a clear purpose.
MATERIALIZED CTEs when: the subquery is expensive and used multiple times; optimizer might re-evaluate otherwise.
NOT MATERIALIZED (default PostgreSQL 12+): let optimizer inline for better plan with indexes.
Always add CYCLE detection for recursive CTEs — prevent infinite loops on data with circular references.
Add depth limit WHERE depth < 100 — safety net for deep or cyclic hierarchies.
Recursive CTE for hierarchies — avoid multiple self-joins or application-level loops.
Writeable CTEs for atomic multi-step DML — update + audit in one statement.
Test recursive CTEs with small data first — verify anchor and recursive members independently.
Use generate_series() (PostgreSQL) for date/number sequences instead of recursive CTE.
Named CTEs improve maintainability — changes in one CTE automatically propagate to referencing queries.

Infinite recursion — forgetting the WHERE/JOIN condition that terminates recursion. Always test with LIMIT first.
UNION instead of UNION ALL in recursive CTE — UNION deduplicates on each iteration (very slow); almost always use UNION ALL.
Assuming CTE is always materialized (PostgreSQL 12+) — default is inline. Use AS MATERIALIZED if you need one evaluation.
Not handling cycles in data — self-referential relationships can have cycles (employee is their own manager due to data error).
Referencing a CTE multiple times thinking it runs once — without MATERIALIZED, may run multiple times.
Complex recursive logic — debug by adding WHERE depth < 3 and checking intermediate results.
Forgetting all recursive CTEs need RECURSIVE keyword — WITH RECURSIVE (not just WITH).

Non-materialized CTE (default PostgreSQL 12+): optimizer can push WHERE predicates into the CTE — better index use.
Materialized CTE: prevents multiple evaluations of expensive subqueries; trades recomputation for memory.
Recursive CTE performance: dependent on depth and rows per level; index the JOIN column (manager_id).
Alternative to deep recursion: store the full path in the table (materialized path pattern) for very deep hierarchies.
For static hierarchies: closure table pattern — pre-compute all ancestor-descendant pairs; O(1) lookups.
ltree extension (PostgreSQL): optimized for hierarchical data with GiST index support.

A recursive CTE with no depth limit and a cyclic underlying data structure (e.g., a corrupted org-chart with a reporting loop) can run indefinitely, consuming resources — always add an explicit iteration/depth cap in production recursive CTEs as a safety bound, regardless of how clean you believe the underlying data is.

Watch execution time and row counts for recursive CTE queries specifically as the underlying hierarchical data grows — a recursive CTE that was instant over a 50-row org chart can become noticeably slower over a 5,000-row one, and it's worth tracking that trend before it becomes a user-facing complaint.

Use CTEs for all complex queries in production — much easier to maintain than deeply nested subqueries.
Document CTE purpose with inline comments: -- CTE 1: calculate department statistics.
Test recursive CTEs with real data including edge cases (root has no manager, leaf has no children).
Consider materialized path or closure table for hierarchies > 6 levels deep — recursive CTE can be slow.
For true graph traversal at scale (not just shallow hierarchies), consider a graph database (Neo4j) or the Apache AGE extension — recursive CTEs get slow on deep, wide graphs.

Write a recursive CTE to traverse the employee hierarchy starting from Eve (CEO). Display each employee with their level (0=CEO, 1=direct report, etc.) and full path (Eve > Alice > Carol).
Find all employees who report to Alice (directly or indirectly) at any level using a recursive CTE.
Write a writeable CTE that gives 10% raise to all Engineering employees and simultaneously inserts records into a salary_audit table.
Generate a complete calendar for 2024 (all 366 days) using a recursive CTE. Then rewrite using generate_series() and compare.

CTE (WITH clause): named temporary result; improves readability; reusable in the same query.
Recursive CTE: ANCHOR UNION ALL RECURSIVE — traverses hierarchies/graphs without application loops.
PostgreSQL 12+: CTEs inlined by default; use AS MATERIALIZED to force single evaluation.
CYCLE detection: prevents infinite loops in recursive CTEs (PostgreSQL 14+).
Writeable CTE: DML (INSERT/UPDATE/DELETE) + RETURNING — atomic multi-step DML in one statement.
Always UNION ALL (not UNION) in recursive member — performance and correctness.
For deep hierarchies (> 6 levels): consider materialized path or closure table pattern.