Recursive CTE
CTE that references itself: iteration inside a single SQL statement. Structure: an anchor SELECT (the seed rows), UNION ALL, and a recursive SELECT that joins back to the CTE. Each iteration feeds the next; the CTE terminates when the recursive member produces no new rows. Used for graphs, trees, hierarchies (manager → reports), and generating series.
Syntax #
WITH RECURSIVE cte (cols) AS (
-- anchor
SELECT ...
UNION ALL
-- recursive step
SELECT ...
FROM source_table s
JOIN cte ON s.parent = cte.id
)
SELECT * FROM cte;Example #
Always guarantee termination. Every recursive CTE needs a predicate in the recursive member that eventually produces zero new rows: a depth counter (WHERE depth < 10), a boundary condition (WHERE d < end_date), or an accumulated-path check for cyclic graphs. Without one, behavior splits by engine: SQL Server stops at MAXRECURSION (default 100), MySQL at cte_max_recursion_depth (default 1000), DuckDB caps to prevent runaway, and Postgres has no built-in limit. It runs until statement_timeout, cancellation, or resource exhaustion. On cycles without a guard, the query loops and consumes all memory. For graph traversal, carry a path array and skip already-visited nodes.