Recursive CTEs: Looping in SQL

SQL is generally set-based, not procedural. You don’t usually write “loops” or “recursion” in standard queries. Recursive Common Table Expressions (CTEs) are the exception. They allow you to reference a query within itself, making it possible to traverse trees, graphs, and hierarchies.

[!TIP] Use Cases:

Organizational Charts (Who reports to whom?)

Comment Threads (Reddit-style nested comments)

Graph Traversal (Shortest path, social networks)

Generating Series (Dates, numbers)

1. Recursive CTEs: Looping in SQL

2. Anatomy of a Recursive Query

A Recursive CTE has three parts:

Anchor Member: The starting point (Initial result set).
Recursive Member: The query that references the CTE name.
Union All: Combines the anchor and recursive steps.

WITH RECURSIVE subordinates AS (
    -- 1. Anchor Member: Start with the top manager
    SELECT
        employee_id,
        manager_id,
        full_name,
        0 as depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 2. Recursive Member: Find people who report to the previous level
    SELECT
        e.employee_id,
        e.manager_id,
        e.full_name,
        s.depth + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
-- 3. Final Select
SELECT * FROM subordinates;

3. Recursive CTEs: Looping in SQL

4. How it Executes

The database executes this iteratively:

Run Anchor: Find the CEO. Put them in the subordinates result table (Working Table).
Run Recursive: Join employees with the current content of the Working Table.
- Find direct reports of the CEO. Add them to result.
- Replace Working Table with only these new rows.
Repeat: Find reports of the reports.
Stop: When the Recursive query returns 0 rows (no more subordinates).

5. Recursive CTEs: Looping in SQL

6. Protecting Against Infinite Loops

If your data has a cycle (A manages B, B manages A), a recursive query will run forever. You can prevent this by tracking the path or setting a limit.

WITH RECURSIVE path_trace AS (
    SELECT id, ARRAY[id] as path FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, p.path || n.id
    FROM nodes n
    JOIN path_trace p ON n.parent_id = p.id
    WHERE NOT n.id = ANY(p.path) -- Stop if we've seen this node
)
SELECT * FROM path_trace;

7. Recursive CTEs: Looping in SQL

8. Interactive: Org Chart Builder

Watch how the recursion unfolds step-by-step.

Recursion Visualization

Waiting to start...

Source Table (Employees)

Name

Manager

Alice (CEO)

NULL

Bob

Alice

Charlie

Alice

Dave

Bob

Eve

Bob

Frank

Charlie

Grace

Frank

9. Recursive CTEs: Looping in SQL

[!NOTE] This module explores the core principles of Recursive CTEs, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

10. Summary

WITH RECURSIVE enables looping in SQL.
Anchor Member: Starts the loop.
Recursive Member: Continues the loop using previous results.
Termination: Happens automatically when the recursive member returns no rows.
Performance: Be careful with large datasets; recursion can be slow. Ensure you have an index on the join column (e.g., manager_id).