再帰CTEは自分自身を参照するため、1つのクエリで組織図のような任意の深さのツリーをたどれます。
WITH RECURSIVE team AS (
SELECT employee_id, first_name, 1 AS level
FROM employees WHERE employee_id = 2 -- start: the anchor
UNION ALL
SELECT e.employee_id, e.first_name, t.level + 1
FROM employees AS e
JOIN team AS t ON e.manager_id = t.employee_id -- step: one level down
)
SELECT * FROM team;
アンカーは1回だけ実行されます。その後、再帰の部分が何度も実行され、毎回、前の回で生まれた行に対して処理を行い、ある回で何も生まれなくなるまで続きます。どの再帰CTEにも、最終的にそれを止めるものが必要です:ここでは、部下のいない人です。数を増やしていく場合はWHEREで上限を付けてください。そうしないと、データの誤り(ループ)によって永遠に実行され続けます。
PostgreSQL、MySQL 8、SQLite、Snowflake、DuckDBではWITH RECURSIVEと書きます。SQL ServerとOracleでは、RECURSIVEという語なしで同じ形を書けます。