Self Referencing Queries
A recursive CTE is a CTE that references itself. It lets plain SQL traverse hierarchies and graphs that would otherwise need a loop in application code. You declare it with WITH RECURSIVE in most engines.
The Two Parts
A recursive CTE has two queries joined by UNION ALL:
- The anchor runs once and produces the starting rows.
- The recursive member references the CTE and produces the next level from the previous one.
- The engine repeats the recursive member until it returns no new rows.
A Hierarchy Example
To list everyone under a manager, the anchor selects the manager, and the recursive member joins the CTE to employees whose manager id matches a row already found. Each pass adds one level deeper.
Guard Against Runaways
Cycles in the data can make recursion never stop. Protect yourself by tracking a depth counter or a visited path and stopping when it grows too large. Many engines also offer a recursion limit setting.
Key idea
A recursive CTE pairs an anchor with a self referencing member joined by UNION ALL to walk hierarchies level by level until no new rows appear.