A CTE that references itself for hierarchies or graph traversals.
A self-referencing CTE that iteratively generates rows, used for traversing hierarchies (org charts, file systems) and graphs. The recursive part unions with the base case until convergence. Always include a termination condition.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0 | RECURSIVE keyword required |
| PostgreSQL | Native | 8.4 | Standard RECURSIVE CTE |
| SQL Server | Native | 2005 | Standard CTE recursion |
| Oracle | Native | 11g R2 | Pre-11gR2 use CONNECT BY |
| SQLite | Native | 3.8.3 | Supports RECURSIVE CTEs |
Details
Great for org charts, file trees, and any hierarchical data.
Standard Syntax
WITH RECURSIVE cte AS (
SELECT id, parent_id FROM tbl WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id
FROM tbl t
JOIN cte ON cte.id = t.parent_id
)
SELECT * FROM cte;
Version Support
MySQL: Native since 8.0
PostgreSQL: Native since 8.4
SQL Server: Native since 2005
Oracle: Native since 11g R2
SQLite: Native since 3.8.3
Per-Database Syntax & Notes
MySQL Native syntax
RECURSIVE keyword required
WITH RECURSIVE cte AS (…)
SELECT * FROM cte;
PostgreSQL Native syntax
Standard RECURSIVE CTE
WITH RECURSIVE cte AS (…)
SELECT * FROM cte;
SQL Server Native syntax
Standard CTE recursion
WITH cte AS (…)
SELECT * FROM cte;
Oracle Native syntax
Pre-11gR2 use CONNECT BY
WITH cte AS (…)
SELECT * FROM cte;
SQLite Native syntax
Supports RECURSIVE CTEs
WITH RECURSIVE cte AS (…)
SELECT * FROM cte;