SQL Recursive CTE Queries for Hierarchies

TL;DR

• Bottom line: Use WITH RECURSIVE to traverse adjacency-list hierarchies (org charts, BOM, categories) by defining an anchor query for root nodes and a recursive member that joins children to already-found rows, terminating when no new rows are produced.
• Key tool/command: WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ... FROM cte JOIN ...) SELECT * FROM cte
• Watch out for: Infinite recursion in cyclic data -- always add a depth counter (WHERE depth < N) or cycle detection.
• Works with: PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2005+, SQLite 3.8.3+, Oracle 11g R2+, BigQuery.

Constraints

• MySQL requires the RECURSIVE keyword in WITH RECURSIVE; omitting it causes "Table doesn't exist" errors. PostgreSQL and SQL Server accept WITH alone but RECURSIVE is best practice for portability. [src2]
• SQL Server defaults to MAXRECURSION 100 -- queries exceeding 100 levels error out. Override with OPTION (MAXRECURSION 0) for unlimited (dangerous) or a specific limit. [src3]
• MySQL defaults cte_max_recursion_depth to 1000. Increase per session with SET SESSION cte_max_recursion_depth = N. [src2]
• Recursive members in MySQL must not use aggregate functions, GROUP BY, window functions, DISTINCT, ORDER BY, or subqueries referencing the CTE. PostgreSQL is more permissive. [src2]
• In MySQL, the CTE must not appear on the right side of a LEFT JOIN in the recursive member. [src2]
• Always test with LIMIT or a depth cap before running on production data to avoid runaway queries.

Quick Reference

Decision Tree

START
|-- Which storage model is already in place?
|   |-- ADJACENCY LIST (parent_id column) --> Use recursive CTE (this unit)
|   |-- NESTED SET (lft/rgt columns) --> Use range queries, no recursion needed
|   |-- MATERIALIZED PATH (/1/3/7/) --> Use LIKE or ltree, no recursion needed
|   |-- CLOSURE TABLE --> Use direct joins, no recursion needed
|   +-- NO TABLE YET --> continue below
|
|-- Read/write ratio?
|   |-- READ-HEAVY (>90% reads, rare moves)
|   |   |-- Depth < 10 levels? --> Materialized path + index
|   |   +-- Depth >= 10 levels? --> Nested set or closure table
|   |-- WRITE-HEAVY (frequent inserts/moves)
|   |   +-- Adjacency list + recursive CTE (this unit)
|   +-- BALANCED
|       +-- Closure table (best flexibility)
|
|-- Database engine?
|   |-- PostgreSQL 14+ --> Use SEARCH/CYCLE clauses
|   |-- PostgreSQL <14 --> Manual path array + is_cycle boolean
|   |-- MySQL 8.0+ --> WITH RECURSIVE; aggregate outside CTE
|   |-- SQL Server --> WITH (no RECURSIVE keyword); OPTION(MAXRECURSION N)
|   +-- SQLite 3.8.3+ --> WITH RECURSIVE; no built-in cycle detection
|
+-- DEFAULT --> Adjacency list + recursive CTE

Step-by-Step Guide

1. Create the adjacency-list table

Design the table with a self-referencing foreign key. Root nodes have NULL in the parent column. [src1]

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    title      VARCHAR(100),
    manager_id INT REFERENCES employees(id),
    INDEX idx_manager (manager_id)
);

Verify: SELECT * FROM employees WHERE manager_id IS NULL; -- returns root node(s)

2. Write the anchor member

The anchor selects starting point(s) -- typically root nodes or a specific subtree root. [src1]

SELECT id, name, title, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL

Verify: Returns exactly the expected root node(s)

3. Write the recursive member

Join the base table to the CTE to find children of already-discovered nodes. Always increment the depth counter. [src1] [src3]

SELECT e.id, e.name, e.title, e.manager_id, cte.depth + 1
FROM employees e
INNER JOIN org_tree cte ON e.manager_id = cte.id
WHERE cte.depth < 20

Verify: Each iteration returns one level deeper; eventually returns 0 rows

