Recursive Queries - Basic Examples

Example 1: Employee Hierarchy

Let's start with a classic organizational chart scenario. We have employees who report to managers, and managers who report to other managers.

Sample Table Structure

CREATE TABLE employees 
(
    employee_id BIGINT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    manager_id BIGINT,
    department VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Essential index for recursive performance
    INDEX idx_employees_manager_id (manager_id),

    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
) COMMENT = 'Employee hierarchy with self-referencing manager relationship';

Sample Data

INSERT INTO employees (employee_id, first_name, last_name, manager_id, department) VALUES
(1, 'Sarah', 'Johnson', NULL, 'Executive'),           -- CEO
(2, 'Mike', 'Chen', 1, 'Engineering'),                -- CTO
(3, 'Lisa', 'Williams', 1, 'Sales'),                  -- Sales Director
(4, 'David', 'Brown', 2, 'Engineering'),              -- Senior Developer
(5, 'Emma', 'Davis', 2, 'Engineering'),               -- Senior Developer  
(6, 'John', 'Miller', 3, 'Sales'),                    -- Sales Manager
(7, 'Amy', 'Wilson', 4, 'Engineering'),               -- Junior Developer
(8, 'Tom', 'Garcia', 6, 'Sales');                     -- Sales Rep

Basic Recursive Query - Find All Subordinates

This query finds all employees who report to a specific manager, directly or indirectly:

