自己結合

自己結合 とは

SQLの自己結合を学び、同一テーブル内の関連データを操作するスキルを習得します。本レッスンでは、自己結合 の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。

なぜ重要か

「社員と上司」「カテゴリと親カテゴリ」「商品とその関連商品」のように、同じテーブルの中で行同士を結びつけたい場面があります。これを実現するのが自己結合 (SELF JOIN) です。同じテーブルを 2 つの別名で参照し、片方を「自分」、もう片方を「相手」として結合します。組織図や階層構造、隣接ペアの比較などに必須のテクニックで、ER 図上は同じテーブルから自分自身に伸びる線で表現されます。最初は混乱しがちな概念ですが、別名さえつければ普通の JOIN と同じ書き方になります。

構文

同じテーブルを 2 回書き、それぞれに別名を付けます。

SQL クエリ

SELECT 列 FROM employees e JOIN employees m ON e.manager_id = m.id;

e を「社員自身」、m を「上司」として扱うことで、1 行に「社員」と「上司」の情報を並べられます。同じテーブルですが SQL から見ると別の 2 つの集合として扱われるため、混乱せず読めるようになります。

ステップごとの動作

下の図は社員テーブルを 2 回参照して、社員ごとに上司の名前を取り出す流れです。

diagram (will load when visible)

代表例 1 — 社員と上司の対応表

SQL クエリ

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

LEFT JOIN にすることで「上司がいない社長」も結果に残ります。INNER JOIN だと社長が消えるので注意してください。組織図を SQL でフラットなレポートに落とすときに最初に書くパターンです。

代表例 2 — 連番カラムで隣接比較

SQL クエリ

SELECT a.day, a.sales AS today, b.sales AS yesterday FROM daily_sales a LEFT JOIN daily_sales b ON b.day = a.day - INTERVAL '1 day';

同じ売上テーブルを 2 つ参照し「今日と昨日の売上」を 1 行に並べる例です。LAG 関数を使わない時代の定番テクで、今もシンプルさで使われます。前日比・前年比のレポートで便利です。

代表例 3 — 同じカテゴリの別商品を探す

SQL クエリ

SELECT a.name AS product, b.name AS related FROM products a JOIN products b ON a.category = b.category AND a.id <> b.id ORDER BY a.id, b.id;

「同じカテゴリの商品ペア」を作るパターンです。a.id <> b.id を付けることで「自分自身とペアにならない」ようにしています。レコメンドの初歩でも使う発想です。

落とし穴

自己結合では別名 (AS) が必須です。FROM employees JOIN employees ON ... のように別名を省くと「曖昧な参照」エラーになります。短い別名 (e, m) を使うと SQL が一気に読みやすくなります。意味のある 2 文字 (employee → e、manager → m) を選ぶと、後で読むときの認知負荷が下がります。

INNER JOIN で自己結合すると、対応するレコードが無い行 (社長など) が結果から消えます。階層トップを残したいときは LEFT JOIN を使い、上司側の列が NULL でも残るようにしましょう。組織レポートで「社長が出てこない」のはほぼバグなので、レビュー時に必ずチェックします。

階層が深いときの限界

2 段階の上下関係なら自己結合 1 回で取れますが、3 段以上の階層 (社員 → 課長 → 部長 → 役員) を一気に展開したいときは、再帰 CTE (WITH RECURSIVE) を使うのが正攻法です。自己結合は「直近 1 段」だけを取りやすいツールと覚えておきましょう。再帰 CTE は後の章で学びます。

自己結合の練習問題ヒント

自己結合を初めて書くときは、頭の中で「同じテーブルから 2 つの集合を作っている」とイメージすると混乱しません。最初は紙に 2 つのテーブルを並べて書き出してから、SQL に落とすと書きやすくなります。慣れてくると、頭の中だけで構造を組み立てられるようになります。

自己結合と SQL の表現力

自己結合を覚えると、「リスト同士の比較」「隣接ペアの差分」「相互推薦」など、これまで諦めていた SQL 表現が一気に書けるようになります。最近のデータベースには LAG / LEAD / ROW_NUMBER などのウィンドウ関数も入っているので、それらと組み合わせれば自己結合よりさらにシンプルに書けるケースも増えています。とはいえ「同じテーブルを 2 視点で見る」という発想は SQL の表現力の根幹なので、自己結合の感覚は身につけておく価値があります。

まとめ

  • 自己結合は同じテーブルを 2 つの別名で参照する
  • 「社員と上司」「今日と昨日」など 1 段の関係に強い
  • 階層トップを残したいときは LEFT JOIN
  • 多段階の階層は再帰 CTE のほうが向く
  • 同じ集合の中でペアを作るときは a.id <> b.id で自分自身を除外
  • 初学者は紙に書き出してから SQL に落とすと理解が早い

次のレッスン

次は 集合演算 UNION/INTERSECT/EXCEPT です。UNION、INTERSECT、EXCEPTを使ったSQL集合演算を実践的に学習します。

事前確認 — 進む前に次の 3 つができることを確認しましょう。

  1. 自己結合 の要点を自分の言葉で説明できる
  2. このレッスンの最小コード (または操作手順) を見ずに書ける
  3. 練習問題やクイズで間違えた箇所を読み直して理解した

理解度チェック (30 秒)

Q. 自己結合 とは何か、1 文で説明してください。

この章のポイント

A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。

関連レッスン

テーブル構造

schema.sql

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(30), manager_id INT ); INSERT INTO employees VALUES (1, '社長', NULL), (2, '部長A', 1), (3, '部長B', 1), (4, '課長X', 2), (5, '課長Y', 2), (6, '一般a', 4), (7, '一般b', 5);

期待される出力

employee_idemployee_namemanager_name
1社長NULL
2部長A社長
3部長B社長
4課長X部長A
5課長Y部長A
6一般a課長X
7一般b課長Y

ヒント

query.sql
query.sql
学習モード
コードの実行結果
データベースを初期化中...