Recursive CTEs

Recursive CTEs — Master recursive Common Table Expressions for tree traversal, graph queries, and series generation. The guide walks through Recursive CTE Structure, Employee Org Chart, Date Series Generation, Tree & Graph Traversal, Fibonacci & Factorial Sequences, Performance Considerations. A recursive CTE has two parts: the anchor member (non-recursive initial query) and the recursive member (references the CTE itself), separated by UNION ALL. Syntax: WITH RECURSIVE cte AS (SELECT ... /* anchor */ UNION ALL SELECT ... FROM cte WHERE ... /* recursive */) SELECT * FROM cte. The recursion stops when the recursive step returns zero rows. The classic recursive CTE example is traversing an employee-manager hierarchy. Anchor: the top-level manager (WHERE manager_id IS NULL). Recursive member: join employees e WITH cte ON e.manager_id = cte.id. Add a level counter and a path string to visualize the tree: CONCAT(cte.path, ' > ', e.name). Depth-first or breadth-first ordering controls traversal order.