How to Use Recursive CTEs to Query Hierarchical Data in SQL

What You’ll Learn

By the end of this lesson, you will know how to use a recursive common table expression to walk through self-referencing data such as an employee hierarchy or a category tree.

• Recognize the two parts of a recursive CTE: the anchor query and the recursive query.
• Return every descendant below a root record.
• Track useful information such as hierarchy depth and a readable path.
• Avoid common problems such as missing termination conditions and cycles.

The Concept

A recursive CTE is a named query that can refer to itself. It is useful when rows describe relationships within the same table. For example, an employees table might contain a manager_id column that points back to another row in the same table.

A recursive CTE has two main parts:

• Anchor query: selects the starting row or rows.
• Recursive query: finds the next level by joining table rows to records already found.

The two parts are joined with UNION ALL. The database repeatedly evaluates the recursive part until it finds no more matching rows.

If you are refreshing your knowledge of selecting and filtering rows, review these SQL SELECT queries before continuing.

A recursive CTE is a good choice when the hierarchy depth is not known in advance. A query can then return a CEO, the CEO’s direct reports, their reports, and so on without writing a separate join for every possible level.

Basic Example

Suppose an organization stores each employee’s manager in the same table. The following query starts with the CEO and recursively finds every employee below that person.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    job_title VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, employee_name, job_title, manager_id) VALUES
    (1, 'Maya Chen', 'Chief Executive Officer', NULL),
    (2, 'Luis Romero', 'VP of Engineering', 1),
    (3, 'Priya Shah', 'VP of Sales', 1),
    (4, 'Noah Williams', 'Engineering Manager', 2),
    (5, 'Ava Patel', 'Software Engineer', 4),
    (6, 'Ethan Brooks', 'Software Engineer', 4),
    (7, 'Sofia Martin', 'Account Executive', 3);

WITH RECURSIVE organization AS (
    SELECT
        employee_id,
        employee_name,
        job_title,
        manager_id,
        0 AS hierarchy_level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        employee.employee_id,
        employee.employee_name,
        employee.job_title,
        employee.manager_id,
        organization.hierarchy_level + 1
    FROM employees AS employee
    JOIN organization
        ON employee.manager_id = organization.employee_id
)
SELECT
    employee_id,
    employee_name,
    job_title,
    hierarchy_level
FROM organization
ORDER BY hierarchy_level, employee_id;

Expected Output

employee_id | employee_name | job_title                | hierarchy_level
1           | Maya Chen     | Chief Executive Officer  | 0
2           | Luis Romero   | VP of Engineering       | 1
3           | Priya Shah    | VP of Sales             | 1
4           | Noah Williams | Engineering Manager     | 2
7           | Sofia Martin  | Account Executive       | 2
5           | Ava Patel     | Software Engineer       | 3
6           | Ethan Brooks  | Software Engineer       | 3

How the Code Works

The anchor query selects the employee whose manager_id is NULL. In this data, that is Maya Chen, the top-level employee. It assigns that row a hierarchy_level of 0.

The recursive query joins employees to the rows already stored in organization. The condition employee.manager_id = organization.employee_id means that each newly selected employee reports to someone already found.

For every recursive step, the query increases hierarchy_level by one. Direct reports are level 1, reports of those employees are level 2, and so on.

The recursion stops naturally when the current level has no employees reporting to it. The final SELECT reads from the completed CTE just like it would read from a regular table or non-recursive CTE.

UNION ALL is normally preferred here because each employee should be discovered once and the query does not need the extra duplicate-removal work performed by UNION.

Another Example

Hierarchical product categories use the same pattern. This example builds a readable category path while traversing from top-level categories to their descendants. The path is useful when displaying breadcrumbs or checking where a category belongs.

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INTEGER REFERENCES categories(category_id)
);

