PR

[SQL] 結合

2017年1月24日(火)
野村 達也
ORACLE MASTERの取得を目指した連載の第4回は、複数の表を扱う「結合」について学ぶ。

皆さん、自分専用Oracle Databaseで遊んで…… いやいや、しっかり勉強していますか? 冗談はさておき、SQL文の操作にもかなり慣れてきたのではないでしょうか。さて第4回目のテーマは複数の表を扱う「結合」について解説していきたいと思います。「結合」は若干SQL文が複雑になるため、どうしてもSQLアレルギーを起こしてしまう方も多いかもしれませんが、試験の出題頻度も高い分野なので非常に重要です。まずは「結合」のイメージをつかみましょう。以下の図をご覧ください。

結合のイメージ

結合のイメージ

従業員表と店舗表という2つの表を「店舗コード」列を使って繋げていますね。これが表の結合です。あとは結合を行う方法としてSQL文の構文を学べばOKというわけです。これまでと同様、見るべきポイントをしっかりと押さえると正解を導くことができるので、今回もしっかりと学習していきましょう!

複数の表を結合して表データを表示しよう

使用する表について

これまでは従業員表(EMPLOYEES)のみ使用していましたが、今回は部門表(DEPARTMENTS)も使用していきます。すべての問題に共通の表定義を以下に示しましたので、しっかりと確認してから問題にチャレンジしてください。

今回使用する従業員表(EMPLOYEES)

今回使用する従業員表(EMPLOYEES)

今回使用する部門表(DEPARTMENTS)

今回使用する部門表(DEPARTMENTS)

内部結合

結合とは、冒頭でもお話したように、複数の表を繋げるものだと思ってください。糊を使ってペタッと貼り付けるようなイメージでもいいでしょう。この「結合」にはタイプがあり、大別すると「内部結合」と「外部結合」に分けることができます。「内部結合」は冒頭の例のように、結合条件に一致する行だけを戻す結合のことを指し、「外部結合」は結合条件に一致しない行も含めて戻します。この違いを押さえつつ、まずは「内部結合」の構文を学んでいきましょう

問題1

※従業員表と部門表の構造を確認してください

従業員表(EMPLOYEES)と部門表(DEPARTMENTS)の2つの表を結合して情報を取得したいと考えています。エラーとならず正常に実行されるSQL文を2つ選んでください。

(a)SELECT e.last_name, d.department_name, d.department_id
  FROM employees e NATURAL JOIN departments d
  WHERE d.department_id > 100;
(b)SELECT e.last_name, d.department_name, department_id
  FROM employees e NATURAL JOIN departments d
  WHERE department_id > 100;
(c)SELECT e.last_name, d.department_name, d.department_id
  FROM employees e JOIN departments d
  USING(department_id)
  WHERE d.department_id > 100;
(d)SELECT e.last_name, d.department_name, department_id
  FROM employees e JOIN departments d
  USING(department_id)
  WHERE department_id > 100;
(e)SELECT e.last_name, d.department_name, department_id
  FROM employees e NATURAL JOIN departments d
  USING(department_id)
  WHERE department_id > 100;

さぁ、来ましたね。複雑そうなSQL文が(笑)。それでは解説です。選択肢を見てみると 「NATURAL JOIN」と「JOIN … USING」という構文があります。エラーにならないSQL文を選択するのですから、今回の問題は構文の使い方で間違っているものを排除すればいいということですね。このような間違い探しの問題は、試験ではよく問われます。まずはNATURAL JOINとJOIN USINGの構文を確認しておきましょう。

NATURAL JOINの構文と特徴

SELECT …
FROM 表名1 NATURAL [INNER] JOIN 表名2
・・・
特徴
・NATURAL JOIN は2つの表にある同じ名前を持つすべての列で等価結合を行う
・同じ名前の列でデータ型が異なるとエラーになる

USING句の構文と特徴

SELECT …
FROM 表名1 [INNER] JOIN 表名2
USING (結合する列名)
・・・
特徴
・USING句は結合する列を限定して等価結合を行う
・USING句は2つの表で結合列の名前が同じでデータ型が異なる場合にも使用できる

