Recursive CTEs in SQL: Hierarchies & Sequences Made Simple

Some SQL problems can't be solved with a single JOIN or GROUP BY. How do you find every employee under a manager, no matter how many levels deep? How do you generate a row for every day in March even when some days had zero sales? The answer is the recursive CTE - a query that calls itself until a condition stops it. It sounds scary, but the pattern is simple once you see it.

The Anchor + Recursive Pattern

Every recursive CTE has exactly two parts joined by UNION ALL:

1. Anchor member - the starting point. It runs once.
2. Recursive member - references the CTE itself, running repeatedly. Each pass uses the previous pass's output as input.

The recursion stops when the recursive member returns no new rows.

WITH RECURSIVE numbers AS (
    -- Anchor: start at 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: add 1 until we hit 10
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT n FROM numbers;

This generates 1 through 10. The anchor produces 1. The recursive member keeps adding 1 while n < 10. When n reaches 10, the WHERE filters everything out and recursion stops.

Note: PostgreSQL, MySQL 8+, and SQLite require the RECURSIVE keyword. SQL Server uses WITH without it.

Generating a Date Spine

A date spine is a continuous list of dates - essential for reports where some days have no activity but you still need a row (so charts don't skip days). Suppose you want every date in June 2026 for a Flipkart sales dashboard.

WITH RECURSIVE date_spine AS (
    SELECT DATE '2026-06-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day'
    FROM date_spine
    WHERE day < DATE '2026-06-30'
)
SELECT day FROM date_spine;

Now LEFT JOIN your orders onto this spine so days with zero orders still appear:

WITH RECURSIVE date_spine AS (
    SELECT DATE '2026-06-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day'
    FROM date_spine
    WHERE day < DATE '2026-06-30'
)
SELECT
    d.day,
    COALESCE(SUM(o.order_amount), 0) AS daily_revenue
FROM date_spine d
LEFT JOIN flipkart_orders o
       ON o.order_date = d.day
GROUP BY d.day
ORDER BY d.day;

Days with no sales now show ₹0 instead of disappearing - exactly what a clean trend line needs.

Walking an Org Chart

The classic use case: an employees table where each row stores its own manager_id. To list everyone reporting (directly or indirectly) under one manager, you recurse down the tree.

WITH RECURSIVE org_chart AS (
    -- Anchor: the top manager
    SELECT
        emp_id,
        emp_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE emp_name = 'Priya Sharma'

    UNION ALL

    -- Recursive: find direct reports of anyone already in the tree
    SELECT
        e.emp_id,
        e.emp_name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    JOIN org_chart oc
      ON e.manager_id = oc.emp_id
)
SELECT emp_id, emp_name, level
FROM org_chart
ORDER BY level, emp_name;

The anchor grabs Priya. The recursive member finds everyone whose manager_id matches someone already in the result, then their reports, and so on. The level column tracks depth - 1 for Priya, 2 for her direct reports, 3 for the next layer.

Exploding a Category Tree

E-commerce categories nest deeply: Electronics → Mobiles → Smartphones → Android. The same downward-walk pattern lists every sub-category under a root.

WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_id
    FROM categories
    WHERE category_name = 'Electronics'

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_id
    FROM categories c
    JOIN category_tree ct
      ON c.parent_id = ct.category_id
)
SELECT category_name FROM category_tree;

Preventing Infinite Loops

Recursion that never ends will hang your database or hit a recursion-depth error. Guard against it:

• Always have a stopping condition. For sequences, a WHERE n < limit. For hierarchies, recursion ends naturally when no more children exist - unless your data has a cycle.
• Watch for cyclic data. If employee A reports to B and B somehow reports to A (bad data), the query loops forever. Track visited IDs or use a level cap: add WHERE oc.level < 50 to the recursive member.
• Use database limits. PostgreSQL doesn't cap recursion by default - set a guard yourself. SQL Server defaults to MAXRECURSION 100; override with OPTION (MAXRECURSION 1000) when you genuinely need more.

-- Safety cap to prevent runaway recursion
...
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
    WHERE oc.level < 50
...

Common Pitfalls

• Forgetting UNION ALL. Using UNION (without ALL) de-duplicates on every pass, which is slower and can mask logic errors. Use UNION ALL unless you specifically need de-duplication.
• Wrong join direction. To walk down a tree, join children to parents already in the CTE. To walk up, reverse it.
• Selecting * in the recursive part. Column lists in the anchor and recursive members must match exactly in count and type.

Best Practices

• Add a level (or depth) column - it's invaluable for debugging and for indenting output.
• Keep the anchor narrow (one starting row when possible) so recursion stays fast.
• For very large hierarchies, test performance; recursive CTEs can be expensive and a materialized path column may be faster.
• Comment the anchor and recursive members so the next analyst understands the direction of travel.

Wrapping Up

Recursive CTEs handle the problems flat SQL can't: hierarchies of unknown depth and continuous sequences like date spines. Remember the shape - anchor, UNION ALL, recursive member referencing itself, and a stopping condition. Once the pattern clicks, org charts, category trees, and gap-free calendars become routine.

Related: SQL + Power BI Challenge for Data Analysts · Practise SQL problems

Don't just read. Prove your skill on DevWithData.