SQL練習室
← SQLガイド

SQLの自己結合(セルフジョイン)とは?上司と部下・同じ部署のペアを求める

自己結合は、同じ表を2回使って、行どうしを結び付ける書き方です。社員表のmanager_idは同じ表の社員idを指すので、上司の名前を取るには社員表を「部下側」と「上司側」の2役として扱います。上司と部下、同じ部署のペア、上司より給与が高い社員を同じ5人の表で確認しましょう。

例で使う表

employeesは1行が社員1人です。manager_idは直属の上司の社員idで、青木は上司がいないのでNULLです。salaryは月給(円)です。青木は営業、北野は営業で青木の部下、中央は開発で青木の部下、大和は営業で北野の部下、村上は開発で中央の部下です。ORDER BYのない結果の行順は一例で、保証されません。

employees:社員5人
idnamedepartmentmanager_idsalary
1青木営業NULL600000
2北野営業1450000
3中央開発1480000
4大和営業2470000
5村上開発3400000

自己結合の基本構文

sql
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人だけが表示されます。

sql
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;
実行結果
namemanager
北野青木
中央青木
大和北野
村上中央

LEFT JOINで上司がいない社員も残す

部下側の社員を全員表示したいときは、INNER JOINをLEFT JOINに変えます。青木は上司がいないため、managerがNULLの行として残ります。5人全員が1行ずつ表示されるので、上司のいない最上位の社員を見落としません。ONの条件は変わりません。

sql
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;
実行結果
namemanager
青木NULL
北野青木
中央青木
大和北野
村上中央

上司ごとの部下の人数を数える

自己結合した結果は普通の結合結果なので、上司側の列でGROUP BYできます。上司側のmを基準に部下の行を数えると、青木は2人、北野と中央は1人です。部下がいない大和と村上は、上司側として現れないため結果に出ません。部下0人の社員も表示したい場合は、上司側を左にしたLEFT JOINにします。

sql
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;
実行結果
namesubordinates
青木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組になります。

sql
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_namesecond_namedepartment
青木北野営業
青木大和営業
北野大和営業
中央村上開発

部下と上司の値を比べて絞り込む

自己結合の利点は、部下の列と上司の列を同じ行に並べられることです。WHEREでe.salary > m.salaryと書けば、上司より給与が高い社員を選べます。大和は470,000円で、上司の北野の450,000円より高いので該当します。北野は450,000円で青木の600,000円より低く、中央の480,000円は青木より低いので除かれます。

sql
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;
実行結果
namesalarymanagermanager_salary
大和470000北野450000

上司の上司まで2段階でたどる

上司の上司を知るには、同じ表をもう1回結合して、e、m、gの3役にします。mは直属の上司、gはその上司です。大和は北野の部下で、北野の上司は青木なので、2段階上は青木です。青木と、青木の直属の部下である北野と中央は、2段階上がいないのでNULLになります。段階が増えるたびに結合を足すため、深さが決まっていない階層は再帰CTEが向きます。

sql
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;
実行結果
namemanagergrand_manager
青木NULLNULL
北野青木NULL
中央青木NULL
大和北野青木
村上中央青木

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

a.id <> b.idだと同じペアが2回出る

ペアを作るときに重複を避けるつもりでa.id <> b.idと書くと、同じ人どうしは消えても、青木と北野、北野と青木のように順序違いが両方残ります。この表では営業が6組、開発が2組の8行になり、組み合わせの数が2倍になります。順序を区別しない組には、a.id < b.idのように片方向だけにします。

sql
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も許す条件にします。

sql
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。