Recursive Queries in SQL
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 ALLoperator. - 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
MAXRECURSIONsafeguards. - 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:
- The Anchor Member: An initial non-recursive query that finds the base or root records (e.g. the CEO who has no manager).
UNION ALL(orUNION): Merges the anchor records with the recursive iterations.- 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.
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:
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
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)
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
FROMorJOINclause.
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:
- 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.
- Cycle Trapping: Without cycle protection, circular graphs cause infinite loops. Candidates should explain tracking an array of visited IDs (
ARRAY[id]), using the SQLCYCLEclause (Postgres 14+), or imposing a recursion depth limit (level < 50orOPTION (MAXRECURSION)).
Interactive Practice Challenge
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.