4. Combine into a complete recursive CTE

Wrap anchor and recursive member with WITH RECURSIVE and UNION ALL. [src1]

WITH RECURSIVE org_tree AS (
    SELECT id, name, title, manager_id, 0 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.title, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 20
)
SELECT * FROM org_tree ORDER BY depth, name;

Verify: SELECT COUNT(*) FROM org_tree; -- matches total employee count

5. Add path tracking for breadcrumbs

Build a full path string. Syntax varies by engine. [src1]

WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 0 AS depth,
           ARRAY[name] AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, ot.depth + 1,
           ot.path || e.name
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT *, array_to_string(path, ' > ') AS breadcrumb
FROM org_tree ORDER BY path;

Verify: Breadcrumb shows CEO > VP Sales > Regional Manager > Rep

6. Add cycle detection for graphs

Use PostgreSQL 14+ CYCLE clause or manual path-based detection. [src1]

WITH RECURSIVE graph_walk AS (
    SELECT id, parent_id, name, 0 AS depth
    FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.parent_id, n.name, gw.depth + 1
    FROM nodes n
    INNER JOIN graph_walk gw ON n.parent_id = gw.id
) CYCLE id SET is_cycle USING path
SELECT * FROM graph_walk WHERE NOT is_cycle;

Verify: SELECT COUNT(*) FROM graph_walk WHERE is_cycle; -- shows detected cycles

Code Examples

PostgreSQL: Org Chart with Depth-First Ordering

-- Input:  employees table with (id, name, title, manager_id)
-- Output: full org tree with depth, path, and visual indentation
WITH RECURSIVE org_tree AS (
    SELECT id, name, title, manager_id, 0 AS depth,
           ARRAY[id] AS path, name::text AS breadcrumb
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.title, e.manager_id, ot.depth + 1,
           ot.path || e.id, ot.breadcrumb || ' > ' || e.name
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 50
)
SELECT id, repeat('  ', depth) || name AS indented_name,
       title, depth, breadcrumb
FROM org_tree ORDER BY path;

MySQL 8.0+: Bill of Materials with Quantity Rollup

-- Input:  bom table with (assembly_id, component_id, quantity)
-- Output: all components for assembly 1 with total quantities
WITH RECURSIVE bom_tree AS (
    SELECT component_id, assembly_id, quantity, 1 AS depth,
           CAST(CONCAT('/', assembly_id, '/', component_id)
                AS CHAR(1000)) AS path
    FROM bom WHERE assembly_id = 1
    UNION ALL
    SELECT b.component_id, b.assembly_id,
           b.quantity * bt.quantity, bt.depth + 1,
           CONCAT(bt.path, '/', b.component_id)
    FROM bom b
    INNER JOIN bom_tree bt ON b.assembly_id = bt.component_id
    WHERE bt.depth < 20
)
SELECT component_id, SUM(quantity) AS total_needed,
       MAX(depth) AS max_depth
FROM bom_tree GROUP BY component_id
ORDER BY total_needed DESC;

SQL Server: Category Tree with MAXRECURSION

