Common Table Expressions

Database	Status	Syntax Family	Min Version
PostgreSQL	full	sql-standard	8.4
MySQL	full	sql-standard	8.0
MariaDB	full	sql-standard	10.2.1
Oracle	full	sql-standard	9i R2
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 subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Through 11, every CTE was an optimization fence (always materialized). 12 inlines non-recursive CTEs by default and exposes MATERIALIZED / NOT MATERIALIZED hints.
14 adds CYCLE and SEARCH clauses for recursive CTEs.

Docs: WITH Queries (Common Table Expressions)

MySQL full

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Added in 8.0; not available in 5.7 or earlier.
cte_max_recursion_depth (default 1000) caps recursive CTE iterations.

Docs: WITH (Common Table Expressions)

MariaDB full

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Non-recursive since 10.2.1, recursive since 10.2.2.
max_recursive_iterations caps recursive CTE iterations.

Docs: Common Table Expressions, Recursive Common Table Expressions

Oracle full

WITH subordinates (employee_id, manager_id, name) AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Oracle calls this subquery factoring. Available since 9i R2; recursive since 11g R2.
Oracle also keeps the older CONNECT BY hierarchical-query syntax as a proprietary alternative.
MATERIALIZE and INLINE optimizer hints control materialization explicitly.

Docs: Subquery Factoring (WITH clause)

SQL Server full

WITH subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

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

Docs: WITH common_table_expression

SQLite full

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Available since SQLite 3.8.3 (2014-02-03).
SQLite accepts RECURSIVE on non-recursive CTEs too; it is a no-op there.

Docs: WITH Clause

Redshift full

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees) SELECT * FROM ranked WHERE rn = 1;

Recursive CTEs (WITH RECURSIVE) added April 2021.
CTEs may head SELECT, INSERT, UPDATE, and DELETE.

Docs: WITH Clause

DB2 full

WITH subordinates (employee_id, manager_id, name) AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

DB2 detects recursion implicitly; no RECURSIVE keyword.

Docs: Common Table Expressions

Snowflake full

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Docs: WITH Clause

BigQuery full

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

Docs: WITH Clause