SQL recursive CTEs: querying hierarchical data

Some relationships continue across many levels

Relational tables are naturally flat. Each row contains columns, but the relationships between rows can form deeper structures. Consider an online store with product categories, where parent_category_id points to another row in the same table:

Products
├── Electronics
│   ├── Accessories
│   │   ├── Keyboards
│   │   └── Mice
│   └── Monitors
└── Furniture
    ├── Chairs
    └── Desks

Since Accessories.parent_category_id = 2 and category 2 is Electronics, Accessories is a child of Electronics. This is a hierarchy. Product categories, employees and managers, folders and subfolders, comments and replies, geographic regions, and organizational units are all hierarchical. The important characteristic is that the same relationship can continue through an unknown number of levels. That is where recursive queries become useful.

A self join can find one level

Suppose the requirement is find the direct children of Electronics. A normal self join is enough:

SELECT
    child.category_id,
    child.category_name
FROM categories AS parent
JOIN categories AS child
    ON child.parent_category_id = parent.category_id
WHERE parent.category_name = 'Electronics'
ORDER BY child.category_id;

This works because we only need one relationship step. But now suppose the requirement changes to find every category below Electronics, regardless of depth. The required result is Accessories, Monitors, Keyboards, Mice. Keyboards and Mice are not direct children of Electronics; they are children of Accessories. A single self join follows only one level. We need SQL to repeatedly follow the relationship until no more child rows exist. This is what a recursive CTE does.

What is a recursive CTE?

A recursive CTE is a query that can refer back to its own result while it is being built. It has two important parts: an anchor query and a recursive query.

WITH RECURSIVE hierarchy AS (
    -- Anchor: where should traversal begin?
    SELECT ... FROM ... WHERE ...

    UNION ALL

    -- Recursive: how do I find the next related level?
    SELECT ... FROM ... JOIN hierarchy ON ...
)
SELECT * FROM hierarchy;

The database repeatedly applies the recursive part until it can no longer find new rows. The examples here use PostgreSQL-style WITH RECURSIVE syntax; recursive-query syntax and available safeguards vary between database systems.

Finding every descendant

To return Electronics and every category below it:

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

    UNION ALL

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

The query found categories several levels below Electronics without knowing beforehand how deep the hierarchy would be.

How the recursion unfolds

The anchor returns Electronics at depth = 0 and tells the recursive query to start there. The recursive member joins c.parent_category_id = ct.category_id: for the first step, ct.category_id = 2, so SQL finds rows with parent_category_id = 2 (Accessories, Monitors) at depth 1. It then repeats on those new rows: for Accessories (category_id = 4), it finds parent_category_id = 4 (Keyboards, Mice) at depth 2. Keyboards, Mice, and Monitors have no children, so no more rows are found and the recursion stops.

Tracking depth and excluding the root

The expression ct.depth + 1 tracks how far each row is from the start, which answers questions such as how deeply nested a category is, which rows are direct children (WHERE depth = 1), and which are deeper (WHERE depth > 1). Because the anchor appears at depth = 0, you can exclude the starting row when the requirement asks for every descendant of Electronics (Electronics is not a descendant of itself) with WHERE depth > 0:

The query that finds only immediate children

Suppose the requirement is find every category below Electronics. Someone writes a single self join filtered to parent.category_id = 2.

SELECT
    child.category_id,
    child.category_name
FROM categories AS parent
JOIN categories AS child
    ON child.parent_category_id = parent.category_id
WHERE parent.category_id = 2;

-- A: Accessories, Monitors, Keyboards, Mice
-- B: Accessories, Monitors
-- C: Keyboards, Mice
-- D: every category in the table

The fix: follow the relationship recursively

Replace the one-level join with a recursive CTE whose recursive member joins back to the tree, and keep WHERE depth > 0 for descendants only:

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

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_category_id, ct.depth + 1
    FROM categories AS c
    JOIN category_tree AS ct
        ON c.parent_category_id = ct.category_id
)
SELECT category_id, category_name, depth
FROM category_tree
WHERE depth > 0;

The important difference is not merely JOIN vs WITH RECURSIVE; it is one relationship step vs repeating the relationship until it ends.

Finding ancestors instead of descendants

So far we moved downward from parent to child. Recursive queries can also move upward. To show the complete category path above Keyboards (Keyboards, Accessories, Electronics, Products), start from Keyboards and reverse the join:

WITH RECURSIVE ancestors AS (
    SELECT category_id, category_name, parent_category_id, 0 AS distance
    FROM categories
    WHERE category_id = 8

    UNION ALL

    SELECT
        parent.category_id,
        parent.category_name,
        parent.parent_category_id,
        a.distance + 1
    FROM categories AS parent
    JOIN ancestors AS a
        ON parent.category_id = a.parent_category_id
)
SELECT category_id, category_name, distance
FROM ancestors
ORDER BY distance;