NATURAL JOIN は結合条件を明示的に指定する必要がなく、2つの表に存在する共通の列を使って自動的(自然)に結合していきます。まさにその名の通りNATURALですね。

一方、USINGは結合列を明示的に指定して結合処理を行います。特徴部分をしっかりと確認し、違いを押さえましょう。次は共通点の確認です。問題の選択肢を確認すると employees e や departments d のように表の後ろに別の名前がついています。これは表別名と呼びますがNATULAL JOIN、USINGのいずれも「結合に使用する結合列に、表名あるいは表別名で修飾してはいけない」というルールがあります。選択肢(c)のSQL文を使って、もう少し詳しく見てみましょう。

結合に使用する結合列は、表明/表別名で修飾できない

結合に使用する結合列は、表明/表別名で修飾できない

USINGで指定する場所だけでなく、SELECT文のいかなる句でも修飾をしてはいけません。これはNATURAL JOIN においても同じことが言えます。

さあ! 説明が長くなりましたが、2つの構文の異なる部分、共通する部分を確認できましたので、もう一度選択肢を確認してみましょう。(a)と(b)はNATURAL JOIN構文です。(a)は結合列である department_id が表別名で修飾されているのでエラーになります。次に(c)と(d)のUSING構文です。これも同じく(c)はUSINGで指定している結合列に対して表別名修飾があるのでエラーになりますね。最後に残った(e)ですが、NATURAL JOINとUSING構文は同時に使用できないことを覚えておいてください。よって、正解は(b)と(d)になります。

さて、もう少し掘り下げて確認することがあります。問題1はエラーにならないSQL文の選択なので(b)と(d)が正解ですが、この2つのSQL文の結果は必ず同じになるでしょうか? もう一度2つの構文の特徴部分をよく確認してみてください。NATURAL JOINは「2つの表にある同じ名前を持つすべての列で等価結合を行う」でしたね。department_id 列ばかり着目していましたが、仮に従業員表と部門表が以下だった場合のことを考えてみましょう。

問題1をさらに考察してみる

問題1をさらに考察してみる

選択肢(b)のSQL文を実行すると自動的にDEPARTMENT_IDだけではなく、MANAGER_ID列も結合列の対象となります。内部結合は結合条件に一致する行だけを戻す動作なのでDEPARTMENT_ID列と一致し、かつMANAGER_ID列とも一致する行を結合して返す動作となります。その結果、MANAGER_IDが205のみの行が条件を満たしますので、返される結果は1行となります。一方選択肢(d)のようにUSINGを使用した場合は、結合列をdepartment_idと明示的に指定しているので、返される結果は4行になります。このようにNATURAL JOINは結合する列を自動的に判別してくれる半面、自分が意図していない列値で結合処理が行われることがありますので、表の定義をしっかりと確認するなど注意が必要です。実際に実機でも試して動作確認をしてみてください。ここまでNATURAL JOINとUSINGの違いは確認できたでしょうか。

それでは次の問題にいきましょう!

問題2

※従業員表と部門表の構造を確認してください

部門番号(DEPARTMENT_ID)が90番以上の従業員番号(EMPLOYEE_ID)と従業員名(LAST_NAME)、部門番号、部門名(DEPARTMENT_NAME)を出力しようとしています。正常に実行されるSQL文を選択してください。

(a)SELECT employee_id, last_name, department_id, department_name
  FROM employees emp join departments dept
  ON (emp.department_id = dept.department_id)
  WHERE department_id >= 90;
(b)SELECT employee_id, last_name, dept.department_id, department_name
  FROM employees emp join departments dept
  ON (department_id)
  WHERE dept.department_id >= 90;
(c)SELECT employee_id, last_name, dept.department_id, department_name
  FROM employees emp join departments dept
  ON (emp.department_id = dept.department_id)
  WHERE dept.department_id >= 90;
(d)SELECT emp.employee_id, emp.last_name,
  dept.department_id, dept.department_name
  FROM employees emp join departments dept
  ON (department_id = department_id)
  WHERE dept.department_id >= 90;
(e)SELECT employee_id, last_name, department_id, department_name
  FROM employees join departments
  ON (department_id = department_id)
  WHERE department_id >= 90;

今度は新しくONというキーワードが出てきました。まずは構文の確認です。

ON句の構文と特徴

