DocsAdvancedRecursive CTEs

Recursive CTEs

Advanced

Concept

A recursive CTE is a CTE that references itself, enabling iterative processing of hierarchical or graph-structured data. It consists of two parts: the anchor member (the starting point, a non-recursive query) and the recursive member (a query that references the CTE itself and extends the result set iteratively). The recursion stops when the recursive member produces no new rows.

Recursive CTEs are the standard SQL mechanism for traversing trees and graphs: organizational hierarchies (employees and managers), category trees, bill-of-materials structures, flight route networks, and more. Each iteration adds one level of depth to the traversal.

Safety is important with recursive CTEs because an incorrect termination condition leads to infinite recursion. Most databases impose a default recursion limit (e.g., 100 iterations in PostgreSQL, configurable with SET max_recursive_iterations). Always include a WHERE condition in the recursive member that ensures progress toward termination.

Syntax

-- Basic recursive CTE structure
WITH RECURSIVE cte_name AS (
  -- Anchor member: starting rows
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive member: extend from previous iteration
  SELECT c.id, c.parent_id, c.name, cte.depth + 1
  FROM categories c
  JOIN cte_name cte ON cte.id = c.parent_id
)
SELECT * FROM cte_name;

-- Generate a series of numbers
WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n FROM nums;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Employee hierarchy with full management chain
WITH RECURSIVE org_chart AS (
  -- Anchor: top-level managers (no manager_id)
  SELECT
    employee_id,
    name,
    manager_id,
    name AS management_chain,
    0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: each employee joined to their manager
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    oc.management_chain || ' > ' || e.name,
    oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON oc.employee_id = e.manager_id
)
SELECT
  employee_id,
  name,
  depth,
  management_chain
FROM org_chart
ORDER BY management_chain;

Common Pitfalls & Tips

1Infinite recursion occurs if the recursive member does not converge. Always include a termination condition (e.g., WHERE depth < 20 or a join that eventually matches no rows).
2UNION ALL is typically used in recursive CTEs. UNION (with deduplication) can work but adds overhead and changes semantics. Only use UNION if you need cycle detection.
3Recursive CTEs on large graphs can be memory-intensive. For very deep or wide hierarchies, consider limiting depth or paginating the traversal.

Practice recursive CTE queries