Recursive CTEs in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
What are Recursive CTEs in SQL?
A recursive CTE lets a query reference itself, accumulating rows iteratively until nothing new turns up. It's designed for hierarchical data — org charts, category trees, folder structures — where the depth isn't known in advance and can't be handled with a fixed number of joins.
How do you write a recursive CTE in PostgreSQL?
The structure has two parts separated by UNION ALL: an anchor member and a recursive member. The anchor produces the starting rows. The recursive member references the CTE by name and extends the result by one step per iteration. PostgreSQL runs the anchor first, then keeps running the recursive member using the rows from the previous pass, until no new rows are produced.
The mechanics are easiest to see with a simple counter first:
WITH RECURSIVE numbers AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 10 ) SELECT n FROM numbers
The same structure traverses the org chart — the anchor is the root employee, each recursive step adds one level:
WITH RECURSIVE org_hierarchy AS (
-- Anchor: start from the top (no manager)
SELECT employee_id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: add each employee whose manager is already in the result
SELECT e.employee_id, e.name, e.manager_id, oh.depth + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, name, depth
FROM org_hierarchy
ORDER BY depth, nameThe anchor selects the root: the employee with no manager. The recursive member joins each subsequent employee to whoever is already in the accumulated result, matching their manager_id to an employee_id already found. Each pass adds one level. Execution stops when no employees remain that can be joined to the current set.
The depth column is a counter that increments by one per level. Carrying a counter through the recursion is standard — useful for output ordering and for enforcing a termination limit.
What happens when a recursive CTE hits a cycle?
The one thing that trips people up
A recursive CTE terminates naturally when the recursive member returns zero new rows. That works correctly for clean tree structures. But if the data has cycles — a manager who reports to someone who reports back to them — the recursion never terminates and the query runs until it hits a resource limit.
PostgreSQL doesn't detect cycles automatically. The practical defense is a depth limit in the recursive member's WHERE clause:
WHERE oh.depth < 20This stops recursion at 20 levels regardless of whether the data has further rows. It's a blunt guard, but it's reliable when you know a reasonable maximum depth.
Also: the RECURSIVE keyword in WITH RECURSIVE is required even when the recursion seems obvious. PostgreSQL uses it as a signal to enable the iterative evaluation mechanism. Leave it out and the query errors.
Should you use generate_series or a recursive CTE?
generate_series vs recursive CTEs
Use generate_series() when the sequence structure is known before the query runs — every day from A to B, every integer from 1 to N. Use a recursive CTE when the structure is encoded in the data and must be discovered during execution — traverse this org chart to whatever depth the data has.
Practice Recursive CTEs in SQL
Brightlane's data pipeline assigns sequential batch identifiers to processing jobs. Each identifier is one greater than the previous, starting at 1.
Write a query to return a sequence of batch identifiers from 1 through 5, with each value appearing as a separate row.
Output:
- Five rows, with one column,
job_id, containing the integers1through5in ascending order.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
trace the reactor with a recursive CTE in Station Zero10 Recursive CTEs practice problems
Write a query to return a sequence of batch identifiers from 1 through 5, with each value appearing as a separate row.
Write a query to return the ID, name, and depth level for every category in the hierarchy.
Write a query to return the ID, name, and depth level for every employee in those top two levels.
Write a query to return the ID, name, and depth level for every employee.
Write a query to return every depth level and the number of employees at that level.
Write a query to return the ID and name of every employee who reports to manager id = 2 at any level of the hierarchy.
Write a query to return every category's name, depth level, and number of products assigned directly to that category.
Write a query to return the ID, name, and step number for every person in the chain. Employee id = 28 is at step 1, their direct manager at step 2, and so on up to the CEO.
Write a query to return the ID, name, and depth level for employee id = 2 and every employee who reports to them at any level.
Write a query to return every qualifying depth level and its employee count.
Start learning to practice all 10 Recursive CTEs problems, with instant grading and mastery tracking.
Common questions about Recursive CTEs
Does a recursive CTE have to use UNION ALL?
No, UNION works too and deduplicates as it goes, which can stop a cyclic graph from looping forever. UNION ALL is the usual choice because it does less work, so reach for UNION deliberately when repeated rows are the problem you are solving.
What does the RECURSIVE keyword actually do?
It makes the CTE name visible inside its own definition. Without it the self-reference fails with the relation not existing, because a plain CTE is not in scope for itself. The keyword goes after WITH, once, even when several CTEs follow.
How do you stop a recursive CTE running away?
Carry a depth counter and test it in the recursive branch. It is a blunt guard, but PostgreSQL does not detect cycles for you, so a hierarchy with a loop in the data will otherwise run until it exhausts a resource limit.
