Recursive CTE

A recursive CTE is a CTE that refers to itself. It starts from some rows, then keeps joining its own previous output back to the table to find the next level, until no new rows come out. It is how SQL walks a tree or a chain, such as who reports to whom.

Parts of a recursive CTE

• The anchor member: the starting rows (the employee you start with).
• The recursive member: the step that finds the next level using the CTE's own previous result.
• UNION ALL between them.
• A natural stop: the recursive member returns no rows when it reaches the top.

Reporting chain of employee 7

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 7

  UNION ALL

  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.id = c.manager_id
  WHERE c.depth < 20
)
SELECT * FROM chain ORDER BY depth;

It returns employee 7, then their manager, then that manager's manager, up to the CEO, whose manager_id is NULL so the join finds nothing and recursion stops. SQL Server writes WITH without the word RECURSIVE. Postgres, BigQuery, Snowflake and MySQL 8 use WITH RECURSIVE.

Protect against loops

If the data has a cycle (A reports to B, B reports to A, usually a data-entry error), the recursion never ends. Two guards: a depth column with a limit, as above, or tracking visited ids. Engines also have their own safety cap. SQL Server stops at 100 recursion levels unless you set OPTION (MAXRECURSION n). Do not rely on that as your design.

Where it is used

Org charts, product category trees, bill of materials, and generating a date spine without a calendar table. In a warehouse with a good date dimension you will rarely need the last one. For very deep or wide hierarchies, people often flatten the tree once into a bridge table, so reports do not recurse on every query.