基本に戻ろう: 自己結合
参照元:Back to basics:self joins < Eddie Awad’s Blog
— beginning of translation —
この記事はOracle結合シリーズの中の一つです。
目次：
1.基本に戻ろう: クロス結合
2.基本に戻ろう: 内部結合
3.基本に戻ろう: 外部結合
4.基本に戻ろう: 等価結合と非等価結合
5.基本に戻ろう: 自己結合
6.基本に戻ろう: アンチジョインとセミジョイン
自己結合はその表自身を結合する方法です。自己結合する表はFROM句で２回(あるはそれ以上)指定され、それぞれの表名の後ろには、結合条件やSELECT句の列名を修飾する別名が続きます。
employee表を例にあげてみましょう。ある従業員の上司はその人自身も従業員の一人です。上司の行も部下の行も同じemployee表に格納されています。
ある従業員とその上司の情報を一緒に抽出するには、employee表自身を結合する必要があります。従って、それぞれの従業員が２つの別々の表に格納されているものとして扱います。次の例の問合せは自己結合を用いて、各従業員の名前とその上司の名前を戻しています。:
SELECT e.last_name employee, m.last_name manager
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id;
EMPLOYEE MANAGER
------------------------- -------------------------
Kochhar King
De Haan King
Hunold De Haan
Ernst Hunold
Austin Hunold
Pataballa Hunold
...
106 row(s) retrieved
しかし
SELECT COUNT (*)
FROM employees;
COUNT(*)
--------------------------------------
107
empleyee表には107件登録されているにもかかわらず、上記の例では106件しか返されていません。これはmanager_idに値が入っていない従業員がいるためです。自己内部結合を使用している場合、Oracleはそのような従業員の行を結果セットから除外します。上司がいない従業員も結果セットに含めるには外部結合を使えばいいのです。:
SELECT e.last_name employee, m.last_name manager
FROM employees e LEFT OUTER JOIN employees m
ON e.manager_id = m.employee_id;
EMPLOYEE MANAGER
------------------------- -------------------------
King
Kochhar King
De Haan King
Hunold De Haan
Ernst Hunold
Austin Hunold
Pataballa Hunold
...
107 row(s) retrieved
上記は自己等価結合の例です。一方、自己非等価結合を使わなくてはいけない場面もあるでしょう。例えば、あなたが部署対抗サッカー大会の試合編成を任されたとしましょう。参加する部署は以下の通りです。:
SELECT department_name
FROM departments
WHERE department_id IN (10, 20, 30, 40);
DEPARTMENT_NAME
------------------------------
Administration
Marketing
Purchasing
Human Resources
4 row(s) retrieved
各部署は他の３つの部署と１度だけ対戦することにしました。ということは、問合せの出力結果には「Administration, Marketing」という組み合わせと「Marketing, Administration」という組み合わせが両方表示されてはいけません。なぜなら、各部署が他の部署と２度対戦することになってしまうからです。この問題を解決するには次のように自己非等価結合を使用します:
SELECT d1.department_name dept1, d2.department_name dept2
FROM departments d1 INNER JOIN departments d2
ON d1.department_id < d2.department_id
WHERE d1.department_id IN (10, 20, 30, 40)
AND d2.department_id IN (10, 20, 30, 40);
DEPT1 DEPT2
------------------------------ ------------------------------
Administration Marketing
Administration Purchasing
Administration Human Resources
Marketing Purchasing
Marketing Human Resources
Purchasing Human Resources
6 row(s) retrieved
これで自己結合の説明は終わりです。
参考文献:
* Oracle Documentation
* Mastering Oracle SQL
— end of translation —
参考文献の１番目の日本語ページはこちら
２番目のほうは残念ながら日本語版は出版されてないようです。