advanced

Recursive Queries in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-209 min readRuntime Verified

Overview

A Recursive Common Table Expression (CTE) is a specialized SQL query that references itself in an iterative loop. In relational databases, recursive queries are the standard solution for querying hierarchical and graph-structured data—such as organizational management charts, bill of materials (part assemblies), nested comment threads, category taxonomies, and flight network pathfinding.

What You Will Learn

In this lesson, you will master:
  • Anatomy of a Recursive CTE: The Anchor Member, the Recursive Member, and the UNION ALL operator.
  • Execution Mechanics: How the database engine maintains working and intermediate queue tables during recursion.
  • Hierarchical Depth & Path Tracking: Calculating level depth and concatenating breadcrumb path strings.
  • Infinite Loop Prevention: Implementing cycle detection and MAXRECURSION safeguards.
  • Graph Traversal: Finding shortest paths and multi-hop relationships in network graphs.

The Three Components of a Recursive CTE

Every recursive CTE follows a mandatory three-part structure:

  1. The Anchor Member: An initial non-recursive query that finds the base or root records (e.g. the CEO who has no manager).
  2. UNION ALL (or UNION): Merges the anchor records with the recursive iterations.
  3. The Recursive Member: A query that references the CTE itself, joining the previous step's output to discover the next generation of child records. The recursion halts automatically when an iteration returns 0 rows.
sql
WITH RECURSIVE cte_name (col1, col2, level) AS (
    -- 1. Anchor Member (Base cases)
    SELECT id, name, 1 AS level
    FROM root_table
    WHERE parent_id IS NULL

    UNION ALL

    -- 2. Recursive Member (Iterative joins)
    SELECT child.id, child.name, parent.level + 1
    FROM child_table child
    INNER JOIN cte_name parent ON child.parent_id = parent.id
)
SELECT * FROM cte_name;
Dialect Syntax Note

PostgreSQL, SQLite, MySQL 8+, Snowflake, and Oracle require the WITH RECURSIVE keyword. Microsoft SQL Server requires only WITH without the RECURSIVE modifier.


Practical Application: Employee Management Hierarchy

Consider an employees table where each employee reports to a manager_id:

sql
WITH RECURSIVE org_chart AS (
    -- Step 1: Anchor - Find top-level executives (No manager)
    SELECT 
        employee_id,
        first_name || ' ' || last_name AS full_name,
        manager_id,
        1 AS org_level,
        CAST(first_name || ' ' || last_name AS VARCHAR(1000)) AS reporting_path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Step 2: Recursive Step - Find employees reporting to the previous tier
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name,
        e.manager_id,
        o.org_level + 1,
        CAST(o.reporting_path || ' -> ' || e.first_name || ' ' || e.last_name AS VARCHAR(1000))
    FROM employees e
    INNER JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT 
    employee_id,
    full_name,
    org_level,
    reporting_path
FROM org_chart
ORDER BY reporting_path;

Cycle Detection & Preventing Infinite Recursion

If dirty data creates a circular reference in the table (e.g. Employee A reports to Employee B, and Employee B reports to Employee A), an unprotected recursive query will loop infinitely until server memory exhausts!

1. PostgreSQL 14+ Native CYCLE Clause

sql
WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id 
    FROM employees e JOIN org_chart o ON e.manager_id = o.employee_id
)
CYCLE employee_id SET is_cycle USING path_trace
SELECT * FROM org_chart WHERE NOT is_cycle;

2. Manual Array Path Tracking (Universal Method)

sql
WITH RECURSIVE org_chart AS (
    SELECT 
        employee_id, 
        manager_id, 
        ARRAY[employee_id] AS visited_ids, 
        FALSE AS is_cycle
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    SELECT 
        e.employee_id, 
        e.manager_id, 
        o.visited_ids || e.employee_id,
        e.employee_id = ANY(o.visited_ids)  -- True if already visited!
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.employee_id
    WHERE NOT o.is_cycle AND NOT (e.employee_id = ANY(o.visited_ids))
)
SELECT * FROM org_chart;

3. SQL Server MAXRECURSION

In SQL Server, protect queries using the query hint OPTION (MAXRECURSION 100).


Common Mistakes & Gotchas

  • Aggregation Inside Recursive Member: You cannot use GROUP BY, HAVING, DISTINCT, or Window Functions directly inside the recursive member query. If aggregation is required, place it in an outer query referencing the completed recursive CTE.
  • Multiple References to CTE in Recursive Member: In most standard relational engines, the recursive member can reference the CTE at most once in the FROM or JOIN clause.

Interview Perspective

SQL Interview Question: How does a Recursive CTE execute under the hood, and how do you protect against circular reference loops?

Key Talking Points for Candidates:

  1. Working Table Mechanics: The database engine creates a working table $R_0$ containing the anchor member results. In each iteration $i$, it joins $R_i$ with the base tables to produce $R_$, repeating until $R_$ is empty. Finally, it returns the union of all $R$ sets.
  2. Cycle Trapping: Without cycle protection, circular graphs cause infinite loops. Candidates should explain tracking an array of visited IDs (ARRAY[id]), using the SQL CYCLE clause (Postgres 14+), or imposing a recursion depth limit (level < 50 or OPTION (MAXRECURSION)).

Interactive Practice Challenge

Challenge: Nested Category Breadcrumbs

You have a categories table with category_id, category_name, and parent_category_id. Write a recursive query that lists all categories starting from root categories (parent_category_id IS NULL), including a calculated depth integer and a breadcrumb string category_path formatted as 'Electronics > Computers > Laptops'.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.