SQL RECURSIVE

What does SQL RECURSIVE do?

RECURSIVE allows a Common Table Expression (CTE) to repeatedly reference itself to traverse hierarchies or generate sequences.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RECURSIVE: PostgreSQL, MySQL 8.0+, SQL Server (as recursive CTE), Oracle 11g+, SQLite 3.8.3+, DuckDB, Redshift (with LIMITations), MariaDB 10.2+

SQL RECURSIVE Full Explanation

RECURSIVE is used inside a WITH clause to declare a recursive CTE. A recursive CTE has two parts: an anchor query that returns the base result set and a recursive query that references the CTE itself. The database repeatedly executes the recursive query, adding rows to the interim result until no new rows are produced, then returns the combined set. Depth-first or breadth-first evaluation order is implementation-specific, and most engines automatically prevent infinite loops by tracking previously returned rows. Performance depends on proper indexing and termination conditions. In ANSI SQL:1999, RECURSIVE became part of the standard; today it is widely supported but syntax details vary slightly by vendor.

SQL RECURSIVE Syntax

WITH RECURSIVE cte_name (column_list) AS (
  -- Anchor member
  SELECT ...
  UNION [ALL]
  -- Recursive member
  SELECT ...
  FROM cte_name
  WHERE termination_condition
)
SELECT * FROM cte_name;

SQL RECURSIVE Parameters

Example Queries Using SQL RECURSIVE

-- 1. Employee hierarchy (PostgreSQL)
WITH RECURSIVE emp_cte AS (
  SELECT id, manager_id, name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL  -- top level
  UNION ALL
  SELECT e.id, e.manager_id, e.name, ec.level + 1
  FROM employees e
  JOIN emp_cte ec ON e.manager_id = ec.id
)
SELECT * FROM emp_cte ORDER BY level, id;

-- 2. Generate a numbers table 1..10 (SQLite)
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

Expected Output Using SQL RECURSIVE

Query 1 returns every employee with their hierarchy level
Query 2 returns a single-column result set containing numbers 1 through 10

Use Cases with SQL RECURSIVE

Expanding parent-child hierarchies (org charts, folder trees)
Graph traversals such as bill of materials or social networks
Generating series of dates or integers without a numbers table
Calculating running aggregates that need prior rows
Solving problems like factorial, Fibonacci, or pathfinding inside SQL

Common Mistakes with SQL RECURSIVE

Forgetting the termination condition, causing infinite recursion
Omitting UNION ALL when duplicates are required or using UNION when performance matters
Selecting from the CTE outside the WITH block without qualifying name conflicts
Assuming ordering is preserved; ORDER BY must be in the final SELECT
Expecting MySQL versions prior to 8.0 to support RECURSIVE

First Introduced In

ANSI SQL:1999 (recursive query), first implemented in PostgreSQL 8.4

Frequently Asked Questions

What problems does SQL RECURSIVE solve?

It simplifies hierarchical queries, sequence generation, and iterative calculations that would otherwise need procedural code.

Is RECURSIVE part of standard SQL?

Yes. Recursive CTEs were added in ANSI SQL:1999 and are now broadly supported.

Can I control recursion depth?

Most databases let you add a WHERE clause on a level column or set engine-specific limits (e.g., SET max_recursion_depth in SQL Server).

What performance tips should I know?

Index the join columns, use UNION ALL, and return only needed columns to minimize work during each recursion step.