SQL Recursive CTE Guide

SQL recursive CTEs are one of the most useful advanced SQL tools because they let a query repeatedly build on its own previous output.

That matters when the data is not flat.

A lot of real business data is hierarchical or parent-child in shape, such as:

• employee org charts
• folder trees
• category hierarchies
• comments with replies
• bills of materials
• referral chains
• menu structures
• account relationships
• project task trees
• dependency graphs

A normal join can handle:

• one parent
• one child
• maybe one extra level

But when the number of levels is unknown, fixed joins stop being practical very quickly.

That is where recursive CTEs become powerful.

They let SQL say:

• start here
• find the next related rows
• then keep repeating that process until there are no more rows to find

This guide explains SQL recursive CTEs clearly, including:

• what they are
• how they work
• the anchor and recursive parts
• common hierarchy patterns
• path building
• depth tracking
• sequence generation
• cycle prevention
• and practical performance habits

Why recursive CTEs matter

A lot of SQL queries work on flat data:

• one row per user
• one row per order
• one row per invoice

Those are easy to filter, join, group, and sort.

But hierarchical data is different.

Suppose you have an employees table like this:

employee_id	employee_name	manager_id
1	CEO	NULL
2	Alice	1
3	Bob	1
4	Cara	2
5	Dan	2
6	Eva	4

If you want:

• direct reports of the CEO

a normal join is enough.

But if you want:

• all descendants of the CEO at every level

that is no longer a simple one-join problem.

You do not know in advance how many levels deep the tree goes.

That is exactly the kind of problem recursive CTEs solve.

The most important rule

Before anything else, remember this:

A recursive CTE starts with a base result, then repeatedly finds the next matching level until it naturally stops or hits a defined stopping condition.

That is the single most important idea in this topic.

If you understand that clearly, recursive CTEs become much less intimidating.

A useful way to think about them is:

• first, find the starting rows
• then, use those rows to find the next rows
• then, use those next rows to find more rows
• and keep going until nothing else matches

That is recursion in SQL form.

What a recursive CTE is

A recursive CTE is a common table expression that references itself.

A standard CTE looks like this:

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT *
FROM recent_orders;

That is a normal CTE. It just gives a name to an intermediate result.

A recursive CTE is different because the CTE’s definition uses the CTE itself.

That is what makes it recursive.

The basic structure looks like:

WITH RECURSIVE cte_name AS (
    -- anchor member
    SELECT ...

    UNION ALL

    -- recursive member
    SELECT ...
    FROM some_table
    JOIN cte_name
      ON ...
)
SELECT *
FROM cte_name;

This is the core recursive CTE pattern.

The two main parts: anchor member and recursive member

Every recursive CTE has two main parts.

1. Anchor member

This is the starting result.

It answers:

• where does the recursion begin?

2. Recursive member

This is the part that keeps finding more rows based on the previous result.

It answers:

• given what we already found, what comes next?

These two parts are usually combined with:

UNION ALL

That lets the CTE grow level by level.

This structure is the key to understanding recursive SQL.

Simple example: employee hierarchy

Let’s use the employee table again:

employee_id	employee_name	manager_id
1	CEO	NULL
2	Alice	1
3	Bob	1
4	Cara	2
5	Dan	2
6	Eva	4

Suppose you want:

• the CEO
• and everyone below the CEO in the reporting chain

A recursive CTE can do that.

Basic recursive CTE pattern

WITH RECURSIVE employee_tree AS (
    -- anchor member
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    -- recursive member
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        et.depth + 1 AS depth
    FROM employees e
    INNER JOIN employee_tree et
        ON e.manager_id = et.employee_id
)
SELECT *
FROM employee_tree
ORDER BY depth, employee_id;

This query does a lot, but the logic is straightforward once broken down.

How the anchor member works

The anchor member is:

SELECT
    employee_id,
    employee_name,
    manager_id,
    0 AS depth
FROM employees
WHERE employee_id = 1

This starts the recursion at:

• employee 1
• which is the CEO

That row becomes the first result in the recursive CTE.

The anchor member is the seed.

Without it, the recursion has nowhere to begin.