-- Input:  categories table with (id, name, parent_id)
-- Output: full category tree with level and path
WITH category_tree AS (
    SELECT id, name, parent_id, 0 AS level,
           CAST(name AS NVARCHAR(MAX)) AS full_path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1,
           CAST(ct.full_path + N' > ' + c.name AS NVARCHAR(MAX))
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, level, full_path
FROM category_tree
ORDER BY full_path
OPTION (MAXRECURSION 200);

Anti-Patterns

Wrong: No depth limit on recursive CTE

-- BAD -- infinite recursion if data has cycles
WITH RECURSIVE tree AS (
    SELECT id, parent_id FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.parent_id
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

Correct: Always include a depth safety cap

-- GOOD -- depth limit prevents runaway recursion
WITH RECURSIVE tree AS (
    SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.parent_id, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 100
)
SELECT * FROM tree;

Wrong: Aggregate inside MySQL recursive member

-- BAD -- MySQL error: aggregate in recursive member
WITH RECURSIVE rollup AS (
    SELECT id, parent_id, cost FROM items WHERE id = 1
    UNION ALL
    SELECT i.id, i.parent_id, SUM(i.cost)
    FROM items i JOIN rollup r ON i.parent_id = r.id
    GROUP BY i.id
)
SELECT * FROM rollup;

Correct: Aggregate in the outer query

-- GOOD -- aggregate after recursion completes
WITH RECURSIVE rollup AS (
    SELECT id, parent_id, cost, 0 AS depth
    FROM items WHERE id = 1
    UNION ALL
    SELECT i.id, i.parent_id, i.cost, r.depth + 1
    FROM items i JOIN rollup r ON i.parent_id = r.id
    WHERE r.depth < 50
)
SELECT parent_id, SUM(cost) AS total_cost
FROM rollup GROUP BY parent_id;

Wrong: UNION instead of UNION ALL (unnecessary dedup)

-- BAD -- UNION forces expensive sort+dedup every iteration
WITH RECURSIVE tree AS (
    SELECT id, parent_id FROM nodes WHERE id = 1
    UNION
    SELECT n.id, n.parent_id
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

Correct: UNION ALL for trees with unique keys

-- GOOD -- UNION ALL avoids unnecessary sort
WITH RECURSIVE tree AS (
    SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.parent_id, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 100
)
SELECT * FROM tree;

Wrong: Projecting parent column instead of child

-- BAD -- projects t.id (parent) instead of n.id (child) = infinite loop
WITH RECURSIVE tree AS (
    SELECT id FROM nodes WHERE id = 1
    UNION ALL
    SELECT t.id
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

Correct: Always project the child row's columns

-- GOOD -- projects n.id (the discovered child node)
WITH RECURSIVE tree AS (
    SELECT id, 0 AS depth FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, t.depth + 1
    FROM nodes n JOIN tree t ON n.parent_id = t.id
    WHERE t.depth < 100
)
SELECT * FROM tree;

Common Pitfalls

• Infinite recursion from cyclic data: A parent_id pointing back to an ancestor creates an endless loop. Fix: Add WHERE depth < N and use PostgreSQL's CYCLE clause or manual path-array detection. [src1]
• MySQL "Table doesn't exist" error: Forgetting the RECURSIVE keyword. Fix: Always write WITH RECURSIVE cte_name AS (...). [src2]
• SQL Server MAXRECURSION 100 error: Default limit terminates at 100 levels. Fix: OPTION (MAXRECURSION N) where N is your expected max depth. [src3]
• Column type truncation in MySQL: MySQL infers CTE column types from the anchor only. Fix: CAST(column AS CHAR(1000)) in the anchor member. [src2]
• Wrong join direction: Joining CTE.id = table.id instead of CTE.id = table.parent_id traverses the wrong direction. Fix: Double-check FK vs PK columns. [src3]
• Missing index on parent_id: Each recursive iteration does a full table scan without it. Fix: CREATE INDEX idx_parent ON table(parent_id); [src1]
• Multiplied rows in BOM queries: Components appearing in multiple assemblies duplicate correctly, but misunderstood output leads to wrong totals. Fix: Aggregate in outer query with GROUP BY component_id. [src3]
• UNION vs UNION ALL performance: UNION forces sort/dedup every iteration. Fix: Use UNION ALL for trees; reserve UNION only for graphs needing dedup. [src1]

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• PostgreSQL's SEARCH and CYCLE clauses (v14+) are syntactic sugar expanded to manual path/array tracking. They are not faster, but less error-prone and more readable.
• MySQL determines CTE column types solely from the anchor member. Recursive string concatenation silently truncates unless you CAST to a large type in the anchor.
• SQL Server's OPTION (MAXRECURSION 0) removes all limits. Always combine with WHERE depth < N as a secondary safety net.
• Recursive CTEs are evaluated iteratively, not truly recursively. Performance scales linearly with tree size but degrades on very wide trees due to joins at each level.
• No major SQL engine parallelizes recursive member iterations. Each level depends on the previous, making this inherently sequential per level.