The recursive structure is almost identical; only the relationship direction changed. Downward uses ON child.parent_category_id = tree.category_id (which rows call the current row their parent). Upward uses ON parent.category_id = tree.parent_category_id (which row is the parent the current row references). Before writing the recursion, decide which direction you need to travel.

Building a hierarchical path

Sometimes the required output is the full path, such as Products > Electronics > Accessories > Keyboards. Build a path while traversing downward from the root, appending each name:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name::TEXT AS category_path,
        0 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

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

Each recursive step takes the existing path (Products > Electronics > Accessories) and appends > Keyboards. This is useful for breadcrumbs, folder paths, organization chains, and category navigation. The string-casting syntax is PostgreSQL-style; other databases may differ.

Finding every root, and carrying state

Our sample has one root (Products, where parent_category_id IS NULL), but a table can contain several independent trees. Starting the anchor from WHERE parent_category_id IS NULL expands all of them, producing an entire forest, so the anchor determines the scope of the recursion. Depth and path are examples of values carried from one step to the next; you can also carry a total cost, ancestor ID, starting root, or accumulated quantity. The pattern is: current row’s calculated state plus a new relationship produces the next row’s calculated state.

When a hierarchy does not end

A proper category tree should eventually reach a leaf, but bad data can create a cycle: if Keyboards pointed back up to Electronics, the relationship would return to an already-visited row and there would be no natural endpoint. That can cause recursive processing to continue until the database’s recursion safeguard stops it or the query fails. Cycles arise from incorrect parent references, corrupted graph data, bad application logic, or relationships that form general graphs rather than trees.

UNION ALL vs UNION

You will commonly see the anchor combined with the recursive query by UNION ALL, which keeps the rows produced by the recursive process. Using UNION introduces duplicate elimination, which can change the result and may alter recursive behaviour. Do not switch from UNION ALL to UNION as a substitute for proper cycle handling: a cycle may involve rows whose calculated columns such as depth or path change each iteration, so the complete rows may not even be duplicates. Cycle handling should reflect the actual relationship rules.

The recursive join defines the entire traversal

Output ordering, and when not to use recursion

A recursive CTE discovers hierarchical rows but does not guarantee the presentation order you want. ORDER BY depth, category_id groups by level rather than by tree branch; if tree-style display order matters, construct an ordering path or another explicit sort key. Never depend on an observed output order you did not request. Finally, use recursion only when the number of levels is unknown or variable. If you only need each category’s direct parent, a normal self join with LEFT JOIN categories AS parent ON parent.category_id = child.parent_category_id is simpler. Use recursion when the problem itself is recursive: repeat this relationship until there are no more rows to follow.

How to spot recursive-query problems

Think about recursive queries when the requirement says all descendants, all ancestors, every manager above this employee, all reports beneath this manager, category tree, folder hierarchy, complete breadcrumb path, nested comments and replies, an unknown number of parent-child levels, traverse until the root, or traverse until there are no children.

Watch for these common mistakes:

• A self join retrieves only one level when all levels are required.
• The anchor starts from more rows than intended.
• Parent and child directions are reversed.
• The recursive join condition does not describe one valid hierarchy step.
• The root row is included when the requirement asks only for descendants.
• Cyclic data causes recursion not to terminate naturally.
• Output order is assumed to be hierarchical without an explicit sort.
• Recursion is used for a problem that needs only a simple one-level join.

Answer five questions before writing a recursive query

Start somewhere
      v
Follow one relationship
      v
Use the newly discovered rows
      v
Follow the same relationship again
      v
Repeat until no valid next row exists

One more prediction

Requirement: return every descendant of Electronics, including descendants several levels below it. Which approach fits?

-- A: SELECT child.* FROM categories AS parent
--    JOIN categories AS child
--      ON child.parent_category_id = parent.category_id
--    WHERE parent.category_id = 2;

-- B: WITH RECURSIVE category_tree AS (
--      SELECT category_id, category_name, parent_category_id
--      FROM categories WHERE category_id = 2
--      UNION ALL
--      SELECT c.category_id, c.category_name, c.parent_category_id
--      FROM categories AS c
--      JOIN category_tree AS ct ON c.parent_category_id = ct.category_id)
--    SELECT * FROM category_tree WHERE category_id <> 2;

-- C: SELECT DISTINCT parent_category_id FROM categories;

-- D: SELECT * FROM categories WHERE parent_category_id = 2;

Summary

Recursive queries are designed for relationships that repeat across an unknown number of levels. A hierarchical table commonly stores a row ID and a parent row ID (category_id, parent_category_id). A recursive CTE has two main parts: the anchor defines where traversal begins (WHERE category_id = 2), and the recursive member defines one relationship step (JOIN category_tree AS ct ON c.parent_category_id = ct.category_id), combined with UNION ALL.