Recursive CTE
SQL · Extra Concepts
Theory
A recursive CTE is a CTE that refers to itself, letting SQL walk through hierarchies such as org charts, category trees or bill-of-materials. It has two parts joined by UNION ALL. The anchor (base case) selects the starting rows, for example the CEO whose manager_id IS NULL. The recursive part joins the table back to the CTE's previous rows to find the next level, for example employees whose manager_id matches an id already found.
The database runs it in rounds: it runs the anchor, then repeatedly runs the recursive part using only the rows produced in the last round, and stops when a round returns no new rows. A level counter (level + 1) records the depth. PostgreSQL, MySQL, SQLite and Snowflake write WITH RECURSIVE; SQL Server and Oracle write just WITH. If the data contains a cycle (A manages B, B manages A), the recursion never ends, so add a depth limit such as WHERE level < 10, or use the database's cycle-detection options.
Why we use it
Hierarchies can be any depth, so you cannot write a fixed number of self-joins. A recursive CTE follows parent-child links level by level until it reaches the bottom, whatever the depth.
When to use it
Use it for org charts, folder or category trees, route finding and generating number or date series. For a fixed, known depth (just manager and employee) a simple self-join is easier. Always guard against cycles.
How it works
- Run the anchor: rows WHERE manager_id IS NULL (level 1)
- Run the recursive part: join employees to the last round's rows
- Add the new rows with level + 1
- Did this round add new rows? Yes: Recurse again on those rows. No: Stop recursing.
- Return all rows collected (UNION ALL)
Syntax
WITH RECURSIVE cte_name AS (
base_case
UNION ALL
recursive_case
)
SELECT * FROM cte_name;Example
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;Key points
- Needs a base case (anchor) and a recursive case joined with UNION ALL
- Classic use case: employee-manager hierarchies
- Stops when a round returns no new rows
- SQL Server and Oracle omit the RECURSIVE keyword
- Cycles in the data loop forever — add a level limit