How the recursive member works

The recursive member is:

SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    et.depth + 1 AS depth
FROM employees e
INNER JOIN employee_tree et
    ON e.manager_id = et.employee_id

This says:

• find employees whose manager_id matches an employee already found in employee_tree

At first, employee_tree contains only the CEO. So the recursive member finds:

• Alice
• Bob

Then employee_tree now contains:

• CEO
• Alice
• Bob

So the next recursion finds:

• Cara
• Dan

Then it finds:

• Eva

Then eventually there are no more matches. So recursion stops.

That is the core recursive behavior.

Why UNION ALL is usually used

Most recursive CTEs use:

UNION ALL

instead of:

UNION

Why?

Because UNION ALL:

• keeps all rows
• avoids the extra overhead of duplicate elimination
• is usually the intended recursive behavior

UNION removes duplicates, which can sometimes hide issues or add unnecessary cost.

That said, whether you want UNION ALL or UNION depends on the logic. But in practical recursive hierarchy queries, UNION ALL is usually the better default.

Tracking depth

One of the most useful things in recursive CTEs is the ability to track depth.

In the example:

0 AS depth

starts the root at depth 0.

Then:

et.depth + 1 AS depth

increments the level for each recursive step.

This is useful because it lets you:

• see how deep each row is in the hierarchy
• filter to a maximum depth
• format results in tree-like output
• or debug how the recursion behaves

Depth tracking is one of the best habits in recursive CTE design.

Example result from the employee tree

The result might look like:

employee_id	employee_name	manager_id	depth
1	CEO	NULL	0
2	Alice	1	1
3	Bob	1	1
4	Cara	2	2
5	Dan	2	2
6	Eva	4	3

This is a very practical hierarchy expansion result.

It is exactly the kind of output recursive CTEs are built for.

Recursive CTEs are great for parent-child tables

Any table that uses:

• id
• and parent_id

or:

• employee_id
• and manager_id

is often a strong candidate for recursive CTEs.

Common examples:

• categories and parent categories
• employees and managers
• folders and parent folders
• comments and parent comments
• menu items and parent menu items
• referrals and referring accounts
• tasks and parent tasks

This pattern appears constantly in real systems.

That is why recursive CTEs are so valuable.

Example: category tree

Suppose you have categories like this:

category_id	category_name	parent_category_id
1	Electronics	NULL
2	Computers	1
3	Laptops	2
4	Accessories	1
5	Chargers	4

A recursive CTE can expand the whole tree under Electronics.

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth
    FROM categories
    WHERE category_id = 1

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1
    FROM categories c
    JOIN category_tree ct
        ON c.parent_category_id = ct.category_id
)
SELECT *
FROM category_tree
ORDER BY depth, category_id;

This is the same recursive pattern applied to a different hierarchy.

Building hierarchical paths

One of the most useful recursive CTE techniques is path building.

For example, instead of only showing:

• the category ID
• and depth

you might want the full path like:

• Electronics
• Electronics > Computers
• Electronics > Computers > Laptops

This can be done by carrying a path string through the recursion.

Example:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth,
        category_name AS path
    FROM categories
    WHERE category_id = 1

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1,
        ct.path || ' > ' || c.category_name AS path
    FROM categories c
    JOIN category_tree ct
        ON c.parent_category_id = ct.category_id
)
SELECT *
FROM category_tree
ORDER BY depth, category_id;

This is a very powerful pattern for:

• breadcrumbs
• tree displays
• debugging hierarchy structure
• exports
• and search indexing

Why path building is useful

Path columns help when you want to:

• display the hierarchy clearly
• show where each node belongs
• search by full category path
• sort or export tree-like results
• or understand how a row was reached during recursion

It is especially useful in:

• category systems
• folder structures
• org charts
• and comment threads

This is one of the best real-world recursive CTE patterns.

Recursive CTEs can also walk upward

So far, the examples move:

• parent → child

But recursion can also go:

• child → parent

For example, starting from a department or employee, you may want:

• the management chain upward
• or the full category ancestry upward

Example idea: start at Laptops, then move to:

• Computers
• Electronics

