SQL Recursive Queries and CTEs: Tutorial with Examples (2026)

What Are Recursive CTEs?

A recursive CTE is a Common Table Expression that references itself. It lets you traverse hierarchical data, walk graphs, and generate sequences without writing procedural code or stacking self-joins of unknown depth.

Standard CTEs (introduced with the WITH keyword) run once and produce a fixed result set. Recursive CTEs run in a loop: each iteration reads the rows produced by the previous iteration, adds new rows, and stops when no new rows appear. This makes them the go-to tool for any problem where the depth of the data is not known in advance.

Common use cases include:

• Organizational charts (manager-to-report chains)
• Category and subcategory trees
• Bill of materials (parts containing other parts)
• File system or menu path traversal
• Generating date or number sequences
• Finding shortest paths in network graphs

Anatomy of a Recursive CTE

Every recursive CTE has exactly two parts joined by UNION ALL:

1. Anchor member -- the base query that returns the starting rows. It runs once.
2. Recursive member -- the query that references the CTE itself, joining new rows to the previous iteration. It runs repeatedly until it returns zero rows.

WITH RECURSIVE cte_name AS (
    -- Anchor member: starting rows
    SELECT columns
    FROM base_table
    WHERE starting_condition

    UNION ALL

    -- Recursive member: references cte_name
    SELECT columns
    FROM base_table
    JOIN cte_name ON base_table.parent = cte_name.id
)
SELECT * FROM cte_name;

The database executes the anchor first, puts those rows into a working table, then runs the recursive member against that working table. New rows are added to the working table and fed back into the next iteration. When the recursive member returns an empty set, execution stops and the full result is returned.

Key rules:

• The anchor and recursive members must have the same number of columns with compatible types.
• The recursive member must reference the CTE name exactly once in its FROM or JOIN.
• You cannot use GROUP BY, HAVING, DISTINCT, or aggregate functions in the recursive member (in most databases).
• Use UNION ALL (not UNION) unless you specifically need to eliminate duplicates to prevent cycles.

Syntax Across Databases

The recursive CTE syntax is part of the SQL standard, but databases differ on one detail: whether you need the RECURSIVE keyword.

PostgreSQL, MySQL 8+, SQLite, MariaDB

These databases require WITH RECURSIVE:

WITH RECURSIVE hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM employees
    WHERE parent_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.parent_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

SQL Server

SQL Server does not use the RECURSIVE keyword. Just write WITH:

WITH hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM employees
    WHERE parent_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.parent_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy
OPTION (MAXRECURSION 200);

The OPTION (MAXRECURSION n) clause sets the maximum depth. The default in SQL Server is 100. Set it to 0 for unlimited (use with caution).

Oracle

Oracle 11g R2 and later support recursive CTEs with standard syntax (no RECURSIVE keyword). Older Oracle versions use CONNECT BY instead:

-- Oracle recursive CTE (11g R2+)
WITH hierarchy (id, name, parent_id, lvl) AS (
    SELECT id, name, parent_id, 1
    FROM employees
    WHERE parent_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.parent_id, h.lvl + 1
    FROM employees e
    JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

-- Oracle legacy: CONNECT BY
SELECT id, name, LEVEL
FROM employees
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

Practical Examples

1. Organizational Chart

The classic recursive CTE use case. Given an employees table where each row points to its manager via manager_id, retrieve the full reporting chain for any employee.

-- Table structure
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id),
    title VARCHAR(100)
);

-- Get the full reporting tree under the CEO
WITH RECURSIVE org_chart AS (
    -- Anchor: start with the CEO (no manager)
    SELECT id, name, title, manager_id,
           CAST(name AS VARCHAR(500)) AS chain,
           1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: find direct reports of current level
    SELECT e.id, e.name, e.title, e.manager_id,
           CAST(oc.chain || ' > ' || e.name AS VARCHAR(500)),
           oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT depth, name, title, chain
FROM org_chart
ORDER BY chain;

This returns every employee with their depth in the hierarchy and a readable chain like "Alice > Bob > Carol".

2. Category Tree

E-commerce sites often store categories as a self-referencing table. A recursive CTE can flatten the tree or build a full path for breadcrumb navigation.

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT REFERENCES categories(id)
);

-- Build full category paths
WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id,
           CAST(name AS VARCHAR(500)) AS full_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           CAST(ct.full_path || ' / ' || c.name AS VARCHAR(500))
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT id, name, full_path
FROM cat_tree
ORDER BY full_path;

