What is a Recursive Common Table Expression

Introduction to Recursive CTEs

A Recursive Common Table Expression (CTE) is a CTE that references itself, allowing SQL to traverse hierarchical or graph-shaped data — organization charts, category trees, bill-of-materials structures, or file systems — without procedural code. It is defined with WITH RECURSIVE (or plain WITH in SQL Server) and consists of an anchor query plus a recursive query combined with UNION ALL.

Basic Syntax

WITH RECURSIVE cte_name AS (
  -- Anchor member: the starting rows
  SELECT ...
  UNION ALL
  -- Recursive member: references cte_name
  SELECT ... FROM some_table JOIN cte_name ON ...
)
SELECT * FROM cte_name;

The anchor runs once; the recursive member then runs repeatedly against the rows produced in the previous iteration until no new rows are returned.

Example: Traversing an Organization Chart

WITH RECURSIVE org_chart AS (
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL          -- the CEO

  UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level, name;

This returns every employee with their depth in the reporting hierarchy, starting from the top-level manager.

Common Use Cases

Hierarchies: org charts, category and menu trees, threaded comments.
Graph traversal: reachability questions such as "which parts depend on part X".
Sequence generation: producing a series of numbers or dates without a helper table.

Tips and Pitfalls

Use UNION ALL rather than UNION unless you specifically need duplicate elimination — it is faster and often required.
Guard against infinite recursion: add a depth column and a WHERE level < n condition, or use the database's recursion limit (MAXRECURSION in SQL Server, max_recursive_iterations in MariaDB).
Ensure the recursive join condition actually narrows the result; cycles in the data (A manages B, B manages A) must be detected explicitly.

Writing Recursive CTEs with Chat2DB

Recursive queries are easy to get wrong by hand. Chat2DB (opens in a new tab) can generate a working recursive CTE from a natural-language description like "show the full category tree with depth", then let you refine and visualize the result.