Querying Hierarchies
Some data is naturally tree shaped, like an employee who reports to a manager who reports to another manager. A recursive common table expression lets a single query walk down or up such chains without a loop in application code.
The Two Parts
A recursive CTE has two halves joined by UNION ALL.
- The anchor is the starting set, such as the top manager.
- The recursive member references the CTE itself to fetch the next level, like the direct reports of the rows found so far.
The engine runs the anchor, then repeatedly runs the recursive part on the newest rows until it returns nothing.
Staying Safe
- Without a stopping condition a cyclic graph can recurse forever.
- Track visited nodes or cap depth to avoid runaway queries.
- Many engines enforce a recursion limit that you can raise carefully.
Key idea
A recursive CTE joins an anchor query to a self referencing member with UNION ALL, letting one statement traverse trees and graphs level by level.