SQLの外部結合でデータを取り出す：ORACLE MASTER Bronze　SQL基礎I 講座（7）

　外部結合が必要となるのは、通常、結合列にNULL値が含まれている行を戻したい場合や外部キーとして使用されていない行を戻したい場合です。

完全外部結合は、結合条件に一致しないデータのうち、両方の表のデータを取得したい場合に使用します（正解d）。

　そのほかの選択肢の不正解の理由は次のとおりです。

■選択肢a：1つの表だけの一致しない行を戻したい場合は、左側外部結合または右側外部結合を使用します。

■選択肢b：NOT NULL設定をしているかどうかは関係がありません。外部結合は、結合条件に一致しない行も戻す結合です。

■選択肢c：両方の表に一致する行のみを戻す結合は内部結合です。外部結合は内部結合結果に加え、一致しない行も戻す結合です。

■問題3

次の従業員表（EMPLOYEES）と部門表（DEPARTMENTS）、ロケーション表（LOCATIONS）の定義を確認してください。

所属する従業員が1人もいない部門や、ロケーションが不明な部門も表示する文を選択しなさい。ただし、部門番号が不明な従業員は表示しません。

    a．
      SELECT employee_id, employee_name, d.department_id,
        department_name, city
      FROM employees e
      RIGHT OUTER JOIN departments d
      ON (e.department_id = d.department_id)
      LEFT OUTER JOIN locations l
      ON (d.location_id = l.location_id);
	  
    b．
      SELECT employee_id, employee_name, d.department_id,
        department_name, city
      FROM employees e
      LEFT OUTER JOIN departments d
      ON (e.department_id = d.department_id)
      RIGHT OUTER JOIN locations l
      ON (d.location_id = l.location_id);
	  			
    c．
      SELECT employee_id, employee_name, d.department_id,
        department_name, city
      FROM employees e
      LEFT OUTER JOIN departments d
      ON (e.department_id = d.department_id)
      LEFT OUTER JOIN locations l
      ON (d.location_id = l.location_id);
			
    d．
      SELECT employee_id, employee_name, d.department_id,
        department_name, city
      FROM employees e
      RIGHT OUTER JOIN departments d
      ON (e.department_id = d.department_id)
      RIGHT OUTER JOIN locations l
      ON (d.location_id = l.location_id);
			
    e．
      SELECT employee_id, employee_name, d.department_id,
        department_name, city
      FROM employees e
      FULL OUTER JOIN departments d
      ON (e.department_id = d.department_id)
      FULL OUTER JOIN locations l
      ON (d.location_id = l.location_id);

正解：a

■解説

　次のようなパターンで考えてみると分かりやすいでしょう。

EMPLOYEE_ID EMPLOYEE_NAME     DEPARTMENT_ID
----------- ----------------- -------------
        100 Steven King                  10
        101 Neena Kochhar                20
        102 Lex De Haan                  30
        103 Alexander Hunold

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- --------------- ---------- -----------
           10 Administration         200        1700
           20 Marketing              201        1800
           30 Purchasing             114
           40 Human Resources        203        2400

LOCATION_ID STREET_ADDRESS       CITY
----------- -------------------- ----------
       1700 2004 Charade Rd      Seattle
       1800 147 Spadina Ave      Toronto
       2400 8204 Arthur St       London

　出題の状況を満たすには、次のような結果が必要です。

1. 部門表の40の部門は、所属する従業員が存在しない（従業員表から参照されていない）レコードですが、結果に含めます。
2. 部門表の30の部門はロケーションが不明ですが、結果に含めます。
3. 従業員表の103の従業員は部門番号がNULLなため、結果に含めません。

　1と3を満たすには、従業員表と部門表を結合し、部門表のすべての行を使用します（右側外部結合）。次のような文で取得できます。

SQL> SELECT employee_id, d.department_id, d.department_name
  2  FROM   employees e
  3  RIGHT OUTER JOIN departments d
  4  ON (e.department_id = d.department_id);
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ---------------
        100            10 Administration
        101            20 Marketing
        102            30 Purchasing
                       40 Human Resources

　2を満たすには、部門表とロケーション表を結合し、部門表のすべての行を使用します（左側外部結合）。次のような文で取得できます。

SQL> SELECT department_id, l.location_id, l.city
  2  FROM   departments d
  3  LEFT OUTER JOIN locations l
  4  ON (d.location_id = l.location_id);
DEPARTMENT_ID LOCATION_ID CITY
------------- ----------- ----------
           10        1700 Seattle
           20        1800 Toronto
           40        2400 London
           30

　この2つの問い合わせをまとめたものが正解aの文です。

SQL> SELECT employee_id, employee_name, d.department_id,
		   department_name, city
  2  FROM employees e
  3  RIGHT OUTER JOIN departments d
  4  ON (e.department_id = d.department_id)
  5  LEFT OUTER JOIN locations l
  6  ON (d.location_id = l.location_id);
EMPLOYEE_ID EMPLOYEE_NAME   DEPARTMENT_ID DEPARTMENT_NAME CITY
----------- --------------- ------------- --------------- ----------
        100 Steven King                10 Administration  Seattle
        101 Neena Kochhar              20 Marketing       Toronto
        102 Lex De Haan                30 Purchasing
                                       40 Human Resources London

　そのほかの選択肢の不正解の理由は次のとおりです。

■選択肢b：

従業員表と部門表を左側外部結合した結果に、ロケーション表を右側外部結合しています。つまり、次の要素を満たしていません。

■選択肢c：

従業員表と部門表が左側外部結合になっているので、条件に一致しない従業員表のデータを戻し、部門表のデータは戻しません。つまり、次の要素を満たしていません。

■選択肢d：

ロケーション表が右側外部結合になっているので、次の要素を満たしていません。

■選択肢e：

すべての表が完全外部結合になっているので、余計なレコードも表示されます。つまり、次の要素を満たしていません。

まとめ

　前回と今回の2回にわたって、複数の表からデータを表示する方法について解説しました。次の内容をチェックしておきましょう。

• 等価結合は等しい値で結合するときに使用し、非等価結合は値の範囲で結合するときに使用する
• 等価結合はn個の表をn-1個の結合条件で結合する
• NATURAL JOIN句は同じ名前の列すべてを結合キーとし、USING句は一部の列だけを結合キーとする。どちらも等価結合のみ
• ON句を使用すると任意の結合条件を記述できる
• 外部結合は結合キーがNULL値であるなど、結合できない結果も含めて出力する
• 左側外部結合はFROM表に含まれるすべての行を出力する
• 右側外部結合はJOIN表に含まれるすべての行を出力する
• 完全外部結合はFROM表とJOIN表に含まれるすべての行を出力する

宿題

　次回は、「副問い合わせを使用した問い合わせの解決」を確認します。次の宿題を解いておいてください。

1つのSELECT文で結果を表示するため、副問い合わせまたは結合を使用する必要があるタスクを2つ選択しなさい。