Result: "Electronics / Computers / Laptops / Gaming Laptops" -- useful for breadcrumbs, URL slugs, and admin interfaces.

3. Bill of Materials

Manufacturing systems track which parts are assembled from sub-parts. A recursive CTE can calculate the total quantity of each raw material needed for a finished product.

CREATE TABLE bom (
    parent_part_id INT,
    child_part_id INT,
    quantity INT,
    PRIMARY KEY (parent_part_id, child_part_id)
);

CREATE TABLE parts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    is_raw_material BOOLEAN
);

-- Explode a product into all raw materials
WITH RECURSIVE exploded AS (
    SELECT child_part_id AS part_id,
           quantity AS total_qty,
           1 AS depth
    FROM bom
    WHERE parent_part_id = 1001  -- finished product ID

    UNION ALL

    SELECT b.child_part_id,
           e.total_qty * b.quantity,
           e.depth + 1
    FROM bom b
    JOIN exploded e ON b.parent_part_id = e.part_id
)
SELECT p.name, SUM(e.total_qty) AS total_needed
FROM exploded e
JOIN parts p ON e.part_id = p.id
WHERE p.is_raw_material = true
GROUP BY p.name
ORDER BY total_needed DESC;

4. Path Finding in a Graph

Given a table of connections between nodes (cities, network devices, social graph), find all paths from point A to point B. Cycle detection is critical here.

CREATE TABLE routes (
    from_city VARCHAR(50),
    to_city VARCHAR(50),
    distance INT
);

-- Find all paths from 'New York' to 'Los Angeles'
WITH RECURSIVE paths AS (
    SELECT
        from_city,
        to_city,
        distance AS total_distance,
        CAST(from_city || ' -> ' || to_city AS VARCHAR(1000)) AS path,
        ARRAY[from_city, to_city] AS visited
    FROM routes
    WHERE from_city = 'New York'

    UNION ALL

    SELECT
        p.from_city,
        r.to_city,
        p.total_distance + r.distance,
        CAST(p.path || ' -> ' || r.to_city AS VARCHAR(1000)),
        p.visited || r.to_city
    FROM routes r
    JOIN paths p ON r.from_city = p.to_city
    WHERE r.to_city != ALL(p.visited)  -- cycle prevention
)
SELECT path, total_distance
FROM paths
WHERE to_city = 'Los Angeles'
ORDER BY total_distance;

The visited array tracks nodes already in the current path, preventing infinite loops. In SQL Server, use a comma-separated string and CHARINDEX instead of arrays.

5. Sequence Generation

Recursive CTEs can generate number sequences without needing a numbers table or a generate_series function.

-- Generate numbers 1 to 100
WITH RECURSIVE numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 100
)
SELECT n FROM numbers;

This works in every database that supports recursive CTEs. It is useful for filling gaps in data, generating test rows, or creating lookup values.

6. Date Series Generation

One of the most practical applications: generating a continuous series of dates to LEFT JOIN against, ensuring your reports have rows for every day even when no data exists.

-- Generate every date in Q1 2026
WITH RECURSIVE date_series AS (
    SELECT CAST('2026-01-01' AS DATE) AS dt

    UNION ALL

    SELECT dt + INTERVAL '1 day'
    FROM date_series
    WHERE dt < '2026-03-31'
)
SELECT dt FROM date_series;

-- Fill gaps in daily revenue report
WITH RECURSIVE date_series AS (
    SELECT CAST('2026-01-01' AS DATE) AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM date_series
    WHERE dt < '2026-03-31'
)
SELECT
    ds.dt AS report_date,
    COALESCE(SUM(o.total_amount), 0) AS revenue,
    COALESCE(COUNT(o.id), 0) AS order_count
FROM date_series ds
LEFT JOIN orders o ON CAST(o.order_date AS DATE) = ds.dt
GROUP BY ds.dt
ORDER BY ds.dt;

In MySQL, use DATE_ADD(dt, INTERVAL 1 DAY) instead of the + operator. In SQL Server, use DATEADD(day, 1, dt).

Performance Tips

Recursive CTEs are powerful but can become expensive on large datasets. Follow these guidelines to keep them fast.

Index the join columns

The recursive member joins the base table to the CTE on every iteration. Without an index on the join column (usually parent_id or id), each iteration performs a full table scan.

-- Essential index for hierarchical queries
CREATE INDEX idx_employees_manager ON employees(manager_id);

-- For BOM-style queries
CREATE INDEX idx_bom_parent ON bom(parent_part_id);

