CTEs & Recursive Queries

Master Common Table Expressions (CTEs) and recursive queries to write readable, self-referencing SQL that handles hierarchical data efficiently.

What it is

A Common Table Expression (CTE) is a temporary named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It improves readability by breaking complex queries into simpler parts. A recursive CTE extends this concept by referencing itself, allowing you to traverse hierarchical structures like organizational charts, file systems, or bill-of-materials lists. The core mental model is "anchor and step": an anchor member establishes the starting point, while the recursive member repeatedly joins back to the CTE until no new rows are found.

Why it matters

• Readability: Replaces nested subqueries with linear, logical steps.
• Hierarchy Handling: Essential for querying parent-child relationships without multiple self-joins.
• Maintainability: Changes to logic occur in one place rather than scattered across nested blocks.
• Performance: Often optimized better by query planners than equivalent correlated subqueries.

Syntax or steps

The basic syntax uses the WITH keyword. For recursion, the structure requires two parts separated by UNION ALL:

1. Anchor Member: Selects the base rows (e.g., top-level managers).
2. Recursive Member: Joins the table to the CTE name to find children of previously selected rows.
3. Termination Condition: Implicitly stops when the recursive member returns zero rows.

Example

This example retrieves all employees reporting directly or indirectly to a specific manager using PostgreSQL/Standard SQL syntax.

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: Start with the CEO (ID 1)
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE id = 1

    UNION ALL

    -- Recursive: Find direct reports of current hierarchy members
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Part-by-part explanation:

• WITH RECURSIVE employee_hierarchy AS (...): Defines the CTE named employee_hierarchy.
• Anchor Block: Selects the root node (id = 1) and initializes the depth counter (level = 1).
• UNION ALL: Combines results from the anchor and subsequent recursive steps. Use UNION ALL instead of UNION for performance unless duplicate removal is strictly required.
• Recursive Block: Joins the employees table to the employee_hierarchy CTE. It finds employees whose manager_id matches an id already in the CTE, incrementing the level.

Common mistakes

• Infinite Loops: Occurs if circular references exist in data (A manages B, B manages A). Fix by adding a path tracking column or limiting depth.
• Using UNION instead of UNION ALL: UNION forces distinct sorting on every iteration, drastically slowing down large datasets. Use UNION ALL unless duplicates are expected and must be removed.
• Missing Column Alignment: The anchor and recursive members must have the exact same number of columns and compatible data types.
• Forgetting the Termination Logic: Ensure the join condition naturally excludes processed rows; otherwise, the query may run indefinitely.

When to use it

Practice

Guided Exercise: Modify the example above to only return employees at level <= 3. Add a WHERE clause in the final SELECT statement.

Challenge: Write a recursive CTE to generate a sequence of numbers from 1 to 10. Hint: Start with SELECT 1 as the anchor and add 1 in the recursive part.

Quick check

Q: Why is UNION ALL generally preferred over UNION in recursive CTEs?

A: UNION ALL avoids the expensive overhead of sorting and removing duplicates during each recursive iteration, which is usually unnecessary because the join logic prevents revisiting nodes in acyclic graphs.

Summary

CTEs provide a modular approach to SQL, while recursive CTEs unlock the ability to process hierarchical data elegantly. By separating the anchor and recursive steps, you create queries that are both powerful and maintainable, avoiding the complexity of deep nesting or multiple self-joins.