Recursive Common Table Expressions

In this lesson, you will learn how to use Recursive Common Table Expressions (CTEs) to navigate complex parent-child relationships stored within your relational tables.

Moving Beyond Simple Hierarchies

Up until now, you have used window functions to analyze data across rows. However, when data represents a structure like an organizational chart or a category tree, you need a way to "walk" the tree. A Recursive CTE allows a query to reference itself, repeatedly executing until it reaches the leaves of your data structure.

The Anatomy of a Recursive CTE

A Recursive CTE is composed of two primary parts joined by a UNION ALL:

1. The Anchor Member: This is the starting point of your query. It identifies the root of your tree (e.g., the CEO of a company or the top-level category).
2. The Recursive Member: This part references the CTE name itself. It joins the result of the previous step back to the source table to find the "children" of the current rows.

WITH RECURSIVE CategoryPath AS (
    -- Anchor: Find the root level
    SELECT id, name, parent_id
    FROM Categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive: Find children of the previous level
    SELECT c.id, c.name, c.parent_id
    FROM Categories c
    INNER JOIN CategoryPath cp ON c.parent_id = cp.id
)
SELECT * FROM CategoryPath;

How It Works

The database engine executes the Anchor Member first, creating the initial result set. It then feeds those results into the Recursive Member. As long as the recursive part finds rows that match the criteria (e.g., categories whose parent_id matches an id from the previous step), it continues to join and expand the result set. When the recursive join returns zero new rows, the process stops, and the full "path" is returned.

This technique is essential for graph traversal and hierarchical reporting where the depth of the tree is unknown. By iteratively building your result set, you can flatten a complex nested structure into a single, queryable list without needing to know the exact number of levels beforehand.

Key Takeaway: Recursive CTEs allow you to traverse hierarchical structures by combining an initial starting point with a self-referencing query that expands until the entire branch is explored.