This just reverses the join direction.

That is another powerful use of recursive CTEs.

Example: find the chain of managers upward

Suppose you want all managers above employee 6.

WITH RECURSIVE manager_chain AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth
    FROM employees
    WHERE employee_id = 6

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        mc.depth + 1
    FROM employees e
    JOIN manager_chain mc
        ON e.employee_id = mc.manager_id
)
SELECT *
FROM manager_chain
ORDER BY depth;

This starts at the employee and walks upward through the hierarchy.

That is a very useful recursive pattern too.

Recursive CTEs can generate sequences

Recursive CTEs are not only for trees.

They can also generate sequences.

Example: numbers 1 through 10.

WITH RECURSIVE numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT *
FROM numbers;

This starts with:

• 1

Then repeatedly adds:

• 1 more

Until:

• n < 10 is no longer true

This is a simple but useful example because it shows recursion without hierarchy.

Why sequence generation can be useful

Sequence generation can help with:

• date generation
• calendar tables
• testing
• gap filling
• iterative logic
• and controlled expansion of numeric ranges

For example, you can generate:

• days in a month
• levels of recursion
• simulated ranges
• or simple iteration helpers

This shows that recursive CTEs are more general than just tree traversal.

Recursive CTEs can generate date ranges

Example: generate 7 days starting from a date.

WITH RECURSIVE dates AS (
    SELECT DATE '2026-04-01' AS d

    UNION ALL

    SELECT d + INTERVAL '1 day'
    FROM dates
    WHERE d < DATE '2026-04-07'
)
SELECT *
FROM dates;

This kind of pattern is useful in:

• reporting
• date scaffolding
• missing-date detection
• time-series alignment
• and analytics prep

That makes recursive CTEs useful even outside classic hierarchies.

Stopping conditions matter a lot

A recursive CTE must stop somehow.

Sometimes it stops naturally because:

• eventually no more rows match

Other times you need an explicit condition like:

• WHERE n < 10
• or WHERE depth < 5

Without a proper stopping condition, recursion can:

• run too long
• error out at recursion limits
• or loop in bad data situations

That is why recursion safety is one of the most important recursive CTE habits.

Cycles are one of the biggest risks

A cycle happens when hierarchical data loops back on itself.

Example:

• employee A reports to B
• B reports to C
• C reports back to A

Or:

• category 5 has parent 4
• category 4 has parent 2
• category 2 has parent 5

This creates a loop.

If the recursive CTE keeps following the relationship blindly, it may:

• repeat forever
• hit engine recursion limits
• or produce nonsense paths

This is one of the biggest practical dangers with recursive queries.

Preventing cycles

There are a few common ways to reduce cycle risk.

1. Keep hierarchy data clean

The best fix is preventing cycles in the data model itself.

2. Add a max depth condition

Example:

• stop after 20 levels

3. Track visited nodes in the path

If the same ID appears again, stop following that branch

This last pattern is especially useful in graph-like or messy data.

Cycle prevention is one of the most important advanced recursive CTE topics.

Limiting recursion depth

A practical safety pattern is to add depth control.

Example:

WITH RECURSIVE employee_tree AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        et.depth + 1
    FROM employees e
    JOIN employee_tree et
        ON e.manager_id = et.employee_id
    WHERE et.depth < 10
)
SELECT *
FROM employee_tree;

This ensures recursion does not go deeper than 10 levels.

Even if the data is supposed to be clean, depth limits can be a useful safety guard.

Recursive CTEs and performance

Recursive CTEs are powerful, but they are not free.

Performance depends on:

• the size of the hierarchy
• join efficiency
• indexing on parent-child keys
• whether cycles exist
• how many rows each step produces
• and whether the query is building heavy path strings or extra columns

A hierarchy query over a few hundred rows is very different from one over millions of nodes.

So recursive CTEs should be treated like any other serious SQL pattern:

• write clearly
• measure performance
• and support them with the right indexes

Indexing parent-child columns matters

If your recursive query repeatedly does something like:

ON child.parent_id = parent.id

then indexing those key columns matters a lot.

Typical important columns include:

