自己結合
結合は異なるレコードを1つのレコードとして表示する機能です。 異なるレコードとは、異なる表のレコードばかりではなく、同じ表の異なるレコードの場合だってあります。 例えば、社員表には上司として働いている人もいれば、部下として働いている人も登録されています。つまり、部下のレコードと上司のレコードを1つのレコードにして表示することもできます。
1つの表（同一表）の異なるレコードを結合する方法を自己結合といいます。 自己結合の説明は次の例題を解きながら行いましょう。
問３
次の表を確認してください。
SQL> desc employees 名前 NULL? 型 ----------------------------- -------- -------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
EMPLOYEE_IDとMANAGER_IDの間には、親子関係があります。 ただし、入社直後でまだ上司が決まっていない新入社員や上司のいない最高経営責任者も含まれている場合があります。
あなたは、従業員の名前と入社日と上司の名前を出力するレポートを作成しなければいけません。 上司が決まっていない今年入社の社員の場合は、上司の名前のかわりにFreshman、最高経営責任者の場合はCEOと表示する必要があります。 目的の結果を得るには、どのSQLを使用しますか。 最適なものを選択しなさい。
-
a. SELECT e.last_name,e.hire_date,
NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
'YYYY'),'Freshman','CEO'))
FROM employees e
LEFT OUTER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.department_id = 90; -
b. SELECT e.last_name,e.hire_date,
NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
'YYYY'),'Freshman','CEO'))
FROM employees e
LEFT OUTER JOIN employees m
ON m.manager_id = e.employee_id
WHERE e.department_id = 90; -
c. SELECT e.last_name,e.hire_date,
NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
'YYYY'),'Freshman','CEO'))
FROM employees e
RIGHT OUTER JOIN employees m
ON m.manager_id = e.employee_id
WHERE e.department_id = 90; -
d. SELECT e.last_name,e.hire_date,
NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
'YYYY'),'Freshman','CEO'))
FROM employees e
RIGHT OUTER JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.department_id = 90;
自己結合のポイントは、表別名を使って、部下のレコードを格納した表、上司のレコードを格納した表とみなすことです。 表別名を付けるだけなら簡単ですが、結合列を修飾する際に、みなした表別名を間違わないようにすることが重要です。 この例題の場合、部下表に「e」という別名を付けています。 どこで、それが分かるかというと、すべての選択肢において、入社年が今年かどうか判断する際に「DECODE(TO_CHAR(e.hire_date,'YYYY')……」としているところです。 FROM句に記述された別名「e」表が部下表ならば、部下レコードにおけるmanager_id（上司のID）が上司自身のID、つまり上司表としてみなされた「m」表のemployee_idと一致するかどうかが結合条件になります。
したがって、ON句にON e.manager_id = m.employee_idと記述されていない、選択肢bとcは不正解です。
残る選択肢aとdの違いは、外部結合がLEFTかRIGHTかです。
「上司の名前の代わりにFreshman、最高経営責任者の場合はCEOと表示する」ということは、上司のいない部下（最高責任者を部下というのはおかしいですが）も表示したいという要件であると判断できるので、「e」という別名を付けたFROM句に記述した表のレコードを全部出力するLEFT OUTER JOINが正解です（選択肢aは正解、dは不正解）。
SQL> --選択肢a
SQL> SELECT e.last_name,e.hire_date,
2 NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
'YYYY'),'Freshman','CEO'))
3 FROM employees e
4 LEFT OUTER JOIN employees m
5 ON e.manager_id = m.employee_id
6 WHERE e.department_id = 90;
LAST_NAME HIRE_DAT NVL(M.LAST_NAME,DECODE(TO
--------------- -------- -------------------------
De Haan 01-01-13 King
Kochhar 05-09-21 King
Allen 16-04-01 Freshman
King 03-06-17 CEO
SQL> --選択肢d
SQL> SELECT e.last_name,e.hire_date,
2 NVL(m.last_name,DECODE(TO_CHAR(e.hire_date,'YYYY'),TO_CHAR(SYSDATE,
'YYYY'),'Freshman','CEO'))
3 FROM employees e
4 RIGHT OUTER JOIN employees m
5 ON e.manager_id = m.employee_id
6 WHERE e.department_id = 90;
LAST_NAME HIRE_DAT NVL(M.LAST_NAME,DECODE(TO
--------------- -------- -------------------------
De Haan 01-01-13 King
Kochhar 05-09-21 King
12c SQL試験の嫌なところは、設問や選択肢のSQLが長くて複雑に感じるところです。特に結合は、2つ以上の表が出てくるだけでなく、その表名や列名が長くて、読むだけでうんざりします。 自己結合はEMPLOYEES表を使用した設問が出題されますが、その他はSH（Sales History）やOE（Order Entry）スキーマの表が使用されることが多いです。無償版のOracleを使って勉強している人は、長い表名や列名に慣れておくといいですよ。