Limit recursion depth

Always add a depth counter and cap it. Even with clean data, a missing NULL parent can create an accidental cycle.

WITH RECURSIVE tree AS (
    SELECT id, parent_id, 1 AS depth
    FROM nodes WHERE parent_id IS NULL

    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 < 50  -- safety cap
)
SELECT * FROM tree;

Filter early in the anchor

If you only need the subtree under a specific node, start the anchor there instead of at the root. This reduces the number of iterations and rows processed.

-- Only the subtree under department 42
WITH RECURSIVE subtree AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 42  -- narrow anchor

    UNION ALL

    SELECT d.id, d.name, d.parent_id
    FROM departments d
    JOIN subtree s ON d.parent_id = s.id
)
SELECT * FROM subtree;

Avoid expensive operations inside the recursive member

Do not use ORDER BY, subqueries, or window functions inside the recursive part. Perform sorting, aggregation, and formatting in the outer SELECT that reads from the CTE.

Consider alternatives for very large trees

If your hierarchy has millions of nodes and you query it frequently, consider denormalized models:

• Materialized path: Store the full path as a string column (e.g., "/1/5/12/"). Queries use LIKE '/1/5/%' for subtrees.
• Nested sets: Store left and right boundary numbers. Subtree queries become range scans.
• Closure table: Store every ancestor-descendant pair in a separate table. Fast reads, more complex writes.

These trade write complexity for read speed. Recursive CTEs remain the best choice when the hierarchy changes frequently or when the dataset is moderate in size.

Preventing Infinite Loops

Circular references in your data will cause a recursive CTE to run forever (or until the database hits its recursion limit). Here are three strategies to prevent this.

Strategy 1: Depth limit

Add a counter column and stop at a reasonable maximum.

WHERE t.depth < 20  -- inside the recursive member

Strategy 2: Path tracking

Accumulate visited node IDs in a string or array and check before recursing.

-- PostgreSQL: array approach
WHERE n.id != ALL(t.visited_ids)

-- SQL Server / MySQL: string approach
WHERE CHARINDEX('/' + CAST(n.id AS VARCHAR) + '/',
                t.path_string) = 0

Strategy 3: Database-level limits

-- SQL Server: append to the outer query
OPTION (MAXRECURSION 100);

-- MySQL 8+: session variable
SET SESSION cte_max_recursion_depth = 1000;

-- PostgreSQL: no built-in limit, use depth counter

In production, combine depth limits with path tracking for maximum safety. A depth limit catches runaway queries early, and path tracking prevents legitimate cycles in graph data from causing duplicates.

Try building recursive queries instantly -- describe your hierarchy in plain English and AI2SQL generates the recursive CTE for your database dialect.

Frequently Asked Questions

What is a recursive CTE in SQL?

A recursive CTE (Common Table Expression) is a query that references itself to process hierarchical or graph-structured data. It consists of two parts: an anchor member that returns the base rows, and a recursive member that joins back to the CTE to traverse relationships. The database executes the recursive member repeatedly until no new rows are produced.

Which databases support recursive CTEs?

All major databases support recursive CTEs: PostgreSQL, MySQL (8.0+), SQL Server, Oracle (11g R2+), SQLite (3.8.3+), and MariaDB (10.2+). The syntax is nearly identical across databases, with minor differences in recursion depth limits and the optional RECURSIVE keyword.

How do I prevent infinite loops in recursive queries?

Use three strategies: (1) Set a maximum recursion depth with a level counter and a WHERE clause like WHERE level < 20. (2) Use database-specific limits such as OPTION (MAXRECURSION 100) in SQL Server or SET cte_max_recursion_depth in MySQL. (3) Track visited nodes by accumulating IDs in a path string or array and checking for cycles before recursing.

When should I use a recursive CTE instead of a self-join?

Use a recursive CTE when the depth of the hierarchy is unknown or variable, such as org charts with arbitrary nesting or category trees with unlimited subcategories. Self-joins work when the depth is fixed and small (e.g., always exactly 3 levels). Recursive CTEs handle any depth automatically without changing the query.

Are recursive CTEs slow? How do I optimize them?

Recursive CTEs can be slow on large datasets if not optimized. Key optimizations: (1) Index the columns used in the recursive join (e.g., parent_id, id). (2) Limit recursion depth with a level counter. (3) Filter early in the anchor member to reduce the starting set. (4) Avoid expensive operations like sorting or aggregation inside the recursive member. For very deep hierarchies, consider materialized path or nested set models as alternatives.