自己結合の使用例１（異なる項目での結合）

time 2016/10/27

folder ORACLE

自己結合とは、同じテーブル同士で結合することを指しますが、どのような場面で使うのでしょうか。

自己結合の使用例を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

-- 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

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

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