検索
連載

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

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

Share
Tweet
LINE
Hatena

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

理解しておきたいこと

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

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

2．外部結合

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

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

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

・左側外部結合

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

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

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

・右側外部結合

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

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

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

・完全外部結合

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

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

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

　最後の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人もいない部門や、ロケーションが不明な部門も表示する文を選択しなさい。ただし、部門番号が不明な従業員は表示しません。

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

正解：a

■解説

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

EMPLOYEES表（従業員表）

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

DEPARTMENTS表（部門表）

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

LOCATIONS表（ロケーション表）

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

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

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

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

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

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

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

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

*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***

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

■選択肢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 講座」バックナンバー

Copyright © ITmedia, Inc. All Rights Reserved.

ページトップに戻る