SQLの自己結合(セルフジョイン)とは?上司と部下・同じ部署のペアを求める
自己結合は、同じ表を2回使って、行どうしを結び付ける書き方です。社員表のmanager_idは同じ表の社員idを指すので、上司の名前を取るには社員表を「部下側」と「上司側」の2役として扱います。上司と部下、同じ部署のペア、上司より給与が高い社員を同じ5人の表で確認しましょう。
例で使う表
employeesは1行が社員1人です。manager_idは直属の上司の社員idで、青木は上司がいないのでNULLです。salaryは月給(円)です。青木は営業、北野は営業で青木の部下、中央は開発で青木の部下、大和は営業で北野の部下、村上は開発で中央の部下です。ORDER BYのない結果の行順は一例で、保証されません。
| id | name | department | manager_id | salary |
|---|---|---|---|---|
| 1 | 青木 | 営業 | NULL | 600000 |
| 2 | 北野 | 営業 | 1 | 450000 |
| 3 | 中央 | 開発 | 1 | 480000 |
| 4 | 大和 | 営業 | 2 | 470000 |
| 5 | 村上 | 開発 | 3 | 400000 |
自己結合の基本構文
SELECT e.name, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id;同じ表を2回使って上司の名前を取り出す
同じ表をFROMとJOINの両方に書くので、eとmという別名で区別します。eは部下側、mは上司側です。ONのe.manager_id = m.idは「部下の上司idが、上司側の社員idと一致する」という対応です。INNER JOINでは、青木のようにmanager_idがNULLの行は一致する上司がなく、結果に残りません。このため5人のうち4人だけが表示されます。
SELECT e.name, m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.id
ORDER BY e.id;| name | manager |
|---|---|
| 北野 | 青木 |
| 中央 | 青木 |
| 大和 | 北野 |
| 村上 | 中央 |
LEFT JOINで上司がいない社員も残す
部下側の社員を全員表示したいときは、INNER JOINをLEFT JOINに変えます。青木は上司がいないため、managerがNULLの行として残ります。5人全員が1行ずつ表示されるので、上司のいない最上位の社員を見落としません。ONの条件は変わりません。
SELECT e.name, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id
ORDER BY e.id;| name | manager |
|---|---|
| 青木 | NULL |
| 北野 | 青木 |
| 中央 | 青木 |
| 大和 | 北野 |
| 村上 | 中央 |
上司ごとの部下の人数を数える
自己結合した結果は普通の結合結果なので、上司側の列でGROUP BYできます。上司側のmを基準に部下の行を数えると、青木は2人、北野と中央は1人です。部下がいない大和と村上は、上司側として現れないため結果に出ません。部下0人の社員も表示したい場合は、上司側を左にしたLEFT JOINにします。
SELECT m.name, COUNT(*) AS subordinates
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY m.id;| name | subordinates |
|---|---|
| 青木 | 2 |
| 北野 | 1 |
| 中央 | 1 |
同じ部署のペアを重複なしで作る
同じ部署の2人を組にするには、ONでdepartmentの一致を指定します。さらにa.id < b.idを加えると、同じ人どうしの組と、順序だけが違う同じ組を除けます。営業は青木・北野・大和の3人で3組、開発は中央・村上で1組、合計4組です。a.id < b.idがないと、営業は3人×3人の9組、開発は2人×2人の4組で、合計13組になります。
SELECT a.name AS first_name, b.name AS second_name, a.department
FROM employees AS a
INNER JOIN employees AS b
ON a.department = b.department
AND a.id < b.id
ORDER BY a.id, b.id;| first_name | second_name | department |
|---|---|---|
| 青木 | 北野 | 営業 |
| 青木 | 大和 | 営業 |
| 北野 | 大和 | 営業 |
| 中央 | 村上 | 開発 |
部下と上司の値を比べて絞り込む
自己結合の利点は、部下の列と上司の列を同じ行に並べられることです。WHEREでe.salary > m.salaryと書けば、上司より給与が高い社員を選べます。大和は470,000円で、上司の北野の450,000円より高いので該当します。北野は450,000円で青木の600,000円より低く、中央の480,000円は青木より低いので除かれます。
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.id
WHERE e.salary > m.salary
ORDER BY e.id;| name | salary | manager | manager_salary |
|---|---|---|---|
| 大和 | 470000 | 北野 | 450000 |
上司の上司まで2段階でたどる
上司の上司を知るには、同じ表をもう1回結合して、e、m、gの3役にします。mは直属の上司、gはその上司です。大和は北野の部下で、北野の上司は青木なので、2段階上は青木です。青木と、青木の直属の部下である北野と中央は、2段階上がいないのでNULLになります。段階が増えるたびに結合を足すため、深さが決まっていない階層は再帰CTEが向きます。
SELECT e.name, m.name AS manager, g.name AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id
LEFT JOIN employees AS g
ON m.manager_id = g.id
ORDER BY e.id;| name | manager | grand_manager |
|---|---|---|
| 青木 | NULL | NULL |
| 北野 | 青木 | NULL |
| 中央 | 青木 | NULL |
| 大和 | 北野 | 青木 |
| 村上 | 中央 | 青木 |
自己結合でよくある間違い
a.id <> b.idだと同じペアが2回出る
ペアを作るときに重複を避けるつもりでa.id <> b.idと書くと、同じ人どうしは消えても、青木と北野、北野と青木のように順序違いが両方残ります。この表では営業が6組、開発が2組の8行になり、組み合わせの数が2倍になります。順序を区別しない組には、a.id < b.idのように片方向だけにします。
SELECT a.name AS first_name, b.name AS second_name
FROM employees AS a
INNER JOIN employees AS b
ON a.department = b.department
AND a.id <> b.id;LEFT JOINの後に上司側の列をWHEREで絞ると、上司のいない行が消える
上司のいない青木はm.departmentがNULLです。LEFT JOINの後にWHERE m.department = '営業'を書くと、NULLとの比較は真にならず、青木の行が消えて実質INNER JOINになります。上司がいない行を残したまま上司側の値で絞るなら、条件をONに書くか、IS NULLも許す条件にします。
SELECT e.name, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id
WHERE m.department = '営業';実際に1問解いてみる
自己結合
employeesから、上司がいる社員について、社員名と直属の上司名を取得してください。上司名の列はmanagerとします。行順は問いません。
関連構文
自己結合についてよくある質問
- 自己結合は特別なJOINの種類ですか?
- 別の種類ではなく、INNER JOINやLEFT JOINに同じ表を2回指定する使い方です。表に別名を付けて、1つを部下側、もう1つを上司側のように別の役割で使います。
- 上司がいない社員を残すにはどうしますか?
- 部下側を左にしたLEFT JOINにします。この表では青木のmanager_idがNULLなので、INNER JOINでは消えますが、LEFT JOINならmanagerがNULLの行として残ります。
- ペアの重複を避ける条件はa.id < b.idだけですか?
- 順序を区別しないペアなら、主キーのような一意な列でa.id < b.idにするのが基本です。a.id <> b.idでは、順序違いの組が両方残ります。同じ人どうしを除くだけでよい場合に限って使います。
- 階層が何段あるか分からない場合も自己結合で書けますか?
- 段数が決まっていれば、結合を段数分だけ足せます。この表の上司の上司は2回結合です。段数が不定の場合は、再帰CTE(WITH RECURSIVE)で上司をたどる方が書きやすくなります。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:結合テーブル(JOIN) — INNER・LEFT・FULL・CROSS JOINの定義とON条件
- PostgreSQL公式:テーブルとカラムの別名 — 同じ表を複数回参照するための別名(自己結合)