再帰 CTE(WITH RECURSIVE)

Oracle

-- Hierarchy with CONNECT BY:
SELECT id, name, manager_id, LEVEL
FROM employee
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- Alternative (11gR2+): standard recursive WITH (the RECURSIVE keyword is not written)
WITH tree (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree;

階層(ツリー)データの走査に使用する: 組織図、部品表など。Oracle の従来の方法は CONNECT BY / PRIOR と LEVEL 疑似列で、START WITH がルート行を設定する。11gR2 以降は標準の再帰 CTE もサポートされる。

PostgreSQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

WITH RECURSIVE はアンカークエリと再帰部分を UNION ALL で結合する。再帰部分は結果が空になるまで繰り返し実行される。階層とグラフの走査に最適。無限ループは UNION(ALL ではなく)または循環保護で防ぐ。

MySQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

MySQL 8.0(および MariaDB 10.2+)は標準準拠の WITH RECURSIVE 構文をサポートする。再帰の深さは cte_max_recursion_depth システム変数(デフォルト 1000)で制限される。古いバージョンではストアドプロシージャでループを書く必要があった。

SQL Server

WITH tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level
OPTION (MAXRECURSION 100);

SQL Server では再帰 CTE に RECURSIVE キーワードを書かない。自身を参照する UNION ALL を含む CTE が自動的に再帰として扱われる。デフォルトの再帰上限は 100 で、OPTION (MAXRECURSION n) で変更する(0 = 無制限、慎重に使用する)。

MongoDB

// Closest equivalent: the $graphLookup stage
db.employee.aggregate([
  { $match: { manager_id: null } },
  { $graphLookup: {
      from: "employee",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "manager_id",
      as: "subordinates"
  } }
])

MongoDB には再帰 CTE がなく、階層の走査には $graphLookup 集計ステージを使用する。connectFromField/connectToField がリンクするフィールドを、maxDepth が深さの上限を設定する。ツリーとグラフの関係を単一のクエリで解決する。

Redis

# Not applicable: Redis has no recursive queries.

Redis はクエリ言語を提供しない。階層/グラフの走査はアプリケーションコードで、またはサーバー側で Lua スクリプトで行う。コアの Redis ではツリーの走査はクライアント側で記述する。(以前使用されていた RedisGraph モジュールのサポートは 2023 年に終了した。)

Elasticsearch

# Not applicable: recursive queries are not supported.
# Approach: model with parent-child (join field) or nested,
# deep hierarchies are traversed on the application side.

Elasticsearch は再帰クエリを提供しない(WITH RECURSIVE / CONNECT BY に相当するものはない)。階層関係は 'join'(親/子)フィールド型で、またはツリーのパス(マテリアライズドパス)をフィールドに格納してモデル化する。多階層の走査は通常アプリケーションで行う。