INSERT INTO categories (category_id, category_name, parent_category_id) VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Laptops', 2),
    (4, 'Accessories', 2),
    (5, 'Cameras', 1),
    (6, 'Digital Cameras', 5);

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth,
        CAST(category_name AS VARCHAR(500)) AS category_path
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        category.category_id,
        category.category_name,
        category.parent_category_id,
        category_tree.depth + 1,
        CAST(category_tree.category_path || ' > ' || category.category_name AS VARCHAR(500))
    FROM categories AS category
    JOIN category_tree
        ON category.parent_category_id = category_tree.category_id
)
SELECT
    category_id,
    category_name,
    depth,
    category_path
FROM category_tree
ORDER BY category_path;

The anchor rows are the top-level categories. Each recursive step appends a child category to the existing path, producing values such as Electronics > Computers > Laptops. The exact concatenation syntax can vary slightly between database systems, so check your database documentation if it does not support the || operator.

Common Mistakes

• Forgetting the recursive keyword: Many database systems require WITH RECURSIVE, not just WITH, when a CTE refers to itself.
• Using the wrong join direction: To find children, join the child’s parent column to the current row’s ID: child.parent_id = tree.id.
• Starting with the wrong rows: The anchor determines the scope of the traversal. Use WHERE parent_id IS NULL for an entire forest, or use a specific ID to begin at one branch.
• Ignoring cycles: A malformed relationship where an employee eventually points back to an earlier manager can cause excessive recursion or an error. Enforce valid relationships in application logic or add cycle-detection logic when your database supports it.
• Assuming result order: Recursive evaluation does not automatically produce the visual order of a tree. Use an explicit ORDER BY, often based on depth, IDs, or a constructed path.
• Ignoring database recursion limits: Very deep or malformed hierarchies may reach a database-specific recursion limit. Validate data and test realistic maximum depths.

Try It Yourself

Using the employees table from the first example, modify the recursive CTE so it starts with Luis Romero instead of the CEO. Return Luis and everyone who reports directly or indirectly to him. Include the employee name, job title, and hierarchy level relative to Luis.

Challenge

Write a recursive CTE for the following support_categories table:

• Start at the category named Technical Support.
• Return that category and all of its descendants.
• Include category_name, depth, and a path from the root category to each result.
• Order the results by the constructed path.

Assume the table has already been populated with the data below.

CREATE TABLE support_categories (
    category_id INTEGER PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INTEGER REFERENCES support_categories(category_id)
);

INSERT INTO support_categories (category_id, category_name, parent_category_id) VALUES
    (1, 'Customer Help', NULL),
    (2, 'Technical Support', 1),
    (3, 'Login Issues', 2),
    (4, 'Password Reset', 3),
    (5, 'Hardware Support', 2),
    (6, 'Billing Questions', 1);

Solution

The anchor query selects only Technical Support. Because the recursive query follows child rows from that starting point, unrelated branches such as Billing Questions are not included.

WITH RECURSIVE support_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth,
        CAST(category_name AS VARCHAR(500)) AS category_path
    FROM support_categories
    WHERE category_name = 'Technical Support'

    UNION ALL

    SELECT
        category.category_id,
        category.category_name,
        category.parent_category_id,
        support_tree.depth + 1,
        CAST(support_tree.category_path || ' > ' || category.category_name AS VARCHAR(500))
    FROM support_categories AS category
    JOIN support_tree
        ON category.parent_category_id = support_tree.category_id
)
SELECT
    category_name,
    depth,
    category_path
FROM support_tree
ORDER BY category_path;

The result contains Technical Support at depth 0, its direct children at depth 1, and Password Reset at depth 2. The constructed path makes the parent-child relationship clear in the result.

Key Takeaways

• A recursive CTE combines an anchor query with a recursive query using UNION ALL.
• Self-referencing tables store relationships such as employee-to-manager or category-to-parent links.
• The recursive join finds the next level of descendants until no matching rows remain.
• Tracking depth and a path makes hierarchical results easier to display and analyze.
• Validate hierarchical data carefully because cycles, incorrect joins, and unexpectedly deep trees can cause problems.