Visualizing Recursive SQL Queries: A Step-by-Step Walkthrough

For a beginner, learning SQL queries without a clear mental picture of what they do can be confusing and may make it hard to grasp the concepts. Recursive queries can be particularly challenging for a starter to wrap their heads around without a visual aid. In this article, I will explain how recursive queries work using a management chain example, with visualizations that make the process crystal clear for beginners.

Recursive CTEs

A recursive CTE is a CTE that references itself. It's extremely useful for working with hierarchical or tree-structured data, such as organizational charts, file systems, or network graphs.

Recursive CTE Syntax

WITH RECURSIVE cte_name AS (
    -- Base query (non-recursive part)
    SELECT columns
    FROM table
    WHERE conditions

    UNION [ALL]

    -- Recursive query (references the CTE itself)
    SELECT columns
    FROM table
    JOIN cte_name ON join_condition
    WHERE conditions
)
SELECT * FROM cte_name;

Components of a Recursive CTE

• Base Case: The initial query that provides the starting point(s) for recursion
• Recursive Case: The part that references the CTE itself
• Termination Condition: The condition that stops the recursion (usually in the WHERE clause)

Sample Hierarchy Structure Visualization

First, let's visualize a sample data as an organizational chart:

Level 0: Alice Johnson (CEO) [id:1]
           │
           ├─ Level 1: Bob Smith (VP Engineering) [id:2, manager:1]
           │       │
           │       └─ Level 2: Dave Brown (Engineering Manager) [id:4, manager:2]
           │               │
           │               ├─ Level 3: Frank Miller (Senior Developer) [id:6, manager:4]
           │               └─ Level 3: Grace Wilson (Developer) [id:7, manager:4]
           │
           └─ Level 1: Carol Williams (VP Marketing) [id:3, manager:1]
                   │
                   └─ Level 2: Eve Davis (Marketing Manager) [id:5, manager:3]
                           │
                           └─ Level 3: Henry Moore (Marketing Specialist) [id:8, manager:5]

The Recursive Query Components

We can choose to start from any id and generate a hierarchy from there. In this case, we will start from id = 7. Our query will have two essential parts:

1. Base Case: The starting point (WHERE id = 7 - Grace Wilson)
2. Recursive Case: The part that joins the CTE to itself to find managers

The full recursive query will look like this:

WITH RECURSIVE management_chain AS (
    -- Base case: start with Grace (level 0)
    SELECT 
        id, 
        name, 
        position, 
        manager_id,
        0 AS level  -- Starting level
    FROM employees_hierarchy
    WHERE id = 7

    UNION

    -- Recursive case: increment level by 1
    SELECT 
        e.id, 
        e.name, 
        e.position, 
        e.manager_id,
        m.level + 1  -- Increment level
    FROM employees_hierarchy e
    JOIN management_chain m ON e.id = m.manager_id
)
SELECT 
    id, 
    name, 
    position,
    level
FROM management_chain
ORDER BY level DESC;  -- Show hierarchy from top to bottom

Step-by-Step Execution Visualization

Let's visualize how the database processes this query:

Initialization Phase (Base Case)

WITH RECURSIVE management_chain AS (
    -- First iteration: base case
    SELECT id, name, position, manager_id
    FROM employees_hierarchy
    WHERE id = 7  -- Grace Wilson
)

Result Set After Base Case:

| id | name         | position   | manager_id |
|----|--------------|------------|------------|
| 7  | Grace Wilson | Developer  | 4          |

Recursive Phase - Iteration 1

Now the recursive part joins the initial result (Grace) with the employees table to find Grace's manager (manager_id = 4):

    -- Recursive part joins Grace (id=7) with her manager
    SELECT e.id, e.name, e.position, e.manager_id
    FROM employees_hierarchy e
    JOIN management_chain m ON e.id = m.manager_id  -- Finds where e.id = 4

New Rows Added:

| id | name       | position           | manager_id |
|----|------------|--------------------|------------|
| 4  | Dave Brown | Engineering Manager| 2          |

Current Result Set:

| id | name         | position           | manager_id |
|----|--------------|--------------------|------------|
| 7  | Grace Wilson | Developer          | 4          |
| 4  | Dave Brown   | Engineering Manager| 2          |

Recursive Phase - Iteration 2

Now we look for Dave Brown's manager (manager_id = 2):

    -- Recursive part joins Dave (id=4) with his manager
    SELECT e.id, e.name, e.position, e.manager_id
    FROM employees_hierarchy e
    JOIN management_chain m ON e.id = m.manager_id  -- Finds where e.id = 2

New Rows Added:

| id | name      | position        | manager_id |
|----|-----------|-----------------|------------|
| 2  | Bob Smith | VP Engineering  | 1          |

Current Result Set:

| id | name         | position           | manager_id |
|----|--------------|--------------------|------------|
| 7  | Grace Wilson | Developer          | 4          |
| 4  | Dave Brown   | Engineering Manager| 2          |
| 2  | Bob Smith    | VP Engineering     | 1          |

Recursive Phase - Iteration 3

Now we look for Bob Smith's manager (manager_id = 1):

    -- Recursive part joins Bob (id=2) with his manager
    SELECT e.id, e.name, e.position, e.manager_id
    FROM employees_hierarchy e
    JOIN management_chain m ON e.id = m.manager_id  -- Finds where e.id = 1

New Rows Added:

| id | name         | position | manager_id |
|----|--------------|----------|------------|
| 1  | Alice Johnson| CEO      | NULL       |

Current Result Set:

| id | name         | position           | manager_id |
|----|--------------|--------------------|------------|
| 7  | Grace Wilson | Developer          | 4          |
| 4  | Dave Brown   | Engineering Manager| 2          |
| 2  | Bob Smith    | VP Engineering     | 1          |
| 1  | Alice Johnson| CEO                | NULL       |

Termination Phase

In the next iteration, we'd look for Alice's manager (manager_id = NULL), which returns no rows, so the recursion stops.

Final Output

After the query completes, we select just the id, name, and position columns and order by id:

| id | name         | position           |
|----|--------------|--------------------|
| 1  | Alice Johnson| CEO                |
| 2  | Bob Smith    | VP Engineering     |
| 4  | Dave Brown   | Engineering Manager|
| 7  | Grace Wilson | Developer          |

Visualizing the Recursion Process

Here's how to imagine the recursion working:

1. Start at the leaf node (Grace):

   Grace (7) → Dave (4)

1. First recursion finds Dave's manager:

   Grace (7) → Dave (4) → Bob (2)

1. Second recursion finds Bob's manager:

   Grace (7) → Dave (4) → Bob (2) → Alice (1)

1. Third recursion stops (Alice has no manager):

   Grace (7) → Dave (4) → Bob (2) → Alice (1) → STOP

Key Concepts Illustrated

1. Base Case: Defines where to start (Grace Wilson)
2. Recursive Join: Connects each employee to their manager
3. Termination: Stops when no more managers are found
4. UNION vs UNION ALL: Using UNION eliminates duplicates (though not needed here)

Why This Visualization Helps Beginners

1. Shows the step-by-step expansion of the result set
2. Illustrates how each recursive call builds on previous results
3. Makes the termination condition clear (NULL manager_id)
4. Demonstrates the hierarchical nature of the query

This example perfectly shows how recursive queries "walk up" a hierarchy by repeatedly joining the intermediate results to the original table.