SQL 65 🛢️ Recursive CTEs for Hierarchical Data
Hierarchical data is data that references itself. An employee has a manager, who is also an employee. A category has a parent category, which is also a category. A part is made of components, which are themselves parts. A comment has a parent comment, which is also a comment. The relationship is recursive: the same table is joined to itself, and the join can be applied repeatedly until the hierarchy is exhausted. Querying this data with a single JOIN returns one level. Querying it with two joins returns two levels. There is no fixed number of joins that works for all hierarchies, because the depth is a property of the data, not the schema. The recursive CTE solves this by expressing the traversal as a loop, and the database iterates until no new rows are produced.
The recursive CTE was introduced in SQL:1999, and it is the standard way to query hierarchical and graph data in SQL. The construct has two parts. The anchor member is the base case: the roots of the hierarchy, the rows that have no parent. The recursive member is the step: it joins the table to the CTE, finding the children of the rows already in the result. The two are combined with UNION ALL, and the database evaluates the anchor once, then the recursive member repeatedly on the new rows, until the recursive member returns nothing. The result is the transitive closure of the relationship — every row reachable from the roots .
The recursion is not a loop in the procedural sense. It is a fixed-point computation: the CTE is defined as the union of the anchor and the recursive step, and the database iterates until the definition produces no new rows. This is the same semantics as a recursive function with a base case and a recursive case, but expressed declaratively. The key difference is that SQL’s recursion is set-based: each iteration operates on a set of rows, not a single row. The recursive member joins the entire previous result to the table, which means the traversal is breadth-first by default, and the number of iterations is the depth of the hierarchy .
This chapter covers three areas. First, why recursive CTEs exist — the problem of variable-depth hierarchies and the limitations of self-joins. Second, how recursive CTEs work — the anchor and recursive members, the UNION ALL combination, the termination condition, and the working-table evaluation. Third, the practical patterns — organizational charts, category trees, bills of materials, graph traversal, and the safeguards against cycles and unbounded recursion. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the recursion evaluation.
Key point: A recursive CTE has an anchor member (base case) and a recursive member (step), combined with UNION ALL. The database evaluates the anchor once, then the recursive member repeatedly on the new rows, until no new rows are produced. The result is the transitive closure of the relationship. Cycles cause infinite recursion unless a termination condition or the CYCLE clause is used .
Why recursive CTEs exist
The variable-depth problem. A hierarchy has a depth that is not known in advance. An organizational chart might be three levels deep in one company and ten levels deep in another. A category tree might have two levels or seven. A fixed number of self-joins works only if the maximum depth is known and the query is written for that depth. A recursive CTE works for any depth, because the recursion continues until the data is exhausted. This is the fundamental advantage: the query describes the traversal, not the number of steps .
The self-join problem. A self-join combines a table with itself, using an alias to distinguish the two roles. SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = m.id returns each employee with their direct manager. To get the manager’s manager, a second self-join is needed. To get three levels, a third. The number of joins is fixed in the query, and each join adds a level. For a hierarchy of unknown depth, this approach fails, because the query cannot be written without knowing the depth. The recursive CTE replaces the fixed number of joins with a loop that continues until the data is exhausted .
The transitive-closure problem. Some queries need the transitive closure of a relationship: all ancestors of a node, all descendants of a node, all paths between two nodes. The transitive closure is not expressible with a fixed number of joins, because the path length is variable. The recursive CTE computes the transitive closure directly: the anchor is the starting node, and the recursive member extends the path by one edge at each iteration. This is the standard way to compute reachability in a graph .
The working-table problem. A recursive CTE is evaluated iteratively. The database maintains a working table that holds the rows produced by the previous iteration. The anchor member is evaluated first, and its result is placed in the working table. Then the recursive member is evaluated, with the working table as the input. The rows it produces are placed in a new working table, and the process repeats. When the recursive member produces no new rows, the recursion stops. The final result is the union of all the iterations. This evaluation strategy is what makes the recursion possible without a procedural loop, and it is the same in every database that supports recursive CTEs .
The cycle problem. A hierarchy is a tree if it has no cycles. A graph can have cycles: a node that is its own ancestor, a category that is its own parent, a path that loops back. The recursive CTE does not detect cycles by itself. Without a termination condition, a cycle causes infinite recursion, and the query never returns. The CYCLE clause (SQL:1999, supported in PostgreSQL 14+ and some other databases) detects cycles and stops the recursion. Without it, the query must include a depth limit or a path check to break the cycle .
The trade-off. A recursive CTE is more complex than a simple join. It has two members, a union, and a termination condition. The performance depends on the size of the hierarchy and the number of iterations. A deep hierarchy with many rows per level can produce a large intermediate result, and the recursion can be expensive. The recursive CTE is the right tool for variable-depth hierarchies, but for a fixed-depth hierarchy, a self-join may be simpler and faster. The trade-off is between the generality of recursion and the simplicity of a fixed join.
a. The structure of a recursive CTE
A recursive CTE is defined with WITH RECURSIVE. The body has two members joined by UNION ALL (or UNION to remove duplicates). The first member is the anchor, and the second is the recursive member that references the CTE by name .
WITH RECURSIVE employee_hierarchy AS (
-- Anchor member: the roots
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: the children
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, name;The anchor member selects the roots. In an employee hierarchy, the roots are the rows where manager_id is NULL — the CEO or the top-level managers. The anchor is evaluated once, and its result is the starting set for the recursion .
The recursive member references the CTE by name. It joins the employees table to the CTE, using the join condition e.manager_id = eh.id. This finds the children of the rows already in the CTE. The level column is incremented at each step, which makes the depth of each row explicit in the result .
The UNION ALL combines the anchor and the recursive member. UNION ALL keeps all rows, including duplicates. UNION removes duplicates, which is slower but necessary if the recursive member can produce the same row more than once. In a tree hierarchy, UNION ALL is correct and faster, because each row is reachable by exactly one path. In a graph with multiple paths to the same node, UNION prevents duplicates but does not prevent cycles .
The termination condition is implicit: the recursion stops when the recursive member produces no new rows. In a tree, this happens when the leaves are reached — the rows with no children. In a graph with cycles, the recursion never stops unless a termination condition is added. The WHERE clause in the recursive member can include a depth limit: WHERE eh.level < 10. The CYCLE clause can detect cycles explicitly: CYCLE id SET is_cycle USING path .
b. The evaluation model
The recursive CTE is evaluated in iterations. Each iteration produces a new working table, and the process continues until the working table is empty .
Iteration 1: The anchor member is evaluated, and its result is placed in the working table and in the result set.
Iteration 2: The recursive member is evaluated with the working table as the input. The rows it produces are placed in a new working table and added to the result set.
Iteration 3: The recursive member is evaluated with the new working table. The rows it produces are added.
… The process repeats until the recursive member produces no new rows.
The result set is the union of all iterations. The number of iterations is the depth of the hierarchy. This is a breadth-first traversal: all the rows at level 1 are produced in iteration 1, all the rows at level 2 in iteration 2, and so on. The level column, incremented at each step, makes the depth explicit.
The working table is the key to the evaluation. It holds only the rows produced by the previous iteration, not the entire result. This means the join in the recursive member is between the previous iteration’s rows and the base table, not between the entire result and the base table. The size of the working table determines the cost of each iteration, and the total cost is the sum of the iteration costs. For a balanced tree, the cost is proportional to the number of rows. For a deep, narrow hierarchy, the cost is proportional to the depth times the width .
The result set can be large. A hierarchy with n rows produces n rows in the result, plus the columns added by the recursive member (such as level and path). The result is not deduplicated by default; if the recursive member produces the same row multiple times, all copies are kept. The UNION operator (instead of UNION ALL) removes duplicates, but it is slower and does not prevent cycles .
c. The common patterns
The recursive CTE is used for a small number of recurring patterns. Each pattern has a characteristic anchor, recursive member, and termination condition.
Organizational hierarchy. The anchor is the rows where manager_id IS NULL. The recursive member joins employees to the CTE on manager_id = id. The result is the full org chart with a level for each employee. The pattern can be extended to include a path from the root to each node: path = eh.path || '/' || e.id .
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level, name::text AS path
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1, o.path || ' > ' || e.name
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY path;Category tree. The anchor is the rows where parent_id IS NULL. The recursive member joins categories to the CTE on parent_id = id. The result is the full category tree, and it can be used to find all descendants of a category or all ancestors of a product .
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 1 AS level
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;Bill of materials. The anchor is the top-level assembly. The recursive member joins parts to the CTE on part_id = component_id. The result is the full bill of materials, with a level for each component. The pattern is used in manufacturing to compute the total quantity of each part needed to build a product .
WITH RECURSIVE bom AS (
SELECT part_id, component_id, quantity, 1 AS level
FROM parts WHERE part_id = 'A'
UNION ALL
SELECT p.part_id, p.component_id, p.quantity, b.level + 1
FROM parts p JOIN bom b ON p.part_id = b.component_id
)
SELECT * FROM bom ORDER BY level, part_id;Graph traversal. The anchor is the starting node. The recursive member joins edges to the CTE on source = target. The result is all nodes reachable from the start. The depth limit is essential, because a graph can have cycles. The path column can be used to detect cycles: if the next node is already in the path, the traversal stops.
WITH RECURSIVE paths AS (
SELECT source, target, 1 AS depth, ARRAY[source] AS visited
FROM edges WHERE source = 'A'
UNION ALL
SELECT p.source, e.target, p.depth + 1, p.visited || e.target
FROM paths p
JOIN edges e ON p.target = e.source
WHERE p.depth < 10 AND NOT e.target = ANY(p.visited)
)
SELECT * FROM paths;The visited array tracks the nodes already on the path. The WHERE NOT e.target = ANY(p.visited) clause prevents the traversal from revisiting a node, which breaks cycles. This is the standard way to traverse a graph without the CYCLE clause .
Date and number series. The anchor is the starting value. The recursive member adds one unit at each step, and the WHERE clause stops at the end value. This is the standard way to generate a series without a numbers table .
WITH RECURSIVE dates AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM dates WHERE day < DATE '2024-01-31'
)
SELECT * FROM dates;Complete Example Session
-- ============================================
-- PART 1: THE EMPLOYEE TABLE
-- ============================================
CREATE TABLE employees (
id INT PRIMARY KEY,
name TEXT,
manager_id INT REFERENCES employees(id)
);
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);
-- ============================================
-- PART 2: THE BASIC HIERARCHY
-- ============================================
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT id, name, manager_id, level
FROM org
ORDER BY level, name;
-- Alice (level 1)
-- Bob, Carol (level 2)
-- Dave, Eve, Frank (level 3)
-- ============================================
-- PART 3: THE PATH FROM THE ROOT
-- ============================================
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level, name::text AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1,
o.path || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT id, name, level, path
FROM org
ORDER BY path;
-- Alice
-- Alice > Bob
-- Alice > Bob > Dave
-- Alice > Bob > Eve
-- Alice > Carol
-- Alice > Carol > Frank
-- ============================================
-- PART 4: ALL SUBORDINATES OF A MANAGER
-- ============================================
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 2 -- Bob
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
-- Bob, Dave, Eve
-- ============================================
-- PART 5: ALL ANCESTORS OF AN EMPLOYEE
-- ============================================
WITH RECURSIVE ancestors AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 4 -- Dave
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN ancestors a ON e.id = a.manager_id
)
SELECT * FROM ancestors;
-- Dave, Bob, Alice
-- ============================================
-- PART 6: THE CYCLE PROBLEM
-- ============================================
-- A cycle: Alice's manager is Frank
UPDATE employees SET manager_id = 6 WHERE id = 1;
-- This recursive CTE would never terminate:
-- WITH RECURSIVE org AS (
-- SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
-- UNION ALL
-- SELECT e.id, e.name, e.manager_id FROM employees e JOIN org o ON e.manager_id = o.id
-- )
-- SELECT * FROM org;
-- Fix: add a depth limit
-- WHERE o.level < 10
-- ============================================
-- PART 7: THE CYCLE CLAUSE
-- ============================================
-- PostgreSQL 14+ supports the CYCLE clause:
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
)
CYCLE id SET is_cycle USING path
SELECT id, name, level, is_cycle, path
FROM org;
-- The CYCLE clause detects when id repeats.
-- is_cycle is true for the row that closes the cycle.
-- path contains the sequence of ids.
-- ============================================
-- PART 8: THE DATE SERIES
-- ============================================
WITH RECURSIVE dates AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM dates
WHERE day < DATE '2024-01-31'
)
SELECT day FROM dates;
-- Generates every day in January 2024.
-- ============================================
-- PART 9: THE BILL OF MATERIALS
-- ============================================
CREATE TABLE parts (
part_id TEXT,
component_id TEXT,
quantity INT
);
INSERT INTO parts VALUES
('A', 'B', 2),
('A', 'C', 1),
('B', 'D', 3),
('C', 'D', 1);
WITH RECURSIVE bom AS (
SELECT part_id, component_id, quantity, 1 AS level
FROM parts
WHERE part_id = 'A'
UNION ALL
SELECT p.part_id, p.component_id, p.quantity, b.level + 1
FROM parts p
JOIN bom b ON p.part_id = b.component_id
)
SELECT * FROM bom ORDER BY level, part_id;
-- Level 1: A → B (2), A → C (1)
-- Level 2: B → D (3), C → D (1)
-- ============================================
-- PART 10: THE COMPLETE HIERARCHY QUERY
-- ============================================
-- Reset the cycle
UPDATE employees SET manager_id = NULL WHERE id = 1;
WITH RECURSIVE org AS (
SELECT
id,
name,
manager_id,
1 AS level,
name::text AS path,
ARRAY[id] AS visited
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
o.level + 1,
o.path || ' > ' || e.name,
o.visited || e.id
FROM employees e
JOIN org o ON e.manager_id = o.id
WHERE NOT e.id = ANY(o.visited)
)
SELECT
id,
name,
level,
path,
REPEAT(' ', level - 1) || name AS indented_name
FROM org
ORDER BY path;
-- The visited array prevents cycles.
-- The indented_name column shows the hierarchy visually.
-- The path column shows the full path from the root.The ten parts show the employee table, the basic hierarchy, the path from the root, all subordinates, all ancestors, the cycle problem, the CYCLE clause, the date series, the bill of materials, and the complete hierarchy query.
Quick Reference
Recursive CTE Structure
| Part | Meaning |
|---|---|
WITH RECURSIVE name AS (...) | Declares the recursive CTE |
| Anchor member | Base case; no self-reference |
UNION ALL | Combines anchor and recursive |
| Recursive member | References the CTE by name |
| Termination | When recursive member produces no new rows |
Common Patterns
| Pattern | Anchor | Recursive member |
|---|---|---|
| Org chart | WHERE manager_id IS NULL | Join on manager_id = id |
| Category tree | WHERE parent_id IS NULL | Join on parent_id = id |
| Subordinates | WHERE id = ? | Join on manager_id = id |
| Ancestors | WHERE id = ? | Join on id = manager_id |
| Bill of materials | WHERE part_id = 'A' | Join on part_id = component_id |
| Date series | SELECT DATE '...' | day + INTERVAL '1 day' |
Cycle Safeguards
| Safeguard | Syntax | Effect |
|---|---|---|
| Depth limit | WHERE level < 10 | Stops after 10 iterations |
| Path array | ARRAY[id] + NOT id = ANY(path) | Prevents revisiting nodes |
CYCLE clause | CYCLE id SET is_cycle USING path | Detects cycles (PostgreSQL 14+) |
Result Columns
| Column | Purpose |
|---|---|
level | Depth of the row in the hierarchy |
path | Path from the root to the row |
visited | Array of nodes already traversed |
is_cycle | True if the row closes a cycle |
Best Practices
✅ Do This:
-- Use UNION ALL for tree hierarchies
UNION ALL -- ✅
-- Add a level column to track depth
SELECT ..., 1 AS level FROM ... WHERE manager_id IS NULL -- ✅
-- Add a path column for readability
o.path || ' > ' || e.name -- ✅
-- Use a depth limit or CYCLE clause for graphs
WHERE level < 10 -- ✅
-- Use an array to track visited nodes
ARRAY[id] AS visited, NOT e.id = ANY(o.visited) -- ✅
-- Index the join column
CREATE INDEX ON employees(manager_id); -- ✅❌ Don’t Do This:
-- Don't use UNION ALL on a graph with cycles
-- Infinite recursion -- ❌
-- Don't forget the anchor member
-- The CTE has no base case -- ❌
-- Don't reference the CTE in the anchor
SELECT * FROM org WHERE ... -- org is not defined yet -- ❌
-- Don't use recursion for fixed-depth hierarchies
-- A self-join is simpler -- ❌
-- Don't ignore performance on deep hierarchies
-- Add a depth limit if the full depth is not needed -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Infinite recursion | Cycle in the data | Depth limit or CYCLE clause |
| Anchor returns no rows | Wrong base case condition | Check the WHERE clause |
| Missing rows | Anchor does not include all roots | Check for multiple roots |
| Duplicate rows | UNION ALL with multiple paths | Use UNION or deduplicate |
| Slow query | Deep hierarchy, large working table | Index the join column, limit depth |
| Wrong level values | Level not incremented | o.level + 1 in the recursive member |
| Path not building | Path not concatenated | `o.path |
| Recursive member references wrong column | Join condition wrong | Check manager_id = id |
Real-World Examples
1. Organizational Hierarchy
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.level + 1 FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;2. All Subordinates
WITH RECURSIVE subs AS (
SELECT id, name FROM employees WHERE id = 2
UNION ALL
SELECT e.id, e.name FROM employees e JOIN subs s ON e.manager_id = s.id
)
SELECT * FROM subs;3. All Ancestors
WITH RECURSIVE anc AS (
SELECT id, name, manager_id FROM employees WHERE id = 4
UNION ALL
SELECT e.id, e.name, e.manager_id FROM employees e JOIN anc a ON e.id = a.manager_id
)
SELECT * FROM anc;4. Category Tree
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;5. Bill of Materials
WITH RECURSIVE bom AS (
SELECT part_id, component_id, quantity, 1 AS level FROM parts WHERE part_id = 'A'
UNION ALL
SELECT p.part_id, p.component_id, p.quantity, b.level + 1 FROM parts p JOIN bom b ON p.part_id = b.component_id
)
SELECT * FROM bom;6. Date Series
WITH RECURSIVE dates AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + 1 FROM dates WHERE day < DATE '2024-01-31'
)
SELECT * FROM dates;7. Graph Traversal
WITH RECURSIVE paths AS (
SELECT source, target, 1 AS depth, ARRAY[source] AS visited FROM edges WHERE source = 'A'
UNION ALL
SELECT p.source, e.target, p.depth + 1, p.visited || e.target FROM paths p JOIN edges e ON p.target = e.source WHERE p.depth < 10 AND NOT e.target = ANY(p.visited)
)
SELECT * FROM paths;8. Path from Root
WITH RECURSIVE org AS (
SELECT id, name, 1 AS level, name::text AS path FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, o.level + 1, o.path || ' > ' || e.name FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;9. CYCLE Clause
WITH RECURSIVE org AS (...) CYCLE id SET is_cycle USING path SELECT * FROM org;10. Indented Hierarchy
SELECT REPEAT(' ', level - 1) || name AS indented_name FROM org ORDER BY path;Visual
The Recursive CTE Evaluation
┌──────────────────────────────────────────────────────────────┐
│ RECURSIVE CTE EVALUATION │
│ │
│ Anchor member │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SELECT ... WHERE manager_id IS NULL │ │
│ │ → Alice (level 1) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ Iteration 1: recursive member on Alice │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ → Bob (level 2) │ │
│ │ → Carol (level 2) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ Iteration 2: recursive member on Bob, Carol │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ → Dave (level 3) │ │
│ │ → Eve (level 3) │ │
│ │ → Frank (level 3) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ Iteration 3: recursive member on Dave, Eve, Frank │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ → no rows │ │
│ │ → recursion stops │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Result: union of all iterations │
│ │
└──────────────────────────────────────────────────────────────┘The Working Table
┌──────────────────────────────────────────────────────────────┐
│ THE WORKING TABLE │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Result set (accumulated) │ │
│ │ ┌────────────────────────────────────────────────┐ │ │
│ │ │ Alice (1) │ │ │
│ │ │ Bob (2), Carol (2) │ │ │
│ │ │ Dave (3), Eve (3), Frank (3) │ │ │
│ │ └────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Working table (previous iteration only) │ │
│ │ ┌────────────────────────────────────────────────┐ │ │
│ │ │ Iteration 1: Alice │ │ │
│ │ │ Iteration 2: Bob, Carol │ │ │
│ │ │ Iteration 3: Dave, Eve, Frank │ │ │
│ │ │ Iteration 4: (empty) → stop │ │ │
│ │ └────────────────────────────────────────────────┘ │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The recursive member joins the working table to the base │
│ table. Only the previous iteration's rows are used. │
│ │
└──────────────────────────────────────────────────────────────┘The Cycle Problem
┌──────────────────────────────────────────────────────────────┐
│ THE CYCLE PROBLEM │
│ │
│ Alice ──► Bob ──► Dave │
│ ▲ │ │
│ └──────────────────┘ │
│ │
│ If Dave's manager is Alice, the hierarchy has a cycle. │
│ │
│ Without a safeguard: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Iteration 1: Alice │ │
│ │ Iteration 2: Bob │ │
│ │ Iteration 3: Dave │ │
│ │ Iteration 4: Alice ← repeat │ │
│ │ Iteration 5: Bob ← repeat │ │
│ │ ... │ │
│ │ Infinite recursion │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Safeguards: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Depth limit: WHERE level < 10 │ │
│ │ Path check: NOT id = ANY(visited) │ │
│ │ CYCLE clause: CYCLE id SET is_cycle USING path │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Path Column
┌──────────────────────────────────────────────────────────────┐
│ THE PATH COLUMN │
│ │
│ id name level path │
│ ── ───── ───── ────────────────────────────── │
│ 1 Alice 1 Alice │
│ 2 Bob 2 Alice > Bob │
│ 4 Dave 3 Alice > Bob > Dave │
│ 5 Eve 3 Alice > Bob > Eve │
│ 3 Carol 2 Alice > Carol │
│ 6 Frank 3 Alice > Carol > Frank │
│ │
│ The path column is built by concatenating the parent's │
│ path with the current name: │
│ o.path || ' > ' || e.name │
│ │
│ It shows the full lineage from the root to the row. │
│ ORDER BY path produces a depth-first ordering. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Recursive CTE | WITH RECURSIVE, anchor + recursive members |
| Anchor member | Base case; no self-reference |
| Recursive member | References the CTE by name |
UNION ALL | Combines anchor and recursive |
| Termination | Recursive member produces no new rows |
| Evaluation | Iterative; working table per iteration |
level column | Depth of the row |
path column | Path from the root |
| Cycle safeguard | Depth limit, path check, or CYCLE clause |
| Result | Transitive closure of the relationship |
Key takeaways:
- A recursive CTE has two members. The anchor member is the base case, and the recursive member references the CTE by name. The two are combined with
UNION ALL. The database evaluates the anchor once, then the recursive member repeatedly until no new rows are produced . - The recursion is iterative, not procedural. Each iteration produces a new working table, and the recursive member joins the previous iteration’s rows to the base table. The result is the union of all iterations. The number of iterations is the depth of the hierarchy .
- The anchor defines the roots. For an org chart, the anchor is the rows where
manager_id IS NULL. For a category tree, it is the rows whereparent_id IS NULL. For a bill of materials, it is the top-level assembly. The anchor determines where the traversal starts. - The recursive member extends the traversal by one step. It joins the base table to the CTE on the parent-child relationship. The
levelcolumn is incremented at each step, and thepathcolumn is built by concatenating the parent’s path with the current name . - Cycles cause infinite recursion. A graph with a cycle never terminates without a safeguard. The
CYCLEclause (PostgreSQL 14+) detects cycles explicitly. A depth limit or a path array is the portable alternative . - The path column enables depth-first ordering.
ORDER BY pathproduces a depth-first traversal, which is often the desired presentation for a hierarchy. Thelevelcolumn can be used to indent the output. - The working table is the key to the evaluation. The recursive member joins only the previous iteration’s rows, not the entire result. This is what makes the recursion efficient and what makes the traversal breadth-first by default .
- Recursive CTEs are the standard tool for hierarchical data. They are used for org charts, category trees, bills of materials, graph traversal, and date series. The pattern is the same in every database that supports recursive CTEs, with minor syntax differences.
Remember: A recursive CTE is a query that references itself. The anchor member is the base case, and the recursive member is the step. The database iterates until no new rows are produced, and the result is the transitive closure of the relationship. The level column tracks depth, the path column tracks lineage, and the CYCLE clause or a depth limit prevents infinite recursion. Recursive CTEs are the standard way to query hierarchical and graph data in SQL, and they turn a variable-depth traversal into a single declarative query.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!