Recursive CTEs - Can My DB?

Database	Status	Syntax Family	Min Version
PostgreSQL	full	sql-standard	-
MySQL	full	sql-standard	8.0
MariaDB	full	sql-standard	10.2.2
Oracle	full	sql-standard+connect-by	11.2
SQL Server	full	sql-standard	2005
SQLite	full	sql-standard	3.8.3
Redshift	full	sql-standard	-
DB2	full	sql-standard	-
Snowflake	full	sql-standard	-
BigQuery	full	sql-standard	-

Compare Databases

Database Details

PostgreSQL full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

14 adds CYCLE (explicit cycle detection) and SEARCH BREADTH/DEPTH FIRST ordering clauses.

Docs: WITH Queries (Common Table Expressions)

MySQL full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Added in 8.0; not available in 5.7 or earlier.
cte_max_recursion_depth (default 1000) caps iteration count.

Docs: WITH (Common Table Expressions)

MariaDB full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Added in 10.2.2. max_recursive_iterations caps iteration count.

Docs: Recursive Common Table Expressions Overview

Oracle full

WITH org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

SQL:1999 recursive subquery factoring arrived in 11g R2. SEARCH and CYCLE clauses are supported.
CONNECT BY remains available and is still common in legacy code.

Docs: Hierarchical Queries (CONNECT BY), Subquery Factoring (WITH clause)

SQL Server full

WITH org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Supported since 2005.
Default recursion depth is 100; change with OPTION (MAXRECURSION n), or 0 for unlimited.
The statement immediately before a CTE must end in a semicolon.

Docs: WITH common_table_expression (Transact-SQL)

SQLite full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Added in 3.8.3 (2014-02-03). SQLite also accepts RECURSIVE on non-recursive CTEs, where it is a no-op.

Docs: WITH Clause

Redshift full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Added April 2021. max_recursion_rows session parameter caps iteration count.

Docs: WITH Clause (Redshift)

DB2 full

WITH org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Shipped in DB2 v8. A recursive CTE must declare its column list up front.

Docs: Common Table Expressions (IBM Db2)

Snowflake full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Docs: WITH Clause (Snowflake)

BigQuery full

WITH RECURSIVE org_chart(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chart ORDER BY depth, id;

Added in 2022. Depth is capped to guard against runaway queries.

Docs: WITH Clause (BigQuery), Recursive CTEs in BigQuery