Hierarchical Data and Recursive CTEs

Standard SQL struggles with hierarchical data like org charts, multi-level product categories, or comment threads. Recursive Common Table Expressions (CTEs) solve this by allowing a query to reference itself, iteratively exploring the hierarchy. They are a structured and powerful method that replaces messy, iterative application code with a declarative, set-based solution.

The Problem: Nested Self-Joins

Imagine you have an employees table with manager_id referencing employee_id. Without recursive CTEs, finding all subordinates of a manager requires nested self-joins, which becomes impossible if the depth is unknown.

-- ❌ Only works for fixed depth
SELECT 
    e1.id, e1.name,
    e2.id, e2.name,
    e3.id, e3.name
FROM employees e1
LEFT JOIN employees e2 ON e1.id = e2.manager_id
LEFT JOIN employees e3 ON e2.id = e3.manager_id
WHERE e1.id = 'emp_root';

This fails when the hierarchy goes deeper than 3 levels. Recursive CTEs elegantly solve this problem.

The Solution: Recursive CTEs

A recursive CTE consists of three key parts:

1. The Anchor: A SELECT that starts the chain (e.g., the root manager)
2. The Recursive Arm: A SELECT that references the CTE itself, joining it to the next level of the hierarchy
3. The Termination: The recursion stops when the recursive part returns no more rows, preventing an infinite loop

-- ✅ Clean approach with recursive CTE
WITH RECURSIVE subordinates AS (
    -- Anchor: The starting point
    SELECT 
        id, 
        name, 
        manager_id, 
        0 AS depth
    FROM employees
    WHERE id = 'emp_root'
    
    UNION ALL
    
    -- Recursive Arm: Find the next level
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates
ORDER BY depth;

Real-World Scenarios

1. Organizational Charts

Visualize a complete reporting structure for your organization.

WITH RECURSIVE org_chart AS (
    SELECT 
        id, 
        name, 
        manager_id,
        0 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL  -- CEO has no manager
    
    UNION ALL
    
    SELECT 
        e.id, 
        e.name, 
        e.manager_id,
        oc.level + 1,
        oc.path || ' → ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    REPEAT('  ', level) || name AS org_structure,
    level,
    path
FROM org_chart
ORDER BY path;

2. Bill of Materials (BOM)

Calculate the total quantity of components needed for a product assembly.

WITH RECURSIVE bom AS (
    SELECT 
        product_id,
        component_id,
        quantity,
        1 AS level
    FROM product_components
    WHERE product_id = 'finished_good_001'
    
    UNION ALL
    
    SELECT 
        pc.product_id,
        pc.component_id,
        pc.quantity * bom.quantity AS quantity,
        bom.level + 1
    FROM product_components pc
    JOIN bom ON pc.product_id = bom.component_id
)
SELECT 
    component_id,
    SUM(quantity) AS total_quantity_required
FROM bom
GROUP BY component_id
ORDER BY level DESC;

3. Category Trees

Get all subcategories under a given product category.

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 'electronics'
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Avoiding Infinite Loops

When working with recursive CTEs, it's possible to create infinite loops if circular references exist in your data.

-- ✅ Safety measure: Limit recursion depth
WITH RECURSIVE subordinates AS (
    -- Anchor (same as before)
    UNION ALL
    -- Recursive Arm (same as before)
)
SELECT * FROM subordinates
WHERE depth <= 10  -- Limit depth to avoid infinite loops
OPTION (MAXRECURSION 10); -- SQL Server specific safety

Performance Tips

Conclusion