SELECT …
FROM 表名1 [INNER] JOIN 表名2
ON 表名1.列名 = 表名2.列名 ※表別名でも可
・・・
特徴
・ON句は結合する列を指定して結合を行う(非等価結合も可)
・ON句は2つの表で結合列名が同じ場合は表修飾が必要
・結合列名が異なっている場合、表修飾は省略可

ON句は、USING句と同様に結合する列を明示的に指定します。USING句との違いは、結合列名が同じ場合は表修飾をつける点です。選択肢の(d)や(e)のように「ON (department_id = department_id)」と書いてしまうと、どちらの表のdepartment_id 列を指定しているのか不明になってしまいます。また選択肢(b)の「ON (department_id)」は構文自体が間違っているので、残りは(a)と(c)となります。次に見るべきポイントはSELECT句やWHERE句で指定している列名になります。選択肢(a)で確認してみましょう。

ON句を使う際のポイントとなる列名

ON句を使う際のポイントとなる列名

上図の「^^^^」で示した department_id列は、従業員表、部門表の両方に存在しています。ON句を使用した場合、結合するそれぞれの表に共通した列名が存在している場合は表名、あるいは表別名で修飾が必要になることを覚えておいてください。適切な修飾がない場合「ORA-00918: 列の定義が未確定です。」といったエラーが発生します。以上のことから正解は(c)となります。

ON句の問題をさらにやりましょう。

問題3

※従業員表の構造を確認してください

従業員表の従業員番号(EMPLOYEE_ID)と上司番号(MANAGER_ID)には関連性があります。従業員番号(EMPLOYEE_ID)171番の従業員の姓(LAST_NAME)とその上司の情報を出力しようと考えています。目的を満たすSQL文を選択してください。

(a)SELECT e.employee_id "EMP ID", e.last_name "EMP NAME",
  m.employee_id "Manager ID", m.last_name "Manager Name"
  FROM employees e join employees m
  ON (e.employee_id=m.manager_id)
  AND e.employee_id=171;
(b)SELECT e.employee_id "EMP ID", e.last_name "EMP NAME",
  m.employee_id "Manager ID", m.last_name "Manager Name"
  FROM employees e join employees m
  ON (e.manager_id=m.manager_id)
  AND e.employee_id=171;
(c)SELECT e.employee_id "EMP ID", e.last_name "EMP NAME",
  m.employee_id "Manager ID", m.last_name "Manager Name"
  FROM employees e join employees m
  ON (e.employee_id=m.employee_id)
  AND e.employee_id=171;
(d)SELECT e.employee_id "EMP ID", e.last_name "EMP NAME",
  m.employee_id "Manager ID", m.last_name "Manager Name"
  FROM employees e join employees m
  ON (e.manager_id=m.employee_id)
  AND e.employee_id=171;

この問題は、出てくる表がEMPLOYEES表のみであることが確認できたかと思います。実は結合は1つの表だけで行うことも可能であり、この結合を「自己結合」といいます。自己結合は、1つの表内に関連性のある列が存在している場合に使用できます。今回の場合、EMPLOYEE_ID列とMANAGER_ID列が該当しますが、文章だけだと分かりづらいので簡単な表を使い説明しましょう。

自己結合の例

自己結合の例

いかがでしょうか。このように自己結合は、1つの表に関連性のある複数の列が含まれている場合使えるというわけです。では自己結合は実際にどのように記述するのでしょうか? 使用する表は1つですが、表別名を使ってあたかも2つの表があるように記述していきます。選択肢を見ると、FROM句はすべて「employees e join employees m」と記載されています。以下のように別の名前の表があると考えれば分かりやすいでしょう。

employees e:表別名 e は「部下表(部下用の表)」employees m:表別名 m は「上司表(上司用の表)」

後は問題文を満たす結合条件を考えていけばいいのです。従業員番号171番の部下の情報とその上司の情報を出したいわけですから、「部下表」のMANAGER_IDと「上司表」のEMPLOYEE_IDを結合すれば条件を満たしますね。以下のイメージ図も参考にしてください。

自己結合は内容が同じで別の名前の表があると考えてみよう

自己結合は内容が同じで別の名前の表があると考えてみよう

