Recursive CTEs: Introduction
You'll learn: How recursive CTEs repeatedly reference themselves to traverse hierarchical or tree-like data structures.
What Makes a CTE Recursive?
Until now, every CTE you've written has been "non-recursive"—it defines a result set once and you're done. But what if your data naturally forms a hierarchy, like an employee reporting chain or a folder tree? A recursive CTE solves this by defining a query that calls itself, building results layer by layer until there's nothing left to add.
Think of it like climbing a family tree: you start with one person (the "anchor"), then find their parents (first recursion), then their parents (second recursion), and so on until you reach the top.
Two Essential Parts
Every recursive CTE has exactly two components:
- Anchor Member: The starting point—your initial row(s) with no dependencies. Example: the CEO who reports to no one.
- Recursive Member: The self-referencing query that adds the next "layer" by joining back to what you've already found. Example: find employees whose manager is someone already in your result set.
These two parts are connected with UNION or UNION ALL. The database alternates: run the anchor once, then run the recursive part repeatedly until it returns no new rows.
Basic Structure (Concept Only)
WITH RECURSIVE hierarchy_name AS (
-- Anchor: starting point
SELECT ...
UNION ALL
-- Recursive: reference hierarchy_name itself
SELECT ...
FROM hierarchy_name
JOIN ...
)
SELECT * FROM hierarchy_name;
You're not writing runnable code yet—just understand that the CTE's name appears inside its own definition.
Key Takeaway: Recursive CTEs use an anchor member (starting rows) and a recursive member (self-referencing query) to traverse hierarchical data step-by-step until complete.