Recursive CTE

Advanced

Subqueries & CTEs

A recursive CTE has an anchor query that seeds the recursion and a recursive member that repeatedly joins the CTE to itself until no new rows are produced.

Overview

Standard SQL does not have loops, but recursive CTEs provide iteration by allowing a CTE to reference itself. The anchor member runs once and seeds the result set. The recursive member joins the previous iteration's results against some table, producing new rows. The engine repeats the recursive step using UNION ALL (which retains duplicates) or UNION (which deduplicates) until the recursive member returns no rows. Common uses include traversing organisation hierarchies, computing Fibonacci numbers, unfolding category trees, and generating date series. Without a termination condition (or a depth limit) a recursive CTE can loop infinitely. PostgreSQL supports CYCLE detection and depth tracking using the SEARCH and CYCLE clauses.

Employee Org Chart — Finding All Reports Under a Manager

The anchor selects the root manager; the recursive member joins employees to the current level via manager_id to walk down the hierarchy.

SQL — recursive CTE org chart, depth tracking

-- employees(id, name, department_id, manager_id, salary, hire_date)
-- Find all employees who report (directly or indirectly) to manager id = 5

WITH RECURSIVE org_tree AS (
    -- Anchor: the manager themselves
    SELECT
        id,
        name,
        manager_id,
        1 AS depth           -- track hierarchy level
    FROM employees
    WHERE id = 5

    UNION ALL

    -- Recursive member: employees whose manager is in the current result
    SELECT
        e.id,
        e.name,
        e.manager_id,
        ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON ot.id = e.manager_id   -- join CTE to itself
)

SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

-- Termination: when no employees have manager_id matching any current org_tree.id,
-- the recursive member returns zero rows and recursion stops.

Product Category Tree and Cycle Prevention

Recursive CTEs on user-supplied data (e.g., category parent_id) risk infinite loops if the data contains cycles. Use a path array or PostgreSQL CYCLE clause to detect and break cycles.

SQL — category tree, cycle prevention with path array and CYCLE clause

-- Flatten a product category tree: categories(id, name, parent_id)
WITH RECURSIVE category_tree AS (
    -- Anchor: root categories (no parent)
    SELECT id, name, parent_id,
           ARRAY[id] AS path,   -- track visited ids for cycle detection
           name::TEXT AS full_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           ct.path || c.id,
           ct.full_path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
    WHERE c.id <> ALL(ct.path)  -- cycle guard: skip if id already visited
)
SELECT id, name, full_path
FROM category_tree
ORDER BY full_path;

-- PostgreSQL 14+ native CYCLE clause (cleaner):
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
CYCLE id SET is_cycle USING path
SELECT * FROM category_tree WHERE NOT is_cycle;

Key Points to Remember

1A recursive CTE has two parts separated by UNION ALL: the anchor (runs once) and the recursive member (iterates).
2Use UNION ALL (not UNION) for recursion unless deduplication is required — UNION adds an expensive sort/hash step per iteration.
3Always include a termination condition: either a WHERE clause that will eventually return no rows, or a depth limit.
4Track visited IDs in an array (or use the CYCLE clause in PostgreSQL 14+) to prevent infinite loops on cyclic data.
5Common use cases: org charts, category trees, bill-of-materials, date series generation, graph traversal.
6Add a maximum depth guard (AND depth < 100) as a safety net against unexpected data cycles.

Interview Questions

Explain the structure of a recursive CTE. What are the anchor member and the recursive member?

MediumAmazon

Write a recursive CTE to find all employees who report directly or indirectly to a given manager.

HardGoogle

How do you prevent infinite recursion in a recursive CTE when the data may contain cycles?

HardMicrosoft

What is the difference between UNION and UNION ALL in a recursive CTE? Which should you use and why?

MediumUber

Ask Aria about Recursive CTE

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

CTE (WITH Clause)

Subquery vs JOIN vs CTE — When to Use Which