Recursive CTE

Recursive CTEs are the most powerful type of CTE. They can reference themselves, enabling you to traverse hierarchies, generate sequences, and solve problems that would otherwise require procedural code.

In this tutorial, you will learn:

• What makes a CTE recursive
• The structure of recursive CTEs (anchor + recursive member)
• How recursive CTEs execute
• Common use cases and patterns
• Preventing infinite loops

Definition

A recursive CTE is a CTE that references itself in its own definition. This self-reference allows the query to iterate, building results step by step.

Key Difference from Regular CTEs

When You Need Recursion

• Hierarchical data: Organization charts, category trees, folder structures
• Network/graph data: Friend connections, route finding
• Sequences: Generate numbers, dates, series
• Bill of materials: Product components and sub-components
• Path finding: All routes between two points

Recursive CTE Anatomy

WITH RECURSIVE cte_name AS (
    -- ANCHOR MEMBER: Starting point (non-recursive)
    SELECT initial_columns
    FROM base_table
    WHERE starting_condition
    
    UNION ALL
    
    -- RECURSIVE MEMBER: Self-referencing part
    SELECT derived_columns
    FROM cte_name            -- References itself!
    JOIN base_table ON ...   -- Usually joins to get next level
    WHERE termination_condition
)
SELECT * FROM cte_name;

Visual Breakdown

Important Notes

• RECURSIVE keyword: Required in most databases (PostgreSQL, SQLite). SQL Server doesn't require it.
• UNION ALL: Must use UNION ALL between anchor and recursive member
• Termination: Recursive member must eventually return no rows, or query runs forever

Execution Flow

Simple Example: Counting to 5

WITH RECURSIVE counter AS (
    -- Anchor: Start at 1
    SELECT 1 AS n
    
    UNION ALL
    
    -- Recursive: Add 1 each iteration
    SELECT n + 1
    FROM counter
    WHERE n < 5  -- Stop condition
)
SELECT n FROM counter;

Execution trace:

• Iteration 0 (anchor): n=1 → work table: [1]
• Iteration 1: 1+1=2, 2<5 ✓ → work table: [2]
• Iteration 2: 2+1=3, 3<5 ✓ → work table: [3]
• Iteration 3: 3+1=4, 4<5 ✓ → work table: [4]
• Iteration 4: 4+1=5, 5<5 ✗ → work table: [] → STOP

Result: 1, 2, 3, 4, 5

Pattern 1: Number Sequence Generation

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;
-- Produces: 1, 2, 3, ..., 100

Pattern 2: Date Range Generation

WITH RECURSIVE dates AS (
    SELECT DATE('2023-01-01') AS dt
    UNION ALL
    SELECT DATE(dt, '+1 day')
    FROM dates
    WHERE dt < DATE('2023-01-31')
)
SELECT dt FROM dates;
-- Produces all dates in January 2023

Pattern 3: Hierarchy Traversal

-- Employee hierarchy (manager -> reports)
WITH RECURSIVE org_chart AS (
    -- Anchor: Start with CEO (no manager)
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: Find employees who report to current level
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart ORDER BY level;

Pattern 4: Path Building

-- Build path from root to each node
WITH RECURSIVE category_path AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, 
           cp.path || ' > ' || c.name
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path;
-- Result: "Electronics > Computers > Laptops"

The Danger of Infinite Recursion

If the recursive member never stops producing rows, the query runs forever (or until the database stops it).

❌ Infinite loop example:

WITH RECURSIVE infinite AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM infinite  -- No WHERE clause = never stops!
)
SELECT * FROM infinite;

Prevention Strategies

1. WHERE clause with termination condition:

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte
    WHERE n < 100  -- ✅ Stops at 100
)

2. LIMIT in main query:

WITH RECURSIVE cte AS (...)
SELECT * FROM cte
LIMIT 100;  -- ✅ Safety net

3. Track visited nodes (for graphs):

WITH RECURSIVE paths AS (
    SELECT id, name, id AS visited
    FROM nodes WHERE id = 1
    
    UNION ALL
    
    SELECT n.id, n.name, p.visited || ',' || n.id
    FROM nodes n
    JOIN paths p ON n.parent_id = p.id
    WHERE INSTR(p.visited, n.id) = 0  -- ✅ Don't revisit
)

4. Maximum depth limit:

WITH RECURSIVE hierarchy AS (
    SELECT id, name, 0 AS depth
    FROM org WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT o.id, o.name, h.depth + 1
    FROM org o
    JOIN hierarchy h ON o.manager_id = h.id
    WHERE h.depth < 10  -- ✅ Max 10 levels
)

Let's practice writing recursive CTEs. Note: Some examples use simulated data since our e-commerce dataset doesn't have natural hierarchies.

What You Cannot Do in Recursive Member

Most databases restrict what can appear in the recursive part:

Performance Considerations

SQLite-Specific Notes

• SQLite uses WITH RECURSIVE syntax
• Default recursion limit is 1000 iterations
• Can be changed with PRAGMA recursive_triggers

Key Takeaways

1. Structure:

   • WITH RECURSIVE cte AS (...)
   • Anchor member (starting point)
   • UNION ALL
   • Recursive member (self-referencing)

2. Execution:

   • Anchor executes once
   • Recursive member executes repeatedly
   • Stops when recursive member returns no rows

3. Use Cases:

   • Number/date sequences
   • Hierarchical data traversal
   • Path building
   • Graph navigation

4. Safety:

   • Always have a termination condition
   • Use LIMIT as a safety net
   • Track visited nodes for graphs

Quick Reference

WITH RECURSIVE cte_name AS (
    -- Anchor: Starting rows
    SELECT columns FROM table WHERE starting_condition
    
    UNION ALL
    
    -- Recursive: Build on previous iteration
    SELECT columns
    FROM table
    JOIN cte_name ON relationship
    WHERE termination_condition
)
SELECT * FROM cte_name;

What's Next?

Finally, we'll wrap up with the CTE Summary – a comprehensive review of everything you've learned about Common Table Expressions.