RECURSIVE CTEQuery Techniques
Buy Me A Coffee

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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 RECURSIVE keyword required
PostgreSQL ✓ Supported 8.4 Standard RECURSIVE CTE
SQL Server ✓ Supported 2005 Standard CTE recursion
Oracle ✓ Supported 11g R2 Pre-11gR2 use CONNECT BY
SQLite ✓ Supported 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: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2005 Oracle: Since 11g R2 SQLite: Since 3.8.3

Per-Database Syntax & Notes

MySQL

RECURSIVE keyword required

WITH RECURSIVE cte AS (…) SELECT * FROM cte;

PostgreSQL

Standard RECURSIVE CTE

WITH RECURSIVE cte AS (…) SELECT * FROM cte;

SQL Server

Standard CTE recursion

WITH cte AS (…) SELECT * FROM cte;

Oracle

Pre-11gR2 use CONNECT BY

WITH cte AS (…) SELECT * FROM cte;

SQLite

Supports RECURSIVE CTEs

WITH RECURSIVE cte AS (…) SELECT * FROM cte;