Recursive CTEs

There are 3 parts to a recursive CTE:

1. The anchor member: An initial query that returns the base result of the CTE.
2. The recursive member: A recursive query that references the CTE. This is UNION ALL’ed with the anchor member.
3. A termination condition that stops the recursive member.

Here is an example of a recursive CTE that gets the manager id for each staff id:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

with org_structure AS (

SELECT id
  ,manager_id
FROM staff_members
WHERE manager_id IS NULL
)
UNION ALL

SELECT sm.id
  ,sm.manager_id
FROM staff_members AS sm
INNER JOIN org_structure AS os
  ON os.id = sm.manager_id