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

Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！（参考記事：「Oracle 10g対応のORACLE MASTERとは？」）

[有限会社 G.F.インフィニティ，Project - ∞]

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

ORACLE MASTER Bronze　SQL基礎I 講座　各回のインデックス

第1回　Oracleで使うSQLの基礎を学ぶ

第2回　SQL問い合わせによる行の制限とソート

第3回　SQLで使える関数の基礎知識

第4回　SQLの関数を使いこなす

第5回　SQLの関数でデータを集計する

第6回　SQLで複数の表からデータを取り出す

第7回　SQLの外部結合でデータを取り出す

第8回　SELECT文中のSELECT文、副問い合わせ

第9回　副問い合わせの構文を覚える

第10回　SQLでデータを自在に操作する

第11回　SQLで表のデータを制限する

最終回　SQLで便利なオブジェクトを使いこなす

理解しておきたいこと

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

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

2．外部結合

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

左側外部結合	内部結合結果に加え、LEFT OUTER JOIN句の左側の表（FROM表）のすべての行を戻す
右側外部結合	内部結合結果に加え、RIGHT OUTER JOIN句の右側の表（JOIN表）のすべての行を戻す
完全外部結合	内部結合結果に加え、FROM表とJOIN表のすべての行を戻す

　それぞれの結合構文は以下のとおりです。これらは内部結合同様、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句を使用することができます。

問題

■問題1

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

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

正解：a、c

■解説

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

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

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

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

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

■問題2

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

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

正解：d

■解説

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

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

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

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

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

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

■問題3

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

従業員表

EMPLOYEE_ID	従業員表の主キー
EMPLOYEE_NAME
DEPARTMENT_ID	部門表のDEPARTMENT_IDを参照する外部キー

部門表

DEPARTMENT_ID	従業員表の主キー
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID	ロケーション表のLOCATION_IDを参照する外部キー

ロケーション表

LOCATION_ID	ロケーション表の主キー
STREET_ADDRESS
CITY

所属する従業員が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

■解説

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

EMPLOYEES表（従業員表）

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

DEPARTMENTS表（部門表）

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

LOCATIONS表（ロケーション表）

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．ロケーションが不明な部門も結果に含める

■選択肢e：

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

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．歩合給をもらっていない従業員を表示する

「次回」へ

「ORACLE MASTER Bronze　SQL基礎I 講座」バックナンバー

• SQLで便利なオブジェクトを使いこなす
• SQLで表のデータを制限する
• SQLでデータを自在に操作する
• 副問い合わせの構文を覚える
• SELECT文中のSELECT文、副問い合わせ
• SQLの外部結合でデータを取り出す
• SQLで複数の表からデータを取り出す
• SQLの関数でデータを集計する
• SQLの関数を使いこなす
• SQLで使える関数の基礎知識
• SQL問い合わせによる行の制限とソート
• Oracleで使うSQLの基礎を学ぶ