Recursive Query Patterns

In this lesson, you will learn how to navigate deep, multi-level hierarchies—like an organizational chart—using recursive queries to traverse data until you reach the "leaves" of the tree.

The Challenge of Hierarchies

Imagine you have an employees table where each row contains an employee_id and a manager_id. To find who reports to a specific director, you could write multiple joins, but that only works if you know exactly how many levels deep the reporting line goes. If a company grows, your query breaks. A recursive query solves this by letting the SQL engine "loop" through the table until it has retrieved every descendant in the chain.

Anatomy of a Recursive Query

A recursive query (often called a Common Table Expression or CTE) has two distinct parts joined by a UNION ALL:

1. The Anchor Member: This is the starting point. It fetches the top-level record (e.g., the CEO).
2. The Recursive Member: This part refers back to the CTE itself. It joins the previous result set to the original table to find the next level of subordinates.

Here is how it looks in practice:

WITH RECURSIVE OrgChart AS (
    -- Anchor: Start with the CEO
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive step: Find subordinates of the previous results
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;

How It Works

When you run this, the engine first grabs the CEO. Then, it uses that CEO’s ID to find everyone whose manager_id matches. It takes those new people and treats them as the "input" to find their subordinates. This repeats automatically. Because the recursive member joins back to the CTE name (OrgChart), the engine keeps diving deeper into the tree, layer by layer, until it finds no more matches, at which point the recursion stops.

This pattern is the standard way to map out any structure that contains nested relationships, such as department hierarchies, project task dependencies, or folder systems.

Key Takeaway: Recursive queries allow you to traverse unlimited levels of parent-child relationships by combining an initial starting point with a self-referencing loop that executes until no further connections are found.