Recursive CTEs
Recursive common table expressions iterate a working set until exhaustion, expressing tree and graph traversals — bill-of-material explosions, org charts, dependency chains — in pure SQL.
Definition
A recursive CTE has an anchor part producing initial rows and a recursive part referencing the CTE itself. The engine iterates: apply the recursive term to the previous iteration results, accumulate, and stop when an iteration yields no rows. UNION ALL keeps duplicates, UNION deduplicates and can terminate cycles in graphs.
Context and scope
Used for hierarchical data without ORM recursion, transitive closure queries, category trees, and graph-style reachability. In PostgreSQL it also powers graph search with cycle detection arrays.
Common misconceptions
- Recursive CTEs execute the same plan repeatedly — each iteration can use indexes; the pattern is iteration, not a single scan
- UNION ALL is just faster — it never deduplicates, so cyclic graphs loop forever; UNION provides the termination guarantee
- Recursion depth is engine-limited — engines mostly stop when iterations produce no new rows; runaway recursion comes from data cycles
Anti-patterns
- Traversing cyclic graphs with UNION ALL and creating an infinite query
- Joining the recursive term against the whole base table each iteration without an index on the join column
- Fetching entire trees when the question is depth or path for one node, and paying full traversal cost
Related concepts
Related examples
Authoritative source
What it is
Some queries are inherently iterative: find all parts under an assembly, all reports under a manager, all reachable nodes. A recursive CTE gives SQL that loop: seed rows, a rule that extends them, and a termination condition of empty iteration.
How it works
The anchor produces the seed set. The recursive term runs against the previous iteration output, appending results to the working table. Iterations continue until the recursive term returns zero rows. PostgreSQL exposes the search path and cycle detection; depth tracking comes from carrying a level or path column through iterations.
When to use it
Tree traversal in schema, transitive dependencies in package or BOM graphs, and adjacency-list models where application-side recursion would round-trip per level. Materializing paths or nested sets is the alternative when read volume dwarfs write volume.
Pitfalls
Cycle safety requires UNION dedup or explicit visited-set tracking with arrays. Each iteration re-evaluates the recursive term, so index the join columns it uses. Very deep chains grow the working set; engines cap iterations, and hitting the cap mid-traversal is an operational error, not a query result.