0Pricing
SQL Interview Prep · レッスン

階層構造のためのSELF JOIN

従業員と上司、親と子の関係を表現するために、テーブルを自身にJOINする方法を学びます。

「階層構造のためのSELF JOIN」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

SELF JOIN とは何か

自己結合とは、1つのテーブルが結合の両側に現れる結合のことです。SELF JOIN という特別なキーワードはありません。通常の INNER または LEFT JOIN を記述し、同じテーブルを2回参照します。

これを機能させる鍵はテーブルエイリアスです。テーブルの各コピーに異なるエイリアスを付けることで、エンジンはそれらを独立した2つのテーブルとして扱います。

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

エイリアスが必須である理由

異なるエイリアスがないとクエリが曖昧になります。すべての列名が2回ずつ現れるため、エンジンはどちらのコピーを指しているのか判断できません。各インスタンスにエイリアスを付ければ解決できます。

この結合は、「各従業員の行を、その従業員のマネージャーである従業員の行と組み合わせる」と読みます。エイリアス e は従業員、m はマネージャーを表し、どちらも同じ物理テーブルに由来します。

-- e = the employee, m = that employee's manager
SELECT e.id, e.name, m.name AS reports_to
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

従業員とマネージャーのモデル

典型的な自己結合のシナリオは隣接リストです。1つのテーブルに行を格納し、各行が同じテーブルへの外部キーによって親を指します。

employees テーブルに employees.id を参照する manager_id があると、組織図全体を1つのテーブルでモデル化できます。各マネージャーも、単なる別の従業員行です。

-- One table holds the whole hierarchy
-- employees(id, name, manager_id)
-- manager_id -> employees.id

全員をマネージャーとともに一覧表示する

自己結合で最もよく問われる質問は、各従業員の隣にマネージャーの名前を表示することです。従業員側のコピーとマネージャー側のコピーを e.manager_id = m.id で結合します。

これにより、マネージャーが存在する従業員ごとに1行返されます。組織の最上位にいるCEOには manager_id がNULLであり、内部結合では除外されることに注意してください。

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

ツリーの最上位を残す LEFT JOIN

manager_id がNULLのCEOを含めるには、LEFT JOIN に切り替えます。従業員側は保持され、親を持たない行ではマネージャー側の列がNULLになります。

面接官は、内部自己結合ではルートノードが除外されることを覚えているか確認するために、この問題を使います。修正方法は、外部結合で「一致しない行を保持する」場合と同じです。

SELECT e.name AS employee,
       COALESCE(m.name, '(top level)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

マネージャーごとの直属の部下数を数える

よくある追加質問は、各マネージャーに直属する部下が何人いるかです。自己結合してから、マネージャーでグループ化します。

従業員をマネージャーに結合し、マネージャーの識別情報でグループ化して、従業員を数えます。これは直属の部下だけを数え、その下の階層全体は数えません。

SELECT m.name AS manager, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

2階層下までたどる

従業員、そのマネージャー、さらにそのマネージャーのマネージャーを取得するには、テーブルのコピーを3つ連結します。各階層が、もう1回の自己結合に相当します。

これは固定された既知の深さであれば機能します。任意の深さが必要なら、自己結合だけでは不十分です。その場合は再帰 CTE を使う合図であり、面接官はその言及を期待しています。

SELECT e.name AS employee,
       m.name AS manager,
       g.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees g ON m.manager_id = g.id;

自己結合と再帰 CTE の比較

面接官が確認する重要な違い：

  • 自己結合は固定された階層数を処理します。コピーが3つなら3階層までで、それ以上は処理しません。
  • 再帰 CTEは、新しい行がなくなるまでテーブルを自分自身に再結合することで、無制限の深さを処理します。

つまり、「各従業員と直属のマネージャーを表示する」は自己結合ですが、「階層をたどってすべての祖先を一覧表示する」には再帰が必要です。

親子カテゴリ

同じパターンで、あらゆるツリー構造をモデル化できます。商品カテゴリ、コメントスレッド、地理的地域などです。自身の id を参照する parent_id を持つ categories テーブルは、従業員とマネージャーのケースと構造が同じです。

「自己参照する外部キーを持つテーブル」は「自己結合または再帰」に当たると認識できることが、再利用可能な重要な洞察です。

SELECT c.name AS category,
       p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

自己結合でよくある間違い

面接では次の点に注意してください：

  • エイリアスを忘れると、列名が曖昧であるというエラーになります。
  • INNER JOIN を使うと、ルート行（親がNULLの行）が気付かないうちに除外されます。
  • 結合の向きを間違えること。e.id = m.manager_id ではなく e.manager_id = m.id としてしまうケースです。

ON を書く前に、どのエイリアスが子で、どのエイリアスが親かを必ず声に出して確認してください。

自己結合を使う場面

テーブル内の別の行同士を関連付ける場合は、いつでも自己結合を検討してください：

  • 参照が1階層に固定された階層構造（従業員とマネージャー）。
  • 1つのテーブルの行同士の組み合わせまたは比較（次のレッスンで扱います）。

関係が再帰的で上限がないなら、より適したツールとして再帰 CTE を挙げてください。このニュアンスを理解しているかどうかが、初級者と中級者を分けます。

理解度チェック

階層構造における自己結合の理解度を確認します。

まとめ：階層構造における SELF JOIN

要点：

  • 自己結合は、両側に同じテーブルを置き、エイリアスで区別する通常の結合です。
  • 隣接リスト（manager_id のような自己参照外部キー）は、1つのテーブルでツリーをモデル化します。
  • 一致するペアには INNER JOIN を使い、NULLの親を持つルート行を残すには LEFT JOIN を使います。
  • 自己結合で扱えるのは固定された深さです。上限のないたどりには再帰 CTEが必要です。

よくある質問

「階層構造のためのSELF JOIN」レッスンは無料ですか？

はい。「階層構造のためのSELF JOIN」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「階層構造のためのSELF JOIN」で何を学びますか？

従業員と上司、親と子の関係を表現するために、テーブルを自身にJOINする方法を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「階層構造のためのSELF JOIN」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. CROSS JOINと直積
  2. 階層構造のためのSELF JOIN
  3. 1つのテーブル内で行を比較する
  4. 適切なJOINの種類を選ぶ
← SQL Interview Prepに戻る