再帰的 共通テーブル式（CTE） は、SQL において自己参照クエリを使用して階層データを巡回することを可能にします。この構造は、アンカー メンバー（基本ケース、通常は manager_id IS NULL のルートノード）と再帰メンバー（親-子関係で CTE を自分自身に結合する反復部分）で構成されています。データベース エンジンは、新しい行が返されなくなるまで再帰メンバーを繰り返し実行し、明示的な反復構造を使わずに結果セットを段階的に構築します。
このアプローチは、SQL の宣言的性質を利用し、最適化ツールが効率的な結合アルゴリズム（通常はハッシュ結合またはマージ結合）を選択できるようにします。構文は、再帰的 CTE が使用される PostgreSQL や MySQL では WITH RECURSIVE を、再帰が暗黙的に行われる SQL Server では単に WITH を使用し、次に CTE の名前と列リストが続きます。
12,000 人の従業員を持つ多国籍企業は、SOX コンプライアンス監査のために完全な報告チェーンを生成する必要がありました。既存のシステムでは、各従業員を反復的に処理し、スカラー関数を再帰的に呼び出してそのマネージャーを見つけ、階層を文字列単位で構築する T-SQL CURSOR が使用されていました。このプロセスには 47 分かかり、実行中に Employees テーブルにロックを保持し、業務時間中の人事の更新を妨げ、100 レベルを超える深い階層を処理する際にスタックオーバーフローエラーが頻繁に発生しました（エンジニアリング部門のマトリックス構造では一般的です）。
解決策 A: 一時テーブルを使用した最適化された CURSOR。 チームは、カーソルを再記述してまず結果を一時テーブルにダンプし、最後にバルク挿入することを検討しました。これによりロック時間が 47 分から約 40 分に短縮されます。利点: 最小限のコード変更、レガシーチームにとって馴染みのあるパターン。欠点: 依然として行単位の処理、深い再帰スタックオーバーフローの脆弱性を抱えており、性能問題を緩和するだけです。
解決策 B: HierarchyID データ型。 テーブルを SQL Server のネイティブ HierarchyID 型に移行し、木の位置を最適化された経路としてエンコードして保存します。利点: O(1) の部分木取得、GetAncestor() や GetDescendant() などの組み込みメソッドがあり、読み取り負荷が高いワークロードに対して非常に高速です。欠点: 大規模なスキーマ移行が必要（12,000 行および履歴データ）、再編成中の維持が複雑（マネージャーの更新にはすべての子孫の経路を再計算する必要があります）、会社が PostgreSQL への移行を検討している間、SQL Server にロックされています。
解決策 C: サイクル検出を伴う再帰的 CTE。 従業員テーブルを manager_id で自分自身に結合する再帰的 CTE を実装し、無限ループを防ぐために訪問したノードを追跡するパス配列を使用します（これは、データ入力エラーにより 2 回発生しました）。利点: 純粋な ANSI SQL 標準（移行時の PostgreSQL に移植可能）、セットベースの実行により実行時間が 4 分 12 秒に短縮され、実行中にテーブルロックが保持されない（スナップショット分離を使用）、スタックオーバーフローがなく任意の深さをハンドリングし、データ品質の問題（サイクル）を自動的に検出します。
チームは 解決策 C を選択しました。実装では、PostgreSQL の配列集約を使用して従業員 ID を蓄積する path 列を持つ再帰的 CTE を使用し、新しい manager_id が蓄積されたパスに存在しないことを確認する WHERE 句を持ちました。結果は 91% のパフォーマンス向上、プロダクションロックの排除、および以前にアプリケーションクラッシュを引き起こした循環報告関係の早期検出をもたらしました。
再帰的 CTE はなぜ終了し、データに循環参照が含まれるとどうなるのか？
候補者は、再帰的 CTE にサイクル検出が組み込まれていると思い込んでいますが、標準の SQL 再帰は、再帰メンバーがゼロの新しい行を返すときのみ終了します。従業員 A が B に報告し、B が C に、C が A に戻る場合、CTE は無限に実行されます（または SQL Server のデフォルトの 100 再帰レベルのような実装制限に達するまで）。この解決策には、訪問したノード ID をパス列に累積することによる手動のサイクル検出が必要であり（配列や区切り文字付き文字列を使用）、WHERE new_id != ALL(path_array) をフィルタリングする必要があります。最新の PostgreSQL （14+）および SQL Server （2022+）は、標準の SQL:1999 CYCLE 句をサポートしています: WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path は、自動的にこれを処理します。
再帰的 CTE とカーソルベースのアプローチの実行計画の違いは何であり、同時実行性にとってなぜ重要か？
ジュニア候補者は、CTE は「速い」と主張することが多いですが、実行モデルを理解していません。SQL Server または PostgreSQL の CURSOR は、エンジンに結果セットを具現化させ、行単位で反復することを強制し、通常はロックや一時データベースリソースを反復の期間中に必要とする Keyset または Static カーソルタイプを使用します。これにより、上記の例での 47 分間全体にわたって基礎となるテーブルに 共有ロック（または 更新ロック）が作成されます。対照的に、再帰的 CTE は単一の SELECT ステートメントです。コミットされた読み取りスナップショット分離（RCSI） または スナップショット分離 の下で、一貫した時点のデータ ビューをロックを保持せずに読み取り、バージョンストアを代わりに使用します。最適化ツールは、親子インデックスの Index Seek 操作でで、再帰メンバーのために通常 Nested Loop Joins を選択し、カーソルアプローチよりも O(n log n) になります。
再帰的 CTE と Nested Sets Model の階層データの違いは何であり、どのような場合にどちらを選ぶのか？
候補者は、トラバース メソッドをストレージ モデルと混同することがよくあります。再帰的 CTE は、隣接リスト（parent_id 外部キー）に関するクエリ時のトラバース技術です。Nested Sets Model（左/右値）は、ツリーのトラバース パスを事前に計算したストレージ設計パターンです。書き込み負荷が高いワークロード（頻繁なマネージャーの変更）では、隣接リスト上の再帰的 CTE の方が優れており、単一の parent_id の更新は O(1) ですが、ネストされたセットは移動されたノードの右側にあるすべての右値を更新するのに O(n) を必要とします。読み取りが重く静的な階層（毎月変わる組織図）に対しては、ネストされたセットが O(1) の部分木取得を提供する（WHERE left BETWEEN parent.left AND parent.right）のに対し、再帰的 CTE には O(n) が必要です。ハイブリッドアプローチは、すべての祖先-子孫ペアを保持する別のテーブル『Closure Tables』を使用し、トラバースとすべての子を見つける両方に O(1) を提供しますが、ストレージは O(n²) かかり維持が複雑になります。選択は読み込み/書き込み比率によります：書き込みが操作の 5% を超える場合は再帰的 CTE を使用し、主に静的なツリーのためにネストされたセットやクローズ テーブルを使用します。