Recursive CTE concept

Question

Accepted Answer

A recursive CTE has two parts joined by UNION ALL: the anchor member (base case, runs once) and the recursive member (references the CTE itself, runs repeatedly until no new rows are produced). The classic use cases are: walking an employee-manager hierarchy to find all reports at any depth, traversing a tree of categories, or generating a sequence of numbers. Most databases (PostgreSQL, SQL Server, SQLite) support recursive CTEs; MySQL added support in version 8.0.