What is a Recursive CTE and When Would You Use One?
Learn how a recursive CTE traverses hierarchical data like org charts, with anchor and recursive members explained through examples.
Expected Interview Answer
A recursive CTE is a Common Table Expression that references itself, repeatedly building on its own previous output until no new rows are produced, making it the standard SQL tool for walking hierarchical or graph-shaped data such as org charts, category trees, or bill-of-materials structures.
A recursive CTE has two parts joined by UNION ALL: an anchor member, which is a plain query producing the starting rows, and a recursive member, which references the CTE's own name and joins it back to the base table to find the next level. The database repeatedly executes the recursive member, feeding each pass's output back in as input to the next, until a pass produces zero rows, at which point recursion stops and all accumulated rows are returned. Typical use cases include listing every employee under a manager regardless of depth, expanding a product category tree, or traversing a parts explosion in manufacturing, none of which have a fixed, known number of levels ahead of time.
- Handles arbitrary-depth hierarchies without hardcoding levels
- Expresses tree and graph traversal directly in SQL
- Avoids application-side loops calling the database repeatedly
- Naturally supports computing depth or path as it recurses
AI Mentor Explanation
Think of tracing a bowling action's full run-up by starting at the delivery stride and repeatedly asking "what was the step just before this one" until you reach the very first step of the approach, at which point there is no earlier step left to find. Each answer feeds the next question, and the process naturally stops when it runs out of prior steps. A recursive CTE works the same way: it repeatedly joins its own output back to the source table to find the next level, stopping automatically when a pass returns nothing new.
Step-by-Step Explanation
Step 1
Write the anchor member
Define the base case query that produces the starting rows, such as the top-level manager or root category.
Step 2
Add UNION ALL and the recursive member
Write a query that joins the CTE name back to the source table to find the next level down or up.
Step 3
Let the engine iterate
The database repeatedly re-executes the recursive member using the prior pass output, accumulating rows each time.
Step 4
Recursion terminates automatically
When a pass produces zero new rows, the engine stops and returns the full accumulated result set.
What Interviewer Expects
- Correct explanation of anchor member plus recursive member joined by UNION ALL
- A concrete hierarchical example such as an org chart or category tree
- Understanding that recursion stops when a pass returns no rows
- Awareness of infinite recursion risk with cyclic data and how to guard against it
Common Mistakes
- Using UNION instead of UNION ALL, which can silently suppress needed duplicate rows or hide cycles
- Forgetting the recursive member must reference the CTE by its own name
- Not considering cyclic data causing infinite recursion without a depth or visited-path guard
- Assuming a recursive CTE is required for simple, fixed-depth joins where a normal JOIN suffices
Best Answer (HR Friendly)
“A recursive CTE is a CTE that refers back to itself to walk through data that has an unknown number of levels, like an org chart where I don't know in advance how many layers of management exist. I write one query for the starting point and another that keeps joining back to itself to pull in the next level, and it automatically stops once there's nothing more to find.”
Code Example
WITH RECURSIVE org_chart AS (
-- Anchor member: the top-level manager
SELECT employee_id, name, manager_id, 1 AS depth
FROM Employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: join back to find the next reporting level
SELECT e.employee_id, e.name, e.manager_id, o.depth + 1
FROM Employees e
JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT employee_id, name, depth
FROM org_chart
ORDER BY depth;Follow-up Questions
- How do you prevent infinite recursion on cyclic data in a recursive CTE?
- What is the difference between UNION and UNION ALL inside a recursive CTE?
- How would you compute the full path string from root to each node using a recursive CTE?
- When would a recursive CTE be a poor choice compared to a materialized closure table?
MCQ Practice
1. A recursive CTE consists of which two parts joined together?
The anchor member supplies the starting rows and the recursive member repeatedly joins back to the CTE itself, combined with UNION ALL.
2. When does a recursive CTE stop executing?
The engine keeps re-running the recursive member until a pass yields no additional rows, then it stops and returns all accumulated rows.
3. Which scenario is a classic use case for a recursive CTE?
Hierarchies of unknown depth, like org charts or category trees, are exactly what recursive CTEs are designed to traverse.
Flash Cards
What is a recursive CTE? — A CTE that references itself to repeatedly build on prior output, typically for hierarchical data.
What two parts make up a recursive CTE? — An anchor member for starting rows and a recursive member joined by UNION ALL.
When does recursion stop? — Automatically, once a recursion pass produces zero new rows.
Give a classic use case. — Traversing an org chart or category tree with an unknown number of levels.