Tweet

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

有限会社 G.F.インフィニティ （Project - ∞）
2005/11/11

　前回に引き続き、複数の表からデータを表示する方法について学びます。前回「SQLで複数の表からデータを取り出す」で、等価結合と非等価結合、内部結合を紹介しました。今回は外部結合を学びましょう。

■理解しておきたいこと

1．確認しておきたい内容

• 外部結合による、結合条件に一致しないデータの表示

2．外部結合

　外部結合は、結合条件を満たさないデータも含めて戻します。外部結合では、結合構文の違いによって、以下の結合タイプが使用できます。

　それぞれの結合構文は以下のとおりです。これらは内部結合同様、ANSIで規格化され、SQL1999構文と呼ばれます。

・左側外部結合

　SELECT 列名リスト FROM 表名1 LEFT OUTER JOIN 表名2 ON(結合条件);

例：
SQL> SELECT e.empno, e.ename, d.deptno, d.dname FROM dept d
     LEFT OUTER JOIN emp e
  2  ON     (e.deptno = d.deptno);
      
     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
‥‥
      7698 BLAKE              30 SALES
      7654 MARTIN             30 SALES
      7900 JAMES              30 SALES
      7844 TURNER             30 SALES
      7521 WARD               30 SALES
                              40 OPERATIONS

　最後の1行が、外部結合によって左側の表（FROM表）であるdept表から戻された行です。

・右側外部結合

　SELECT 列名リスト FROM 表名1 RIGHT OUTER JOIN 表名2 ON(結合条件);

例：
SQL> SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e
     RIGHT OUTER JOIN dept d
  2  ON     (e.deptno = d.deptno);
      
     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
‥‥
      7698 BLAKE              30 SALES
      7654 MARTIN             30 SALES
      7900 JAMES              30 SALES
      7844 TURNER             30 SALES
      7521 WARD               30 SALES
                              40 OPERATIONS

　左側外部結合の例と結果は同じですが、最後の1行は、外部結合によって右側の表（JOIN表）であるdept表から戻された行です。

・完全外部結合

　SELECT 列名リスト FROM 表名1 FULL OUTER JOIN 表名2 ON(結合条件);

　デフォルトのemp表では完全外部結合のイメージがわきにくいので、1行追加してテストします。

例：
SQL> INSERT INTO emp(empno, ename, deptno) VALUES (8000,
     'TEST', null);
SQL> SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e
     FULL OUTER JOIN dept d
  2  ON     (e.deptno = d.deptno);
      
     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
‥‥
      7698 BLAKE              30 SALES
      7654 MARTIN             30 SALES
      7900 JAMES              30 SALES
      7844 TURNER             30 SALES
      7521 WARD               30 SALES
      8000 TEST
                              40 OPERATIONS

　最後の2行が、完全外部結合によってemp表とdept表から戻された行です。この例のように、結合キー列がNULL値の場合も結合はできません。その場合、外部結合を行うことで結果に含めることが可能になります。

　今回はON句を使用した構文と例を紹介しましたが、外部結合でもNATURAL JOIN句（自然結合）、USING句を使用することができます。

問題

外部結合を使用する処理を2つ選択しなさい。

a．両方にNULL値が含まれている
b．片方に含まれないデータも取得する必要がある
c．両方に含まれるデータも含まれないデータも取得する必要がある
d．両方に含まれるデータのみ取得する必要がある
e．主キーと外部キーの関係がある表からデータを取得する

正解：a、c

　前回の宿題にした問題です。外部結合は、結合条件に一致しないレコードも同時に出力するためのものです（正解c）。一致しない原因としては、結合キーにNULL値がある場合（正解a）、他方に含まれない結合キー値が存在する場合があります。

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

●選択肢b：外部結合には、結合条件に一致しないデータのうち片方の表のデータを取得する左側外部結合と右側外部結合、両方の表のデータを取得する完全外部結合がありますので、説明として不十分であると思われます。

●選択肢d：両方に含まれるデータのみ取得するには、内部結合を使用します。

●選択肢e：結合を行えるのは、表に主キーと外部キーの関係が存在する場合に限りません。非等価結合を使用すれば、同じ値が含まれていなくても結合することができます。また、非等価結合でも内部結合、外部結合を行うことができます。

どのようなときに完全外部結合を使用するとよいでしょうか。

a．1つの表のみに一致しない行が含まれている
b．NOT NULL設定されている表である
c．両方の表に一致する行が含まれている
d．両方の表に一致しない行が含まれている

正解：d

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

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

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

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

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

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

次の従業員表（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：
従業員表と部門表を左側外部結合した結果に、ロケーション表を右側外部結合しています。つまり、次の要素を満たしていません。

1．所属している従業員が存在しない部門も結果に含める
2．ロケーションが不明な部門も結果に含める

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

1．所属している従業員が存在しない部門も結果に含める
3．部門番号がNULLの従業員は結果に含めない

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

2．ロケーションが不明な部門も結果に含める

3．部門番号がNULLの従業員は結果に含めない

■まとめ

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

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

■宿題

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

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

a．従業員の名前とその上司の名前を同時に表示する
b．給与が2000ドル以上で部門20に属している従業員を表示する
c．指定した従業員の給与と同じ給与を受け取っている従業員を表示する
d．入社してから3カ月以上経過した従業員を表示する
e．歩合給をもらっていない従業員を表示する

＠IT自分戦略研究所 全記事インデックス

テクノロジ：Database
データベーススキル

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

ITトレメ	スキルアップに役立つ問題を無料で出題
ラーニングカレンダー	ITスキル研修4000件、最新情報の検索できます