SQL Recursive Queries - Understanding Hierarchical Data

Introduction

Recursive queries help you work with hierarchical data, like organization charts or folder structures. Think of it as following a trail of breadcrumbs to find all connected items.

Why Recursive Queries Matter?

They’re useful when you need to:

• Navigate organizational hierarchies
• Build folder trees
• Find all connected items
• Traverse relationships of unknown depth

Main Content: Understanding Recursive Queries

Let’s use this simple employee hierarchy table:

-- Create a simple table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

-- Insert sample data
INSERT INTO employees VALUES
(1, 'John (CEO)', NULL),
(2, 'Sarah (VP)', 1),
(3, 'Mike (VP)', 1),
(4, 'Laura (Manager)', 2),
(5, 'Steve (Manager)', 2),
(6, 'Tom (Staff)', 4),
(7, 'Amy (Staff)', 4),
(8, 'David (Staff)', 5);

Example 1: Basic Recursive Query

Let’s find all employees under a manager:

WITH RECURSIVE emp_hierarchy AS (
    -- Base case: start with top manager
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 as level,
        employee_name as path
    FROM employees
    WHERE employee_id = 2  -- Starting with Sarah

    UNION ALL

    -- Recursive case: find subordinates
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        h.level + 1,
        h.path || ' > ' || e.employee_name
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    level,
    path
FROM emp_hierarchy
ORDER BY level, employee_name;

This query shows:

1. Organizational hierarchy under Sarah
2. Level of each employee
3. Path from top to each employee

Results:

level | path
------|--------------------------------
1     | Sarah (VP)
2     | Sarah (VP) > Laura (Manager)
2     | Sarah (VP) > Steve (Manager)
3     | Sarah (VP) > Laura (Manager) > Tom (Staff)
3     | Sarah (VP) > Laura (Manager) > Amy (Staff)
3     | Sarah (VP) > Steve (Manager) > David (Staff)

Example 2: Finding All Managers

Let’s trace the management chain upward:

WITH RECURSIVE manager_chain AS (
    -- Base case: start with employee
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 as level
    FROM employees
    WHERE employee_id = 6  -- Starting with Tom

    UNION ALL

    -- Recursive case: find managers
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        m.level + 1
    FROM employees e
    JOIN manager_chain m ON e.employee_id = m.manager_id
)
SELECT 
    level,
    employee_name as manager
FROM manager_chain
ORDER BY level;

This query shows:

1. All managers above Tom
2. Their levels in hierarchy

Results:

level | manager
------|-------------
1     | Tom (Staff)
2     | Laura (Manager)
3     | Sarah (VP)
4     | John (CEO)

Example 3: Level-Based Analysis

Let’s count employees at each level:

WITH RECURSIVE org_levels AS (
    -- Base case: start with CEO
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: find all employees
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        o.level + 1
    FROM employees e
    JOIN org_levels o ON e.manager_id = o.employee_id
)
SELECT 
    level,
    COUNT(*) as employees_at_level
FROM org_levels
GROUP BY level
ORDER BY level;

This query shows:

1. Number of employees at each level
2. Organization depth

Results:

level | employees_at_level
------|-------------------
1     | 1
2     | 2
3     | 2
4     | 3

Best Practices

1. Base Case

   • Start with clear condition
   • Limit initial scope
   • Handle NULL values
   • Keep it simple

2. Recursive Part

   • Ensure termination
   • Control depth
   • Maintain performance
   • Clear join conditions

3. Performance

   • Add MAXRECURSION hint if needed
   • Index join columns
   • Limit result size
   • Test with real data

4. Common Uses

   • Organization charts
   • File systems
   • Network paths
   • Bill of materials

5. Things to Remember

   • Must have termination condition
   • Base and recursive parts
   • UNION ALL vs UNION
   • Performance implications

Remember:

• Start simple
• Ensure termination
• Test thoroughly
• Watch performance

Common Mistakes to Avoid:

1. Infinite recursion
2. Missing base case
3. Complex conditions
4. Poor performance

Happy querying! 🚀