CONCEPT
Recursive CTE¶
Definition¶
A recursive CTE (recursive common table expression) is the SQL WITH RECURSIVE name AS (base_query UNION [ALL] recursive_reference) construct that lets a query reference its own partial output, enabling hierarchical queries and graph traversals (org charts, comment threads, dependency graphs, transitive closures) in a single SQL statement without procedural loops. The recursion terminates when the recursive reference produces no new rows. Support is engine-specific and was historically the last major SQL-standard feature to land in MySQL; in sharded engines like Vitess it requires coordination between the query planner and the shard-local executors.
Seen in¶
- — Vitess 21 introduces experimental recursive CTE support. "This feature enhances query flexibility, particularly for managing parent-child relationships like organizational structures or tree-like data." First canonical wiki disclosure of recursive CTEs landing in the vtgate evalengine SQL surface.