Recursive CTEs
Overview
Standard SQL is terrible at traversing hierarchical trees or graphs (e.g., finding the 'Manager of the Manager of the Manager' up a corporate ladder, or traversing a file system directory). A Recursive CTE solves this by calling itself in a loop until it runs out of data. It consists of an 'Anchor' query (the starting point) and a 'Recursive' query (the loop), glued together with a UNION ALL.
Syntax
-- Syntax: WITH RECURSIVE name AS (...)
WITH RECURSIVE OrgChart AS (
-- 1. The ANCHOR: Start with the CEO (manager_id is NULL)
SELECT emp_id, name, manager_id, 1 AS hierarchy_level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 2. The RECURSION: Join the CTE to the table to find the direct reports!
SELECT e.emp_id, e.name, e.manager_id, oc.hierarchy_level + 1
FROM employees e
-- The CTE literally JOINs against itself!
JOIN OrgChart oc ON e.manager_id = oc.emp_id
)
-- 3. Execute the results
SELECT * FROM OrgChart ORDER BY hierarchy_level;Common Pitfalls
- Infinite Loops. If your data has a circular reference (Employee A reports to Employee B, and Employee B reports to Employee A), the recursive CTE will loop infinitely and crash the database server. Always ensure your data is a strict Directed Acyclic Graph (DAG), or implement a depth-limit counter (e.g.,
WHERE level < 100). - Using
UNIONinstead ofUNION ALL. Recursive CTEs strictly requireUNION ALLto bind the anchor to the recursive step. StandardUNIONwill fail or cause massive performance degradation.
Interview Questions
The recursion automatically halts when the recursive step (the query after the UNION ALL) executes and returns exactly ZERO rows. Once it finds no new children, the loop terminates.
Real-World Example
Generating a sequential list of dates for a calendar report (e.g., ensuring days with 0 sales still show up on a chart).
WITH RECURSIVE DateGenerator AS (
-- Anchor: Start at the beginning of the month
SELECT '2026-01-01'::DATE AS report_date
UNION ALL
-- Recursion: Add 1 day until we hit the end of the month!
SELECT report_date + INTERVAL '1 day'
FROM DateGenerator
WHERE report_date < '2026-01-31'
)
SELECT * FROM DateGenerator;Check Your Knowledge
Test your understanding of Recursive CTEs with these quick questions.