Recursive CTE Patterns
Recursive CTEs are ideal for org charts, category trees, and dependency chains.
Recursion Components
| Part | Purpose |
|---|---|
| Anchor query | Starting rows |
| Recursive query | Expands from previous level |
| Stop condition | Prevents infinite recursion |
Hierarchy Example
WITH RECURSIVE org_tree AS (
SELECT employee_id, manager_id, full_name, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.employee_id
WHERE ot.depth < 10
)
SELECT employee_id, manager_id, full_name, depth
FROM org_tree
ORDER BY depth, employee_id;
| employee_id | manager_id | full_name | depth |
|---|---|---|---|
| 1 | NULL | CEO | 0 |
| 5 | 1 | VP Engineering | 1 |
| 12 | 5 | Engineering Manager | 2 |
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Missing recursion depth guard | Runaway recursion | Add depth limits or cycle protection logic |
| Using non-indexed join keys | Slow expansion on large trees | Index parent-child key columns |
| No clear ordering in output | Hard-to-read hierarchy | Include depth and ordering columns |
Quick Reference
WITH RECURSIVE t AS (
SELECT ...
UNION ALL
SELECT ... FROM source JOIN t ON ...
)
SELECT * FROM t;