How does a recursive CTE work, and what are the anchor and the recursive part?
A recursive common table expression walks a hierarchy. It has two parts, and the engine runs the first one and then keeps running the second one until there is nothing left to do.
CREATE TABLE staff (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
manager_id INTEGER
);
-- The anchor part: the starting rows.
-- The recursive part: the rows that come from the rows we already have.
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 0 AS depth
FROM staff
WHERE manager_id IS NULL
UNION ALL
SELECT s.id, s.name, s.manager_id, c.depth + 1
FROM staff s
JOIN chain c ON s.manager_id = c.id
)
SELECT id, name, depth FROM chain ORDER BY depth, name;
So how does it work? The anchor part runs first, and it produces the starting rows. In this example those are the people with no manager, so the top of the tree. Then the engine takes those rows and runs the recursive part on them, which finds the people who report to them. Then it runs the recursive part again on the new rows, and again, until the recursive part produces no rows at all.
There are two parts to the second half of the definition. The word UNION ALL joins the anchor rows to the recursive rows. The word UNION does the same thing but removes the duplicate rows, and it is slower because the engine has to check for them.
So the rule to remember is this. The anchor produces the first rows, the recursive part produces the next ones from the ones before, and the engine keeps going until the recursive part gives back nothing.
The next two questions in this topic put it to work and look at the stopping condition.