How to Use Recursive CTEs in SQL

When working with SQL, you’ll eventually encounter data that references itself. Examples include employee-manager relationships, organizational hierarchies, product categories, folder structures, and family trees.

Querying these relationships can become difficult because you don’t always know how many levels deep the hierarchy goes.

This is where recursive Common Table Expressions (CTEs) become incredibly useful.

A recursive CTE allows a query to repeatedly reference its own results until a stopping condition is reached. This makes it possible to traverse hierarchical and tree-like data structures directly in SQL.

In this guide, you’ll learn what recursive CTEs are, how they work, when to use them, and practical examples you can apply in real-world analytics and database projects.

What Is a CTE?

A Common Table Expression (CTE) is a temporary result set defined within a SQL query.

Basic syntax:

WITH sales_summary AS (
    SELECT
        product,
        SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY product
)
SELECT *
FROM sales_summary;

The CTE behaves like a temporary table within the query.

What Makes a CTE Recursive?

A recursive CTE is a Common Table Expression that references itself. It consists of an anchor query and a recursive query, allowing SQL to repeatedly process hierarchical data until a termination condition is met.

A recursive CTE references itself.

General structure:

WITH RECURSIVE cte_name AS (

    -- Anchor query

    SELECT ...

    UNION ALL

    -- Recursive query

    SELECT ...
    FROM cte_name

)
SELECT *
FROM cte_name;

The recursive portion repeatedly runs until no more rows are returned.

Understanding the Two Parts

Every recursive CTE contains:

1. Anchor Query

The starting point.

Example:

SELECT employee_id,
       employee_name,
       manager_id
FROM employees
WHERE manager_id IS NULL

This returns the CEO.

2. Recursive Query

The recursive query finds child records.

Example:

SELECT e.employee_id,
       e.employee_name,
       e.manager_id
FROM employees e
JOIN employee_hierarchy h
ON e.manager_id = h.employee_id

This repeatedly finds employees reporting to previous employees.

Visualizing an Employee Hierarchy

Consider the following table:

Employee ID	Employee Name	Manager ID
1	CEO	NULL
2	Sarah	1
3	David	1
4	Emma	2
5	James	2
6	Michael	3

The hierarchy looks like:

CEO
├── Sarah
│   ├── Emma
│   └── James
└── David
    └── Michael

A recursive CTE can retrieve the entire hierarchy.

Example 1: Employee Hierarchy

WITH RECURSIVE employee_hierarchy AS (

    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level

    FROM employees

    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        h.level + 1

    FROM employees e

    JOIN employee_hierarchy h
      ON e.manager_id = h.employee_id

)

SELECT *
FROM employee_hierarchy;

Result:

Employee	Level
CEO	1
Sarah	2
David	2
Emma	3
James	3
Michael	3

The recursion continues until no more child employees exist.

How Recursive Execution Works

Step 1:

Anchor query returns:

CEO

Step 2:

Recursive query returns:

Sarah
David

Step 3:

Recursive query runs again:

Emma
James
Michael

Step 4:

No more child records exist.

Recursion stops automatically.

Why Use Recursive CTEs?

Recursive CTEs are ideal when dealing with unknown hierarchy depths.

Examples include:

• Organization charts
• Product categories
• Folder structures
• Bill of materials
• Family trees
• Dependency graphs

Without recursion, multiple self-joins may be required.

Example 2: Product Categories

Suppose an e-commerce database contains:

Category ID	Category Name	Parent Category
1	Electronics	NULL
2	Computers	1
3	Laptops	2
4	Gaming Laptops	3

Hierarchy:

Electronics
└── Computers
    └── Laptops
        └── Gaming Laptops

Recursive query:

WITH RECURSIVE category_tree AS (

    SELECT
        category_id,
        category_name,
        parent_category,
        1 AS level

    FROM categories

    WHERE parent_category IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category,
        ct.level + 1

    FROM categories c

    JOIN category_tree ct
      ON c.parent_category = ct.category_id

)

SELECT *
FROM category_tree;

This returns the complete category structure.

Example 3: Building Paths

Recursive CTEs can create hierarchical paths.

Example:

Electronics
Electronics > Computers
Electronics > Computers > Laptops

Query:

WITH RECURSIVE category_path AS (

    SELECT
        category_id,
        category_name,
        parent_category,
        category_name AS path

    FROM categories

    WHERE parent_category IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category,
        CONCAT(
            cp.path,
            ' > ',
            c.category_name
        )

    FROM categories c

    JOIN category_path cp
      ON c.parent_category = cp.category_id

)

SELECT *
FROM category_path;

This is useful for navigation menus and reporting.

Example 4: Generating Numbers

Recursive CTEs are also used to generate sequences.

Example:

WITH RECURSIVE numbers AS (

    SELECT 1 AS num

    UNION ALL

    SELECT num + 1

    FROM numbers

    WHERE num < 10

)

SELECT *
FROM numbers;

Output:

1
2
3
4
5
6
7
8
9
10

This demonstrates recursion without hierarchical data.

Common SQL Databases Supporting Recursive CTEs

Most modern databases support recursive CTEs.

Examples include:

• PostgreSQL
• SQL Server
• MySQL (Version 8+)
• SQLite
• Oracle Database

Syntax may vary slightly between platforms.

Recursive CTE vs Self-Join

Many beginners use repeated self-joins.

Example:

Employee
Manager
Manager's Manager
Manager's Manager's Manager

This works only when the hierarchy depth is known.

Recursive CTEs handle unlimited hierarchy levels dynamically.

Feature	Self-Join	Recursive CTE
Fixed Levels	Yes	No
Dynamic Depth	No	Yes
Readability	Moderate	High
Scalability	Limited	Better

Performance Considerations

Recursive CTEs are powerful but should be used carefully.

Limit Recursion Depth

Deep hierarchies can increase execution time.

Index Parent Columns

Example:

manager_id
parent_category

Indexes improve recursive joins significantly.

Avoid Infinite Loops

Circular references can cause endless recursion.

Example:

A → B
B → C
C → A

This creates a cycle.

Many databases provide recursion limits to prevent this.

Common Mistakes

Missing Termination Conditions

Recursion must eventually stop.

Using UNION Instead of UNION ALL

UNION removes duplicates and may reduce performance.

Ignoring Circular References

Cycles can cause unexpected behavior.

Not Tracking Levels

Adding a hierarchy level column often improves reporting and debugging.

Real-World Analytics Use Cases

Data teams frequently use recursive CTEs for:

Organizational Reporting

Analyze reporting structures.

Product Taxonomies

Navigate category hierarchies.

Customer Referral Trees

Track referral networks.

Data Lineage

Understand data dependencies.

Workflow Analysis

Trace process hierarchies.

These use cases appear regularly in data engineering and analytics projects.

Recursive CTEs are one of the most powerful SQL features for working with hierarchical and self-referencing data. By combining an anchor query with a recursive query, they allow databases to traverse organizational structures, category trees, folder hierarchies, and many other complex relationships.

While they require careful design to avoid performance issues and circular references, recursive CTEs often provide a cleaner and more scalable solution than multiple self-joins.

For data analysts, analytics engineers, and database professionals, understanding recursive CTEs is an important skill that unlocks advanced SQL querying capabilities.

FAQ