自己結合の限界
代わりに再帰が必要になる場合を学びます。
「自己結合の限界」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。
自己結合とは
自己結合とは、テーブルをそれ自体に結合することです。同じテーブル内の行を比較する場合に便利で、たとえば1つのemployeesテーブルに格納された従業員とその上司を見つける際に使用できます。
その制限を確認する前に、基本的な自己結合が実際にどのように機能するかを振り返りましょう。
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;1階層
自己結合は、階層構造の1つの階層レベルを優雅に処理できます。各従業員とその直属の上司を組み合わせたい場合、必要なのは1つの自己結合だけです。
データが1階層だけの場合や、直接の親子関係だけに関心がある場合には、これで問題なく対応できます。
SELECT child.name AS employee, parent.name AS direct_manager
FROM employees child
LEFT JOIN employees parent ON child.manager_id = parent.id;2階層：早くも複雑に
従業員とその上司、さらにその上司の上司まで必要な場合はどうでしょうか。2つ目の自己結合を追加する必要があります。クエリは長くなり、読みづらくなります。
階層を1レベル追加するたびに、結合用の別名とJOIN句を1つずつ追加する必要があります。
SELECT e.name AS employee,
m.name AS manager,
gm.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;3階層：パターンが崩れる
3つ目の階層を追加するには、さらに別の結合が必要になります。この段階では、クエリは冗長で壊れやすく、保守も困難です。階層の深さが変わると、クエリ全体を書き直さなければなりません。
これが自己結合の最初の大きな制限です。自己結合は階層の深さに応じて拡張できません。
SELECT e.name AS employee,
m.name AS manager,
gm.name AS grand_manager,
ggm.name AS great_grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id
LEFT JOIN employees ggm ON gm.manager_id = ggm.id;未知の深さ：自己結合では対応できない
実際の組織図やカテゴリツリーでは、深さがクエリの実行時点では不明であることがよくあります。自己結合では、レベル数をあらかじめハードコードする必要があります。明日、階層が10レベルの深さになった場合、3レベルまでの自己結合クエリではデータがひそかに欠落します。
これは根本的な制限です。自己結合では、任意の数のレベルをたどることができません。
-- This only retrieves up to 3 levels deep.
-- Employees deeper than level 3 are simply missing from results.
SELECT e.name, m.name, gm.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;循環によって自己結合は完全に破綻
もう1つの重大な制限は、データに循環（AがBを管理し、BがCを管理し、CがAを管理する）が含まれている場合です。自己結合クエリは無限ループにはなりませんが、循環を正しく検出したり報告したりすることもできません。
通常の自己結合だけでは、循環参照を防ぐことはできません。再帰クエリには組み込みの循環検出メカニズムがありますが、自己結合にはそのような仕組みがまったくありません。
-- Cyclic data: row 3 points back to row 1
-- id | name | manager_id
-- 1 | Alice | 3 <-- cycle!
-- 2 | Bob | 1
-- 3 | Charlie | 2
-- A self join just shows one hop; it cannot detect the loop
SELECT e.name, m.name AS reports_to
FROM employees e
JOIN employees m ON e.manager_id = m.id;再帰CTEの導入
SQLには、深さが不明な階層をたどるために特化した解決策があります。それが再帰共通テーブル式（CTE）です。PostgreSQL、MySQL 8以降、SQLite、SQL ServerでサポートされているWITH RECURSIVE構文を使用します。
再帰CTEには、2つの部分があります。アンカーメンバー（開始行）と、再帰メンバー（各関係をたどるステップ）です。
WITH RECURSIVE org_tree AS (
-- Anchor: start with the top-level CEO (no manager)
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: find each employee whose manager is already in org_tree
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, depth FROM org_tree ORDER BY depth;完全なパスを追跡する
再帰CTEの強力な機能の1つは、下位へ進みながらコンテキストを蓄積できることです。たとえば、ルートから各ノードまでの完全なパスを構築できます。これは静的な自己結合ではまったく不可能です。
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id,
name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id,
ot.path || ' > ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;自己結合と再帰CTE：使い分け
自己結合を使用する場合：
- 階層がちょうど1つまたは2つのレベル必要な場合。
- 深さが固定され、あらかじめ分かっている場合。
- CTEのオーバーヘッドなしでシンプルにしたい場合。
再帰CTEを使用する場合：
- 深さが可変または不明な場合。
- 祖先または子孫への完全なパスが必要な場合。
CYCLE句または手動のガードによる循環検出が必要な場合。
パフォーマンスに関する考慮事項
インデックス付きの列に対する自己結合は、深さが固定されたクエリでは非常に高速です。各結合は1回の検索で済み、データベースのオプティマイザも適切に処理します。
再帰CTEはより柔軟ですが、深いツリーや幅の広いツリーではコストが高くなることがあります。不正なデータや予期しない循環によるクエリの暴走を防ぐため、再帰メンバーには必ず深さの上限を設けてください。
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
WHERE ot.depth < 10 -- safety guard: stop at depth 10
)
SELECT name, depth FROM org_tree;再帰が必要な実際のユースケース
自己結合では対応できない、任意の深さの走査が必要な一般的なデータモデルは数多くあります。
- カテゴリツリー — ECカタログ内の入れ子になった商品カテゴリ。
- 部品表 — 部品から構成され、各部品もサブパーツから構成される製品。
- コメントスレッド — 返信への返信への返信。
- ファイルシステムのパス — ディレクトリの中にあるディレクトリ。
これらすべてのケースでは、自己結合を積み重ねるのではなく、再帰CTEを使用してください。
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, name AS full_path
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id,
ct.full_path || ' / ' || c.name
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, full_path FROM category_tree ORDER BY full_path;理解度チェック
自己結合の制限と、代わりに再帰CTEを使用するタイミングについて理解度を確認します。
レッスンのまとめ
このレッスンでは、階層データに対する自己結合の制限について学びました。
- 自己結合は、階層の1つまたは2つの固定されたレベルではうまく機能します。
- レベルを1つ追加するたびに明示的なJOINが必要になるため、クエリは壊れやすく、保守が困難になります。
- 自己結合では未知の深さに対応できません。ハードコードされたレベルを超える行は、気付かないうちに除外されます。
- データ内の循環参照に対する保護機能はありません。
- 深さが可変または不明な場合は、代わりに再帰CTE（
WITH RECURSIVE）を使用します。 - 暴走する実行から保護するため、再帰クエリには必ず深さのガードを追加してください。
自己結合から再帰CTEへ切り替えるタイミングを判断できることは、SQLで木構造データをクエリするための重要なスキルです。
よくある質問
「自己結合の限界」レッスンは無料ですか？
はい。「自己結合の限界」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。
「自己結合の限界」で何を学びますか？
代わりに再帰が必要になる場合を学びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「自己結合の限界」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Academyレッスンでコードを書いて実行できますか？
はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。