ON句に「(e.manager_id = m.employee_id)」と記載されているのは(d)になりますので、この(d)が正解になります。選択肢だけ見ていると複雑そうなSQL文に見えても、実行していることは実は単純だったと思いませんか?(笑) こちらも実機を使っていろいろと試してみるとよいでしょう。

外部結合

ここからは外部結合について確認していきます。外部結合は、内部結合で得られた結果に加えて結合条件に一致しない行も含めて戻すという動作になります。問題をやりながら、外部結合の構文について押さえていきましょう。

問題4

※従業員表と部門表の構造を確認してください

従業員表の従業員の姓(LAST_NAME)と所属する部門名(DEPARTMENT_NAME)を出力します。なお、まだ部門に配属されていない従業員の情報もあわせて出力する必要があります。

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e ________ departments d
ON (e.department_id = d.department_id);

目的の情報を得るためには空白部分にどの結合条件を記述する必要がありますか。適したものを選択してください。

(a)INNER JOIN
(b)RIGHT OUTER JOIN
(c)NATURAL JOIN
(d)LEFT OUTER JOIN
(e)FULL OUTER JOIN
(f)CROSS JOIN

外部結合は、以下の3つの構文を覚えてください。

・LEFT OUTER JOIN(左側外部結合)
 [解説]:構文の左側に配置した表のすべての行を取得します。
・RIGHT OUTER JOIN(右側外部結合)
 [解説]:構文の右側に配置した表のすべての行を取得します。
・FULL OUTER JOIN(完全外部結合)
 [解説]:構文の両側に配置した表のすべての行を取得します。

こちらも簡単な図を使って解説していきましょう。以下の図解では従業員表と部門表をDEPARTMENT_ID列で結合していますが、それぞれ異なる外部結合を使用することによる結果の違いを示しています。

LEFT OUTER JOIN(左側外部結合)

LEFT OUTER JOIN(左側外部結合)

RIGHT OUTER JOIN(右側外部結合)

RIGHT OUTER JOIN(右側外部結合)

FULL OUTER JOIN(完全外部結合)

FULL OUTER JOIN(完全外部結合)

いかがでしょう。ここまで確認したところで、もう一度問題文を見てみます。「部門に配属されていない従業員の情報もあわせて出力する」と記述があることから、従業員表の情報はすべて出す必要があることが分かりますね。あとはFROM句で指定してある表の指定を確認してください。左側の従業員表のデータをすべて表示するLEFT OUTER JOINが、要件を満たす外部結合となります。したがって解答は(d)になります。

さて、見慣れない選択肢(f)について少し解説しておきます。結合タイプは大別して「内部結合」「外部結合」と説明していましたが、厳密にはもう一種類、選択肢(f)の「クロス結合」(CROSS JOIN)があります。クロス結合は、デカルト積(2つの表に含まれる行の総組み合わせ)を結果として戻す結合方式です。例えばそれぞれ100行が格納されている2つの表をクロス結合すると、返される結果は10000行(100×100)になります。結合条件が無効、あるいは省略されている場合にも選択される結合で、パフォーマンス的に注意が必要な結合となりますがあわせて覚えておきましょう!

さぁ、今回最後の問題に挑戦です!

問題5

※従業員表の構造と部門表の構造を確認してください

従業員表の従業員番号(EMPLOYEE_ID)と上司番号(MANAGER_ID)には関連性があります。すべての従業員の姓(LAST_NAME)と入社日(HIRE_DATE)に加え、従業員の上司の姓と従業員が所属する部門名(DEPARTMENT_NAME)を表示する必要があります。空白部分に適したものを選択してください。

SELECT e.last_name, e.hire_date, m.last_name, d.department_name
FROM employees e 1)________________ employees m
ON (e.manager_id = m.employee_id)
2)_____________ departments d
ON (e.department_id = d.department_id);

(a)1) LEFT OUTER JOIN   2) LEFT OUTER JOIN
(b)1) RIGHT OUTER JOIN  2) RIGHT OUTER JOIN
(c)1) LEFT OUTER JOIN   2) RIGHT OUTER JOIN
(d)1) RIGHT OUTER JOIN  2) LEFT OUTER JOIN
(e)1) FULL OUTER JOIN   2) FULL OUTER JOIN

