Recursive CTEs

Recursive CTEs are one of the most advanced features in SQL. They allow a query to reference its own output, enabling you to traverse hierarchies of unknown depth, such as organizational charts, bill-of-materials, or folder structures.

Definition: The Recursive Anatomy

A recursive CTE consists of three main parts:

1. Anchor Member: A base query that returns the starting point of the recursion (e.g., the CEO).
2. Recursive Member: A query that joins the CTE to itself to find the “next level” of data.
3. Termination Condition: Implicitly, the recursion stops when the recursive member returns no new rows.

Context: Why Recursion?

Without recursive CTEs, if you wanted to find everyone under a manager in a 10-level hierarchy, you would need to write 10 separate JOIN statements. If a 11th level was added, your code would break. A recursive CTE handles any number of levels automatically.

Detailed Explanation: Syntax (Postgres/MySQL)

Recursive Org Chart

WITH RECURSIVE OrgChart AS (
  -- 1. Anchor: Start with the CEO
  SELECT ID, Name, ManagerID, 1 AS Level
  FROM Employees
  WHERE ManagerID IS NULL

  UNION ALL

  -- 2. Recursive: Find people who report to anyone already in OrgChart
  SELECT e.ID, e.Name, e.ManagerID, oc.Level + 1
  FROM Employees e
  JOIN OrgChart oc ON e.ManagerID = oc.ID
)
SELECT * FROM OrgChart;

Example: Generating a Number Series

Recursion isn’t just for tables; you can use it to generate data on the fly.

Task: Generate a list of dates for the next 7 days.

Data Generation

WITH RECURSIVE NextWeek AS (
  SELECT CURRENT_DATE AS DateValue -- Anchor
  UNION ALL
  SELECT DateValue + 1 FROM NextWeek -- Recursive
  WHERE DateValue < CURRENT_DATE + 6 -- Limit
)
SELECT * FROM NextWeek;

Under the Hood: The Working Table

How does the database execute recursion without an infinite loop?

1. Evaluate Anchor: The engine runs the anchor query and puts the results into two places: the Final Result and a Working Table.
2. Loop:
   • The engine runs the recursive member, using the current Working Table as the input for the join.
   • The results are added to the Final Result.
   • The results become the new Working Table.
3. Terminate: The loop repeats until the Working Table is empty.

The Infinity Trap

If your data has a Cycle (e.g., Alice reports to Bob, and Bob reports to Alice), a recursive CTE will run forever until it crashes the server or hits a safety limit.

Caution

Safety First:
Most databases have a maximum recursion depth (e.g., 100 levels) to prevent runaway queries. In SQL Server, you can control this with OPTION (MAXRECURSION n).

Summary of Advanced Logic

Feature	Best Use Case
Scalar Subquery	Comparison against a single calculated value (e.g., average).
EXISTS	Checking if a relationship exists without returning data.
Standard CTE	Breaking complex queries into readable, modular steps.
Recursive CTE	Navigating hierarchies (Org charts, folder trees).