Oracle University 講師が ORACLE MASTER 取得を目指している方に向けて試験トピックを解説するブログ連載講座。「ORACLE MASTER Silver SQL 2019」資格取得に向けた試験トピックについて解説します。

————————————————-

今回はORACLE MASTER Silver SQLの試験トピック「複数の表からのデータ表示 – 外部結合の使用 -」に関連する問題をご紹介いたします。ORACLE MASTER Silver SQL(Exam Number: 1Z0-071-JPN)の詳細につきましては以下URLをご参考ください。

Oracle Database SQL Exam Number: 1Z0-071

URL: https://education.oracle.com/ja/oracle-database-sql/pexam_1Z0-071

————————————————-

今回は表の結合方法の１つである外部結合について学習していくことにします。

それでは問題を確認してください。

(解説)

今回の問題は外部結合の内容になりますが、基本的な結合方法である内部結合については「第12回 複数の表からのデータ表示 – 自己結合の使用 -」で扱っていますのでそちらを確認した上で今回の内容を学習すると理解が進むと思います。ご参考にしてください。

さて、外部結合とはどのような結合方法なのか？まずは以下のイメージ図をご覧ください。

EMP表(従業員表)とDEPT表(部門表)の２つの表を結合していますが、結合キーであるDEPTNO列の値が一致して得られたデータに加えて結合キーが一致していないデータも表示しています。このような結合を外部結合と言います。上記イメージ図をSQL文で表現すると以下になります。

今回の例のように左側の表(EMP表)と右側の表(DPET表)の両方において、結合キーに一致しないデータも含めたすべてのデータを表示したい場合はFULL OUTER JOINと記述します。これを「完全外部結合」と言います。

JOIN句の左側の表(今回はEMP表)のデータのみ結合キーに一致しないデータも含めてすべて表示したい場合はLEFT OUTER JOINと記述します。これを「左側外部結合」と言います。

JOIN句の右側の表(今回はDEPT表)のデータのみ結合キーに一致しないデータも含めてすべて表示したい場合はRIGHT OUTER JOINと記述します。これを「右側外部結合」と言います。

外部結合がどのような結合処理を行うのか確認できたところで問題の選択肢をあらためて見ていきましょう。ここまでの知識で選択肢をかなり絞り込むことができます。

選択肢１は、EMP_TABLE表とDEPT_TABLE表に対して「結合列の値が一致する行と一致しない行の両方を表示する」と記述がありますので完全外部結合のことを説明しています。正しい内容ですね。

選択肢２は「・・一致しない行のみ表示」という部分に着目すると誤りであることが分かると思います。

選択肢３は内部結合の説明文になりますのでこちらも誤りです。

選択肢４の説明の中で「Oracle結合演算子(+)」と出てきてますが「これは何？」と思う方がいらっしゃると思いますので以下に詳しく説明しておきましょう。

  • Oracle構文による結合

リレーショナル・データベースを操作するために最適な言語であるSQLですが、かつてはSQL規格を統一した制度などがなく、各ベンダー毎に独自の構文などが使用されていました。このような背景からOracle Databaseでも表と表の結合処理にて独自構文を使用した記述の方法があります。現在は言語仕様の標準化が制定されており、制定された年によりSQL86, SQL89, SQL92, SQL1999 …といった名称で標準規格が作られています。

以下に構文の違いをサンプルのSQL文を用いて記載しますのでしっかりと確認しておいてください。

さて、問題の選択肢に戻ります。

外部結合処理をOracle構文で記述することはできますが完全外部結合だけはOracle構文では記述することができません。問題文のSQL文は完全外部結合を実行していましたよね。したがって選択肢４は誤りになります。

選択肢５ですが、外部結合の結果というのは、結合キー値が一致するデータ、つまり内部結合処理の結果と、結合キー値が一致しないデータを表示したものになりますので「・・内部結合によって返される行も含んだ結果」という文言に間違いはありません。正しい内容となります。

最後の選択肢６を確認します。「デカルト積」という用語が出てきましたね。表の結合処理にてデカルト積(直積とも言います)を生成するということは各表のデータを総当たりで結合する処理を指します。この結合処理のことをクロス結合といいます。例えば、表Aが10件、表Bが10件あった場合のデカルト積は100件ということになります。デカルト積は以下のような条件下で生成されます。

  • 結合条件が省略されている場合
  • 結合条件が正しく指定されていない場合

クロス結合によるデカルト積の生成は大量データを扱うことになることが多いためシステムに大きな負荷をかける場合があります。そのため使用する場合は、本当に必要な処理であるかを見極めることが重要です。

一般的には、大量のデータを生成したい場合や故意にシステム負荷を上げるといった場合などに使用されることがあるでしょう。こちらも標準規格の構文とOracle構文についてサンプルのSQL文を載せておきますので確認しておいてください。

デカルト積の生成は外部結合の結合処理とは異なることから選択肢６は誤りであることが分かるかと思います。

以上の結果から正解は１と５になります。

今回の問題に関するSQL文が実行できるように定義のサンプルと外部結合を使用した実行例を乗せておきますので是非活用してください。

— dept_table 作成 —
drop table dept_table;
create table dept_table(
        deptno          number(5)       primary key,
        dname           varchar2(20),
        loc                 varchar2(20)
);

— sample データ —
insert into dept_table values(10, ‘Administration’, ‘New York’);
insert into dept_table values(20, ‘Accounting’, ‘Boston’);
insert into dept_table values(30, ‘Sales’, ‘Chicago’);
insert into dept_table values(40, ‘Operations’, ‘Dallas’);
commit;


— emp_table 作成 —
drop table emp_table;
create table emp_table(
        empno           number(6)       primary key,
        ename           varchar2(20),
        mgr               number(6),
        deptno           number(5)       references dept_table(deptno)
);

— sample データ —
insert into emp_table values(100, ‘King’, NULL, 10);
insert into emp_table values(101, ‘Kochhar’,100, 20);
insert into emp_table values(102, ‘Allen’, 101, 20);
insert into emp_table values(103, ‘Ward’, 102, 30);
insert into emp_table values(104, ‘Landry’, 102, 30);
insert into emp_table values(105, ‘Ford’, 102, NULL);
commit;

今回は外部結合に関する学習を行いました。３つの外部結合の種類とOracle Databaseの独自構文の記述についても出てきましたが表示されるデータの違いをしっかり把握しておきましょう。特にOracle結合演算子である(+)の記号の使い方は勘違いしやすいので注意が必要です。念のためにおさらいしておきましょうね。

左側外部結合・・・Oracle結合演算子(＋)は右側の表に付ける　例) WHERE emp.deptno = dept.deptno(+)

右側外部結合・・・Oracle結合演算子(＋)は左側の表に付ける　例) WHERE emp.deptno(+) = dept.deptno

完全外部結合・・・Oracle結合演算子(＋)を使用した構文は存在しない

 サンプルの定義やデータも是非活用していただき、意図したデータを検索できるように学習を進めてください。

それでは今回の講義はこれまでとします。お疲れ様でした。

————————————————-

– ORACLE MASTER Silver SQL 2019 のご紹介

– ORACLE MASTER Silver SQL 2019 試験記事トピック一覧

————————————————-

その他の ORACLE MASTER 試験トピック解説講座シリーズ：

【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧

————————————————-