WITH RECURSIVE employee_hierarchy AS 
(
    -- Anchor: Start with the target manager
    SELECT 
        employee_id
        , first_name
        , last_name
        , manager_id
        , department
        , 0 AS level
        , CAST(first_name AS VARCHAR(500)) AS hierarchy_path
    FROM employees
    WHERE employee_id = 2  -- Mike Chen (CTO)

    UNION ALL

    -- Recursive step: Find direct reports of current level
    SELECT 
        e.employee_id
        , e.first_name
        , e.last_name
        , e.manager_id
        , e.department
        , eh.level + 1
        , CONCAT(eh.hierarchy_path, ' -> ', e.first_name) AS hierarchy_path
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    employee_id
    , first_name
    , last_name
    , department
    , level
    , hierarchy_path
FROM employee_hierarchy
ORDER BY level, last_name;

Basic Recursive Query - Find Management Chain

This query finds the complete management chain for a specific employee:

WITH RECURSIVE management_chain AS 
(
    -- Anchor: Start with the target employee
    SELECT 
        employee_id
        , first_name
        , last_name
        , manager_id
        , department
        , 0 AS level_up
    FROM employees
    WHERE employee_id = 7  -- Amy Wilson

    UNION ALL

    -- Recursive step: Find manager of current person
    SELECT 
        e.employee_id
        , e.first_name
        , e.last_name
        , e.manager_id
        , e.department
        , mc.level_up + 1
    FROM employees e
    INNER JOIN management_chain mc ON e.employee_id = mc.manager_id
)
SELECT 
    employee_id
    , first_name
    , last_name
    , department
    , level_up
    , CASE 
        WHEN level_up = 0 THEN 'Self'
        WHEN level_up = 1 THEN 'Direct Manager'
        ELSE CONCAT('Manager Level ', level_up)
    END AS relationship
FROM management_chain
ORDER BY level_up;

Example 2: Category Tree

E-commerce sites often have nested categories. Let's see how to work with them recursively.

Sample Table Structure

CREATE TABLE categories 
(
    category_id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_category_id BIGINT,
    slug VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Critical for recursive performance
    INDEX idx_categories_parent_id (parent_category_id),
    INDEX idx_categories_active_parent (is_active, parent_category_id),

    UNIQUE KEY uk_categories_slug (slug),
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
) COMMENT = 'Hierarchical product category structure';

Sample Data

INSERT INTO categories (category_id, name, parent_category_id, slug, sort_order) VALUES
(1, 'Electronics', NULL, 'electronics', 1),
(2, 'Clothing', NULL, 'clothing', 2),
(3, 'Computers', 1, 'computers', 1),
(4, 'Phones', 1, 'phones', 2),
(5, 'Laptops', 3, 'laptops', 1),
(6, 'Desktops', 3, 'desktops', 2),
(7, 'Gaming Laptops', 5, 'gaming-laptops', 1),
(8, 'Business Laptops', 5, 'business-laptops', 2),
(9, 'Mens Clothing', 2, 'mens-clothing', 1),
(10, 'Womens Clothing', 2, 'womens-clothing', 2);

Basic Recursive Query - Get Category Tree

This query builds a complete category tree starting from root categories:

WITH RECURSIVE category_tree AS 
(
    -- Anchor: Start with root categories (no parent)
    SELECT 
        category_id
        , name
        , parent_category_id
        , slug
        , sort_order
        , 0 AS depth
        , name AS full_path
        , LPAD('', 0, '  ') AS indent
    FROM categories
    WHERE 
        parent_category_id IS NULL
        AND is_active = TRUE

    UNION ALL

    -- Recursive step: Get child categories
    SELECT 
        c.category_id
        , c.name
        , c.parent_category_id
        , c.slug
        , c.sort_order
        , ct.depth + 1
        , CONCAT(ct.full_path, ' > ', c.name) AS full_path
        , LPAD('', (ct.depth + 1) * 2, '  ') AS indent
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_category_id = ct.category_id
    WHERE c.is_active = TRUE
)
SELECT 
    category_id
    , CONCAT(indent, name) AS indented_name
    , full_path
    , depth
    , sort_order
FROM category_tree
ORDER BY full_path, sort_order;

Basic Recursive Query - Find All Subcategories

This query finds all subcategories under a specific parent category:

WITH RECURSIVE subcategories AS 
(
    -- Anchor: Start with the target category
    SELECT 
        category_id
        , name
        , parent_category_id
        , slug
        , 0 AS level_down
    FROM categories
    WHERE 
        category_id = 1  -- Electronics
        AND is_active = TRUE

    UNION ALL

    -- Recursive step: Find child categories
    SELECT 
        c.category_id
        , c.name
        , c.parent_category_id
        , c.slug
        , sc.level_down + 1
    FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.category_id
    WHERE c.is_active = TRUE
)
SELECT 
    category_id
    , name
    , slug
    , level_down
FROM subcategories
WHERE level_down > 0  -- Exclude the starting category itself
ORDER BY level_down, name;

Example 3: Simple Number Series

Sometimes recursive queries are useful for generating data series, like sequences of numbers or dates.

Generate Number Series

WITH RECURSIVE number_series AS 
(
    -- Anchor: Start with 1
    SELECT 1 AS n

    UNION ALL

    -- Recursive step: Add 1 until we reach 10
    SELECT n + 1
    FROM number_series
    WHERE n < 10
)
SELECT n
FROM number_series;

Generate Date Series

This is useful for filling gaps in reporting data:

WITH RECURSIVE date_series AS 
(
    -- Anchor: Start date
    SELECT DATE('2024-01-01') AS report_date

    UNION ALL

    -- Recursive step: Add one day until end date
    SELECT DATE_ADD(report_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE report_date < DATE('2024-01-31')
)
SELECT 
    report_date
    , DAYNAME(report_date) AS day_name
    , WEEK(report_date) AS week_number
FROM date_series
ORDER BY report_date;

Key Performance Tips for Basic Recursive Queries

Always Index the Recursive Column: The column used for the recursive relationship (manager_id, parent_category_id) must be indexed.
Filter Early: Add WHERE conditions in both the anchor and recursive parts to limit the data processed.
Limit Recursion Depth: Most databases allow setting max_recursive_iterations to prevent runaway queries.
Use Appropriate Data Types: Don't use TEXT for path building if VARCHAR is sufficient.
Consider Adding Level Tracking: Including a level/depth column helps with ordering and can be useful for limiting traversal depth.

These basic examples demonstrate the fundamental patterns you'll use in most recursive scenarios. The key is starting simple and building complexity gradually as you become comfortable with the recursive CTE syntax.