Querying self referencing data
A recursive common table expression lets a single query traverse hierarchies like org charts, category trees, or bill of materials. It repeatedly applies itself until no new rows appear.
Two parts joined by UNION ALL
A recursive CTE has two members combined with UNION ALL:
- The anchor member runs once and seeds the starting rows, such as the root employee with no manager.
- The recursive member references the CTE name and joins it to the base table, producing the next level down.
The engine keeps re executing the recursive member, feeding each new batch back in, until a pass returns zero rows.
Tracking depth and paths
You can carry extra columns to enrich the walk:
- A level counter increments each iteration to record depth.
- A path string concatenates ids so you can detect cycles or print breadcrumbs.
Guarding against runaway loops
Cyclic data can recurse forever. Protect the query by checking the path for repeats, or by capping depth with a level limit. Many engines also expose a maximum recursion setting that stops runaway expansion.
Key idea
A recursive CTE seeds rows with an anchor, then repeatedly joins itself to add deeper levels until no new rows appear, making it the standard tool for walking hierarchical data.