Recursive CTEs in MySQL: Hierarchies and Sequences

Recursive CTEs let a query reference itself, which unlocks problems ordinary SQL struggles with: walking an org chart of unknown depth, expanding category trees, or generating a series of dates from nothing.

Key Points

• Structure: anchor member UNION ALL recursive member inside WITH RECURSIVE
• Traverse hierarchies: start at the CEO, repeatedly join employees to managers
• Generate sequences: numbers 1–100 or every date in a year without a table
• Track depth with a level column incremented each iteration
• cte_max_recursion_depth (default 1000) prevents infinite loops
• Essential for building date scaffolds in time-series analysis