自己結合とは、同じテーブル同士で結合することを指しますが、どのような場面で使うのでしょうか。
自己結合の使用例を2部構成で紹介します。
まずは第1部として、同テーブルの異なる項目での自己結合の使用例を紹介します。
異なる項目での自己結合の使用例
同テーブルの異なる項目での自己結合での使用例は、上位のデータの取得するようなケースです（循環リレーションシップ）。
具体的には、以下のようなケースです。
- 社員マスタにおいて、ある社員の上司のデータを取得する
- 組織マスタにおいて、ある組織の上位の組織（人事課の上位は総務部など）のデータを取得する
ポイントは、テーブルの項目の一つに上位のIDを持っていることです。
社員マスタを例に自己結合を考える
以下のような社員マスタを例に考えてみます。
-- employee（社員マスタ） -- 1.id（ID） -- 2.name（氏名） -- 3.enter_date（入社年） -- 4.boss_id（上司のID） ID NAME ENTER_DATE BOSS_ID --- --------- ---------- ------- 001 NAGASHIMA 19800101 002 HARA 19900201 001 003 MOTOKI 19950301 001 004 ABE 20010401 002 005 SAKAMOTO 20020501 002 006 UTSUMI 20030601 002 007 MAEDA 20030601 999
上司の氏名も含めた社員情報
上司の氏名も含めた社員情報を出力する場合を考えてみます。
社員のID・氏名・上司のID・上司の氏名を出力します。ただし、上司がいない場合は上司のID・上司の氏名はNULLとします。
SELECT e1.id
,e1.name
,e2.id boss_id
,e2.name boss_name
FROM employee e1
,employee e2
WHERE e1.boss_id = e2.id(+)
;
ID NAME BOSS_ID BOSS_NAME
--- --------- ------- ---------
001 NAGASHIMA
002 HARA 001 NAGASHIMA
003 MOTOKI 001 NAGASHIMA
004 ABE 002 HARA
005 SAKAMOTO 002 HARA
006 UTSUMI 002 HARA
007 MAEDA
特定の条件の合致する上司に属する社員情報
特定の条件の合致する上司に属する社員情報を出力する場合を考えてみます。
1990年より過去に入社している社員の部下社員のID・氏名を出力します。
SELECT e1.id
,e1.name
FROM employee e1
,employee e2
WHERE e1.boss_id = e2.id
AND e2.enter_date < '19900101'
;
ID NAME
--- ---------
002 HARA
003 MOTOKI
上司がマスタに存在しない社員情報
上司がマスタに存在しない社員情報を出力する場合を考えてみます。
該当する社員のID・氏名を出力します。ただし、上司のIDがNULLの社員は対象外とします。
SELECT e1.id
,e1.name
FROM employee e1
WHERE e1.boss_id IS NOT NULL
AND NOT EXISTS(SELECT *
FROM employee e2
WHERE e1.boss_id = e2.id
)
;
ID NAME
--- ---------
007 MAEDA
関連
