【SQL】自己結合（SELF JOIN）の使い方

SQL
2021.07.31

こんにちは、かつコーチです。

「社員テーブルの中で、上司と部下の関係を一覧にしたい」「同じ商品カテゴリの中で、価格が近い商品同士を比較したい」。
こうした「同じテーブルの中にある行同士を比較・結合したい」というケースで使うのが自己結合（SELF JOIN）です。

この記事では、employeesテーブルを例に自己結合の考え方と実装方法を解説します。前提として、通常のJOINやテーブルエイリアスの使い方は理解している読者を想定しています。

自己結合（SELF JOIN）とは？

同じテーブルを2回参照して結合する仕組み

自己結合とは、同じテーブルを2つあるかのように扱い、テーブル自身と結合する手法です。
SQLにSELF JOINという専用の構文があるわけではなく、通常のJOINにテーブルエイリアス（テーブルに一時的な別名を付ける仕組み）を使うことで実現します。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    manager_id INT
);

manager_id列には、その社員の上司にあたる社員のidが格納されています。
この構造は階層構造（自己参照構造）と呼ばれ、組織図やカテゴリのツリー構造など、実務で頻出するデータモデルです。

なぜ自己結合が必要なのか

employeesテーブル単体では、manager_idに入っているのは数値のidだけで、上司の名前は分かりません。
「社員名と、その上司の名前を並べて一覧にしたい」という要求に応えるには、employeesテーブルを「社員として」と「上司として」の2つの役割で同時に参照する必要があります。
これが自己結合が必要になる典型的な場面です。

基本の書き方

手順1：テーブルに2つのエイリアスを付ける

自己結合では、同じテーブルに異なるエイリアスを付けて、別々のテーブルであるかのように扱います。

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

ここではeを「社員側」、mを「上司側」として扱っています。
e.manager_id = m.idという結合条件によって、社員のmanager_idと一致するidを持つ行を「上司の行」として結合しています。

手順2：LEFT JOINを使う理由

上記の例ではINNER JOINではなくLEFT JOINを使っています。
これは、社長など「上司がいない社員（manager_idがNULL）」も一覧に含めるためです。
INNER JOINにすると、上司がいない社員の行が結果から除外されてしまいます。

❌ INNER JOIN の場合、manager_idがNULLの社員（例：社長）が結果から消える
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

✅ LEFT JOIN の場合、上司がいない社員も manager_name が NULL として表示される
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

「全員を一覧に含めたいのか、上司がいる人だけに絞りたいのか」で、INNER JOINとLEFT JOINを使い分けます。

手順3：同じレベルの行同士を比較する

自己結合は上下関係だけでなく、「同じ条件を満たす行同士の比較」にも使えます。
例えば、同じdepartment（部署）に所属する社員のペアを一覧にする場合です。

SELECT e1.name AS employee1, e2.name AS employee2, e1.department
FROM employees e1
INNER JOIN employees e2
    ON e1.department = e2.department
    AND e1.id < e2.id;

e1.id < e2.idという条件を加えているのがポイントです。
これがないと、「Aさん×Bさん」と「Bさん×Aさん」のような同じペアが重複して出力されたり、「Aさん×Aさん」自身との組み合わせまで含まれてしまいます。

つまずきやすい設定・注意点

同じペアが重複して出力される

❌ Before：自己結合の結合条件が不十分で、同じ組み合わせが2回出力される

SELECT e1.name AS employee1, e2.name AS employee2
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department;

私が初めて自己結合で組み合わせを作ったとき、まさにこの書き方をしてしまい、結果を確認すると想定の2倍近い行数が返ってきて驚いたことがあります。
原因は、e1.id < e2.idのような一意な組み合わせに絞る条件を入れていなかったことでした。
departmentが一致するだけの条件では、「Aさん×Bさん」と「Bさん×Aさん」の両方が別の行として出力され、さらに「Aさん×Aさん」自身の組み合わせまで含まれてしまいます。

✅ After：e1.id < e2.idを条件に加えて、一意な組み合わせだけに絞る

SELECT e1.name AS employee1, e2.name AS employee2
FROM employees e1
INNER JOIN employees e2
    ON e1.department = e2.department
    AND e1.id < e2.id;

「同じ行との組み合わせ」や「順序違いの重複」を避けたい場合は、IDの大小関係を条件に加えるのが定石です。

エイリアスを付け忘れてカラム名が曖昧になる

自己結合では、同じテーブルを2回参照するため、エイリアスを省略するとColumn 'name' in field list is ambiguous（カラム名が曖昧）というエラーになります。
すべてのカラム参照に、必ずどちらのエイリアスを指しているかを明示する必要があります。

❌ SELECT name FROM employees e JOIN employees m ON e.manager_id = m.id;
✅ SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id;

応用・一歩先の使い方

複数階層の上司を辿る

自己結合を重ねることで、「上司の、さらに上司」といった複数階層の関係も表現できます。

SELECT e.name AS employee_name, m.name AS manager_name, gm.name AS grandmanager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

ただし、階層の深さがデータによって不定な組織図などでは、この書き方は階層数の分だけJOINを重ねる必要があり限界があります。
不定な深さの階層を再帰的に辿りたい場合は、MySQL 8.0以降で使える再帰CTE（WITH RECURSIVE）という機能を使うのが定石です。
この内容はSQL基礎編の範囲を超えるため、応用編で扱います。

前後のデータを比較する

自己結合は、時系列データで「前回の値と今回の値を比較する」用途にも使えます。

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE,
    amount INT
);

SELECT s1.sale_date, s1.amount, s2.amount AS prev_amount
FROM sales s1
LEFT JOIN sales s2 ON s1.id = s2.id + 1
ORDER BY s1.sale_date;

近年はウィンドウ関数のLAGを使う方がシンプルに書けるケースも多いですが、自己結合の考え方を理解しておくと、ウィンドウ関数が使えない環境でも同等の処理を組み立てられます。

まとめ

この記事のポイント

  • 自己結合は、同じテーブルを2つのエイリアスで参照し結合する手法
  • 上司・部下のような自己参照構造のデータを一覧化する際によく使う
  • 全件を含めたいかどうかでINNER JOINとLEFT JOINを使い分ける
  • 組み合わせのペアを作る際は、e1.id < e2.idのような条件で重複を防ぐ

次に読むべき記事

  • SQL 複数テーブル JOIN
  • SQL UNION UNION ALL 違い
  • SQL 相関サブクエリ 使い方

タグ: SQL, 上級者向け, JOIN

タイトルとURLをコピーしました