これまで学習してきた総まとめといった問題でしょうか。「複雑で分からないから…… 選択肢はもうこれでいいや!」というような発想をしては絶対にダメです! この問題のポイントは3つあります。1つ目は自己結合。これはすでに学習しましたね。2つ目は外部結合。これも先ほど学びました! そして最後、3つ目のポイントが3つの表の結合です。使用する表はEMPLOYEESとDEPARTMENTSの2つですが、自己結合を行っているので実質的には以下のように3つの表が結合されていると考えてください。

employees e:表別名 e 「部下表」
employees m:表別名 m 「上司表」
departments d:表別名 d 「部門表」

表が3つあるからといって、それを一気に結合するような特殊な結合があるわけではありません。表の結合というのは、必ず2つの表の間で処理を行います。ですから表が3つの場合は、まず2つの表で結合を行い、その結果と別の表を結合するといった手順になります。

問題文とSQL文を再度確認してみましょう。まずは自己結合にて従業員とその上司の情報を出す必要がありますが、そのSQL文の処理部分は以下の赤で囲まれたところです。

部下表と上司表の自己結合の処理部分

部下表と上司表の自己結合の処理部分

従業員には、新入社員のように上司がまだ割り振られていない人もいるかもしれません。問題文には「すべての従業員の~」と書いてありますので、上司がいない従業員も表示する必要があります。そのため左側のemployees e(部下表)のすべての表示が必要なため、 1)にはLEFT OUTER JOINを指定する必要があります。次はその結果をもって部門表を結合していきましょう。

今度は以下の枠の部分の処理です。

前段の結合の結果と部門表の結合を処理する部分

前段の結合の結果と部門表の結合を処理する部分

従業員の部門情報を結合するわけですが、同じように部門が割り当てられていない従業員もいるかもしれません。問題文には「すべての従業員の~」とあるわけですから、所属部門が決まってない従業員も表示する必要があります。よって 2)もLEFT OUTER JOINの指定が必要になるということですね。以上の結果から正解は(a)となります。

第4回はかなりボリュームがある内容となりましたが、結合は試験でもよく出題されます。複雑なSQL文が出てくるので「これでいいや……」と適当に選択しないようにしっかりと学習して臨んでください。結合の問題が出たら「やった! 点稼げる」という気持ちになるぐらい操作に慣れておきましょう!(笑)

さて、次回でSQL文の学習は最後になります。最後まで気を抜かずに頑張っていきましょう~。

* OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中の社名、商品名等は各社の商標または登録商標である場合があります。

日本オラクル株式会社 オラクルユニバーシティ研修部 シニアインストラクター

日本企業にて、某国産データベースのサポート業務を行った後、オラクルデータベース製品を扱う仕事に携わる。緊急トラブルや現地対応の業務、パフォーマンステストなどの構築作業を経て、日本オラクルへ入社。日本オラクルではオラクルユニバーシティ研修部に所属し、オラクルデータベース関連のコースを幅広く担当。また、各種ORACLE MASTER資格の試験対策も手がけている。業務外ではお酒(ワイン, 日本酒 etc..)をこよなく愛し、一度お店にいくと顔を覚えられるほどの酒豪でもある。

連載バックナンバー

データベース技術解説
第9回

[DBA] バックアップおよびリカバリ

2017/4/4
ORACLE MASTERの取得を目指した連載の第9回は、データベースのバックアップとリカバリについて学ぶ。
データベース技術解説
第8回

[DBA] データベース記憶域構造の管理

2017/3/23
ORACLE MASTERの取得を目指した連載の第8回は、データベースを構成するファイル群について学ぶ。
データベース技術解説
第7回

[DBA] ネットワークとインスタンスの管理

2017/3/7
ORACLE MASTERの取得を目指した連載の第7回は、リスナーを介したネットワーク経由の接続方法やインスタンスについて学ぶ。

Think IT会員サービス無料登録受付中

Think ITでは、より付加価値の高いコンテンツを会員サービスとして提供しています。会員登録を済ませてThink ITのWebサイトにログインすることでさまざまな限定特典を入手できるようになります。

Think IT会員サービスの概要とメリットをチェック

他にもこの記事が読まれています