• id
• parent_id
• employee_id
• manager_id
• category_id
• parent_category_id

This is one of the strongest performance habits for hierarchical data.

Without useful indexing, each recursive step may have to do much more work than necessary.

Recursive CTEs are often easier than repeated self-joins

Before recursive CTEs, people sometimes wrote queries with:

• self-join level 1
• self-join level 2
• self-join level 3
• and so on

That only works when the depth is fixed and known.

But real hierarchies are often:

• unknown in depth
• uneven
• or likely to change over time

That is why recursive CTEs are usually the cleaner and more maintainable solution.

They say:

• keep going until the tree ends

That is much better than hard-coding five levels and hoping that is enough.

Common recursive CTE use cases

Here are some of the most common practical use cases.

1. Org charts

Find all reports under a manager.

2. Folder trees

Expand all subfolders under a parent.

3. Category hierarchies

Show all descendants or ancestors in taxonomy structures.

4. Comment threads

Expand nested replies.

5. Bills of materials

Walk parts and sub-parts in manufacturing or inventory.

6. Menu trees

Build navigation structures.

7. Referral chains

Find downstream referrals or upstream referrers.

8. Date and number generation

Generate sequences for scaffolding or testing.

These are exactly the kinds of problems recursive CTEs are designed for.

Common mistakes with recursive CTEs

There are a few recurring mistakes.

1. No clear anchor member

If the starting rows are wrong, everything downstream is wrong.

2. Wrong recursive join direction

This causes the query to walk the hierarchy the wrong way.

3. No stopping condition or safety logic

This can lead to runaway recursion.

4. Ignoring cycles

Bad data can break the whole query.

5. No depth tracking

This makes debugging much harder.

6. No supporting indexes

This can make recursion much slower than necessary.

7. Confusing row meaning at each level

Always know what one result row represents.

These mistakes are very common when people first start using recursion in SQL.

A practical recursive CTE workflow

A strong workflow for recursive CTE design usually looks like this:

Step 1

Define the relationship clearly. Example:

• employee.manager_id -> employees.employee_id

Step 2

Decide the direction. Are you walking:

• downward to descendants or
• upward to ancestors?

Step 3

Write the anchor member. What rows should start the recursion?

Step 4

Write the recursive member. How do you find the next level from the current level?

Step 5

Add depth tracking. This makes inspection and limits easier.

Step 6

Add a safety condition if needed. Especially for messy or large hierarchies.

Step 7

Validate the output carefully. Check:

• row counts
• depth
• path
• duplicates
• and whether the traversal direction is correct

This process makes recursive SQL much easier to build correctly.

FAQ

What is a recursive CTE in SQL?

A recursive CTE is a common table expression that references itself so SQL can repeatedly process hierarchical or sequential data such as org charts, folder trees, categories, and parent-child records.

When should I use a recursive CTE?

Use a recursive CTE when you need to walk through multi-level parent-child relationships, generate sequences, build hierarchical paths, or expand data where each step depends on the previous step.

What are the two main parts of a recursive CTE?

A recursive CTE has an anchor member, which provides the starting rows, and a recursive member, which repeatedly joins back to the CTE to find the next level of rows.

What is the biggest risk with recursive CTEs?

The biggest risk is uncontrolled recursion, especially when circular relationships exist or when the query has no proper stopping condition. That can cause incorrect results, duplicate paths, or recursion-depth errors.

Final thoughts

SQL recursive CTEs are one of the most powerful tools for working with data that has depth.

They let you move beyond flat row-by-row queries and handle:

• org charts
• category trees
• folder structures
• ancestor chains
• sequence generation
• and many other step-by-step expansion problems

The most important ideas to remember are:

• a recursive CTE has an anchor member and a recursive member
• the anchor starts the result
• the recursive member keeps finding the next level
• depth tracking makes recursion easier to understand
• path building makes hierarchies easier to display
• stopping conditions and cycle prevention matter a lot
• and indexing parent-child keys is important for performance

Once you understand those patterns, recursive CTEs stop feeling like advanced magic and start feeling like a very practical way to teach SQL how to walk through hierarchical data.