Recursive Queries in SQL

Introduction

Recursive queries allow SQL databases to process hierarchical or tree-structured data, such as organizational charts, category trees, and ancestry relationships. They are implemented using Common Table Expressions (CTEs) with the WITH RECURSIVE clause.

This tutorial covers the fundamentals of recursive queries, their syntax, and practical examples.

Why Use Recursive Queries?

Recursive queries are useful when working with:

• Hierarchical data (e.g., employee-manager relationships).
• Tree structures (e.g., category-subcategory systems).
• Graph traversal (e.g., shortest path algorithms).

Instead of performing multiple self-joins, recursive queries efficiently retrieve multi-level hierarchical data in a single execution.

Understanding Recursive CTEs

A recursive CTE consists of two parts:

1. Anchor Query – The base case that retrieves the first level of data.
2. Recursive Query – The recursive part that joins data with itself to retrieve subsequent levels.

Basic Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor Query (Base Case)
    SELECT column1, column2 FROM table WHERE condition  
    UNION ALL  
    -- Recursive Query (Recursive Case)
    SELECT column1, column2 FROM table, cte_name WHERE join_condition  
)
SELECT * FROM cte_name;

The recursive query repeatedly executes until no more rows are returned.

Example 1: Employee Hierarchy

Consider an employees table where each employee has a manager_id.

Table Structure

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

Sample Data

Recursive Query to Get Employee Hierarchy

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Get the top-level manager (Alice)
    SELECT id, name, manager_id, 1 AS hierarchy_level  
    FROM employees  
    WHERE manager_id IS NULL  
    UNION ALL  
    -- Recursive Query: Get employees reporting to previous level
    SELECT e.id, e.name, e.manager_id, eh.hierarchy_level + 1  
    FROM employees e  
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id  
)
SELECT * FROM EmployeeHierarchy;

Output

Explanation:

• Alice (Level 1) has no manager.
• Bob and Charlie (Level 2) report to Alice.
• David, Eve, and Frank (Level 3) report to Bob or Charlie.

Example 2: Category-Subcategory Relationship

Consider a categories table for an e-commerce website.

Table Structure

CREATE TABLE categories (
    id INT PRIMARY KEY,
    category_name VARCHAR(100),
    parent_id INT
);

Sample Data

Recursive Query to Fetch Category Tree

WITH RECURSIVE CategoryTree AS (
    -- Anchor Query: Fetch top-level categories
    SELECT id, category_name, parent_id, 1 AS level  
    FROM categories  
    WHERE parent_id IS NULL  
    UNION ALL  
    -- Recursive Query: Get subcategories
    SELECT c.id, c.category_name, c.parent_id, ct.level + 1  
    FROM categories c  
    INNER JOIN CategoryTree ct ON c.parent_id = ct.id  
)
SELECT * FROM CategoryTree ORDER BY level;

Output

Explanation:

• Electronics (Level 1) is the parent category.
• Phones & Laptops (Level 2) are subcategories of Electronics.
• Android, iPhone & Gaming Laptops (Level 3) are further subcategories.

Performance Considerations

Recursive queries can be slow if the hierarchy is too deep. To optimize:

1. Use indexing on id and parent_id columns.
2. Limit recursion depth with OPTION (MAXRECURSION n) (SQL Server).
3. Use JSON/XML storage for deeply nested structures.

Conclusion

• Recursive queries help process hierarchical data efficiently.
• WITH RECURSIVE CTEs allow self-referencing queries in SQL.
• They are widely used for employee hierarchies, category trees, and graph-based relationships.

Understanding and optimizing recursive queries can greatly enhance database performance when handling hierarchical data.