[SQL] 副問合せ

連載 [第5回] :

ORACLE MASTER Bronze Oracle Database 12cを何が何でも取得しよう！！

2017年2月7日(火)

ORACLE MASTERの取得を目指した連載の第5回は、副問合せについて学ぶ。

とうとうSQL文の学習は今回の第5回で最後となります。ここまでの学習で、問題を解く際にはポイントを絞って考えることや、実機検証の重要性が分かってきたのではないでしょうか。さて、今回扱うテーマは「副問合せ」になります。副問合せとは、SQL文の中にさらにSQL文が組み込まれている入れ子のような構文になります。どんな時に使うのでしょうか？　まずは使用例を紹介しましょう。以下のような情報を確認する必要があると仮定してください。

「従業員の中で一番給与をもらっている人は誰か？」

この条件を満たす情報を得るためには、以下の2つのステップが必要になります。

（1）従業員の給与の中で一番の給与額を求める
（2）一番の給与額情報を元に、条件に一致する従業員の名前を検索する

では、この条件をこれまで使用してきた従業員表（EMPLOYEES）を使ってSQL文に直します。そしてさらに副問合せの構文に変えてみます。

副問合せの構文の考え方

上記のように2つのステップが必要だった情報も、副問合せを使用するとSQL文を1つにまとめることができます。イメージは掴めましたでしょうか？　では早速本題に移っていきましょう！

SQL文の中に別のSELECT文を埋め込み条件を指定する

使用する表について

今回使用する従業員表（EMPLOYEES）と部門表（DEPARTMENTS）の定義情報です。しっかりと確認して問題にチャレンジしてください。

今回使用する従業員表（EMPLOYEES）

今回使用する部門表（DEPARTMENTS）

副問合せの特徴・使い方

冒頭の例で、副問合せのイメージは確認できたと思います。早速、例題を解きながら副問合せの基本的な使い方やルールについて学んでいきましょう。

問題1

副問合せについて正しい記述を示しているのはどれですか。

（a）副問合せ内にGROUP BY句とORDER BY句を使用することができる
（b）副問合せ内に副問合せを指定できるのは3つまでである
（c）副問合せの結果は必ず1行を戻す必要がある
（d）HAVING句に副問合せを使用することはできない
（e）通常、実行順序は副問合せを実施してから主問合せの順である
（f）副問合せはカッコで囲み必ず主問合せの比較条件の右側に配置する

○×問題ですね。このような問題が出たら、必ず点を取れるようにしておきましょう。順番に解説していきます。副問合せ内にはグループ関数であるGROUP BY句やHAVING句を使用することはできますが、ORDER BY句を使用することはできません。したがって（a）は誤りです。（b）はネストできる数ですが、副問合せのネストは最大で255レベルまで可能なので、これも誤りとなります。

副問合せには1行のみを戻すものと複数行を戻すものがあり、1行のみを戻す問合せを単一行副問合せ、複数行を戻す問合せを複数行副問合せといいます。詳細は後ほど説明しますが、選択肢（c）は「必ず1行を戻す必要がある」と記載されているので誤りです。

副問合せが使える場所はWHERE句だけではなく、FROM句やHAVING句など、色々なところで使用することができるので選択肢（d）も誤りですね。選択肢（e）は、[冒頭]で紹介したSQL文も思い出して頂ければ特に誤りがないことが分かるかと思います。選択肢（f）は、前半部分の「カッコで囲む」という部分には問題ありません。しかし後半部分の「必ず主問合せの比較条件の右側に配置する」という部分が間違っています。通常はSQL文を見やすくするために副問合せの配置は右側に置きますが、必須ではありません。以上のことより正解は（e）となります。

では、続きまして、副問合せの種類である単一行副問合せと複数行副問合せについて詳しくみていきましょう。

単一行副問合せ

単一行副問合せでは、副問合せ内で実行した結果は1行のみを戻します。副問合せで得られた結果を用いて、主問合せ側のWEHRE句で比較します。比較には単一行演算子（=、>、>=、<、<=、<>など）を使用します。仮に単一行副問合せにおいて、副問合せの結果が複数行戻ってきたらどうなるのでしょうか？　以下の図をご覧ください（冒頭のSQL文を少し変えたものです）。

WHERE句に複数行副問合せを使用すると……

副問合せ側で複数の結果が戻ってきてしまったら、どの値をイコールとして代入すればよいか分からなくなりますよね。実際に単一行演算子を使用して複数の値が戻ってきた場合、「ORA-01427: 単一行副問合せにより2つ以上の行が戻されます」というエラーが発生しますのでご注意ください。

では、単一行副問合せの問題をやってみましょう！

問題2

従業員表（EMPLOYEES）の各部門に所属する人の最高給与が、部門（DEPARTMENT_ID）の80番の平均給与より高い部門を確認したいと考えています。条件を満たすSQL文はどれになりますか。

（a）SELECT department_id, max(salary) FROM employees
　　WHERE department_id = 80
　　AND salary > (SELECT AVG(salary) FROM employees)
　　GROUP BY department_id;
（b）SELECT department_id, MAX(salary) FROM employees
　　GROUP BY department_id
　　HAVING MAX(salary) > (SELECT AVG(salary) FROM employees
　　WHERE department_id = 80);
（c）SELECT department_id, MAX(salary) FROM employees
　　GROUP BY department_id
　　HAVING MAX(salary) > (SELECT MAX(salary) FROM employees)
　　AND department_id = 80;
（d）SELECT department_id, AVG(salary) FROM employees
　　GROUP BY department_id
　　HAVING AVG(salary) > (SELECT MAX(salary) FROM employees
　　WHERE department_id=80);

選択肢を見ると、副問合せと一緒にグループ関数も使用していますね。グループ関数を忘れてしまった方は、第3回の講義をもう一度しっかり確認しましょう！　さて、副問合せの問題では、「何」と「何」を比較するのかをしっかりと把握することが重要です。問題文を見てみると「各部門に所属する人の最高給与」と「部門ID（DEPARTMENT_ID）が80番の部署の平均給与」の比較であることが分かります。後はこの条件をSQL文に変換してみればよいのです。各部門の最高給与は、MAX関数を使えば求められます。部門IDが80番の部署の平均給与についてはAVG関数を使い、WHERE句の指定で80番に絞り込めばOKです。

では選択肢をみていきましょう。（a）の副問合せを見てみると、従業員全体の平均給与を求めて、それよりも高い給与の人という比較を行っていますので、誤りであることが分かります。次に選択肢（b）を見てみましょう。副問合せの内容は、部門番号80番の平均給与を求めています。主問合せでは各部門の最高給与を求める内容が書かれており、結果を絞り込む条件としてHAVING句で副問合せの内容を比較しています。これが正解ですね！

念のため他の2つも確認しておきます。選択肢（c）の副問合せは全従業員の中から最高給与を求めていますので、誤りです。選択肢（d）は副問合せが部門番号80番に所属する人の最高給与で、主問合せが各部門の平均給与を求める内容となっているので、こちらも誤りです。

いかがでしょうか。いろんな関数が出てきたり、SQL文がネストされていたりするので複雑そうに見えたかもしれませんが、副問合せにおいてもやはり見るべきポイントを押さえて学習を進めることが重要ということがお分かりいただけたかと思います。

複数行副問合せ

続いて複数行副問合せです。複数行副問合せは、文字どおり副問合せの結果が2行以上を戻すことが可能な問合せになります。比較のために使用する演算子が単一行副問合せとは異なり、IN、ANY、ALLといった複数行比較演算子を使用することを覚えておきましょう。では問題を解きながら複数行比較演算子について詳しく見ていきます。

問題3

従業員表(EMPLOYEES)に以下のデータが格納されています。

従業員表（EMPLOYEES）のデータ

上記のデータとDEPARTMENTS表から、JOB_IDがADから始まる従業員が所属している部門番号（DEPARTMENT_ID）と部門名（DEPARTMENT_NAME）を表示するSQL文を2つ選択してください。

（a）SELECT department_id, department_name FROM departments
　　WHERE department_id = (SELECT department_id FROM employees
　　WHERE job_id LIKE 'AD%');
（b）SELECT department_id, department_namae FROM departments
　　WHERE department_id IN (SELECT department_id FROM employees
　　WHERE job_id LIKE 'AD%');
（c）SELECT department_id, department_name FROM departments
　　WHERE department_id ANY (SELECT department_id FROM employees
　　WHERE job_id LIKE 'AD%');
（d）SELECT department_id, department_name FROM departments
　　WHERE department_id =ANY (SELECT department_id FROM employees
　　WHERE job_id LIKE 'AD%');
（e）SELECT department_id, department_name FROM departments
　　WHERE department_id =ALL (SELECT department_id FROM employees
　　WHERE job_id LIKE 'AD%');

それでは解説をしていきましょう。すべての選択肢において副問合せ内でADから始まるJOB_IDの部門番号（DEPARTMENT_ID）を主問合せ側に戻していることが分かります。ADから始まるJOB_IDは「AD_PRES」「AD_VP」「AD_ASST」の3種類になり、それぞれに該当する部門番号（DEPRATMENT_ID）は「10」と「90」になります。よく分からなければ副問合せ部分だけSQL文を切り出し、SELECT句にJOB_ID列を追加して実際にSQL文を実行して確認してみましょう！

2行以上の値を主問合せ側に渡すので、選択肢（a）のようにイコール（=）の指定ではエラーとなります。選択肢（b）のINですが、内容的に同じ意味合いで展開すると部門番号が「10」or「90」となります。ADから始まるJOB_IDの部門番号「10」あるいは「90」の部門番号と部門名を表示するSQL文となるので、選択肢（b）は正しいです。選択肢（c）のANYも複数行比較演算子になりますが、使用の際にはANYの前に単一行比較演算子で学習した=、>、<、<>などの指定が必要ですので、構文が間違っています。選択肢（d）の=ANYは、副問合せ結果の「どれか」とイコールという意味になるため、選択肢（b）のINと同じ意味となり正しい内容です。選択肢（e）の=ALLは「すべて」とイコールという意味になり「10」and「90」となるため誤りです。したがって正解は（b）と（d）になります。

では最後にもう1問やってみましょう。

問題4

従業員表(EMPLOYEES)に以下のデータが格納されています。

従業員表（EMPLOYEES）のデータ

以下のSQL文を実行した結果を示している選択肢はどれか。正しいものを選んでください。

SELECT employee_id, last_name, salary, department_id FROM employees
WHERE salary >ALL (SELECT AVG(salary) FROM employees
　　　　　　　　　WHERE department_id IN (20,110)
　　　　　　　　　GROUP BY department_id);

（a）部門「20」または「110」の平均給与よりも、給与が多い従業員情報を表示する
（b）部門「20」または「110」の平均給与よりも、給与が少ない従業員情報を表示する
（c）部門「20」および「110」の平均給与よりも、給与が少ない従業員情報を表示する
（d）部門「20」および「110」の平均給与よりも、給与が多い従業員情報を表示する
（e）エラーが発生する

まず副問合せ内のSQL文を実行した内容がどのような結果になるか確認してみましょう。以下をご覧ください。

問題4の副問合せを実行した場合のイメージ

後は>ALLの指定によってこの2つの値（9500、10150）がどのように使われるかを考えればよいわけですね。>ALLは副問合せから戻された「すべての値よりも大きい」という意味になりますので、「最大値より大きい」と置き換えることができます。今回は9500と10150の値が戻されているので、2つのうちの最大値すなわち「10150よりも大きい」という条件になります。さぁ、ここまで確認ができたらあとは正解の選択肢を選ぶだけです。

（a）の選択肢は部門「20」または「110」の平均給与よりも給与が多い従業員情報とあります。平均給与9500と10150 のどちらか一方より多い、つまり2つのうち最小値より大きいものが条件となるので、誤りとなります。選択肢（b）は部門「20」または「110」の平均給与よりも給与が少ない、つまり最大値より小さいという意味に置き換えることができるため、これも誤りです。次に選択肢（c）と（d）を確認してみましょう。どちらも部門「20」および「110」と記載されています。選択肢（c）は部門「20」および「110」の平均給与よりも給与が少ないと記載があるので、2つの平均給与9500と10150の両方よりも少ない、つまり最小値よりも小さいという解釈となるため誤りです。

よって、部門「20」および「110」の平均給与よりも給与が多いとある（d）の選択肢が正しい答えとなります。言い換えれば、最大値より大きいということを意味します。これは、>ALLの説明として適切なので、正解は（d）となります。

今回の問題の選択肢をまとめてみると、

選択肢 (a) は >ANY の説明
選択肢 (b) は <ANY の説明
選択肢 (c) は <ALL の説明
選択肢 (d) は >ALL の説明

となります。

複数列副問合せ

最後に複数列副問合せについて少し触れておきます。これまでの副問合せは戻される列は1つのみでしたが、副問合せは複数列を戻すことも可能です。以下の実行例を見てください。

複数列副問合せの例。従業員132番の上司ID（manager_id）とその所属部署名（department_name）を表示

上記のように主問合せ側で受け取る条件の列（ここではdepartment_idとmanager_id）をカッコで囲み、副問合せ側から戻される列の値とペアになるように記述します。実機操作でも確認しておいてください！

さて、12c SQL基礎［12c SQL］（試験番号：1Z0-061-JPN）取得に向けた連載は以上となります。今回扱うことができなかったDML（Data Manipulation Language）文の使用や、表の作成・制約の設定といったデータ定義言語なども試験範囲となっていますので、しっかり学習しておきましょう。試験範囲についてはOracle Universityの試験のサイトでご確認頂きますようお願いいたします。1Z0-061-JPN試験の試験範囲はこちらの「テスト内容チェックリスト」タブにございます。

これまでの解説と重複しますが、見るべきポイントをしっかりと把握し、必ず実機にてSQL文を書いて学習してください。これが合格への一番の近道です！　頑張りましょう！！

次回からは、Bronze資格取得のために必要となる、もう一つの試験「Bronze DBA 12c（試験番号：1Z0-065-JPN）」に焦点をあてて解説をしていきます。それではまた次回お会いしましょう～

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

著者

野村達也

この著者の記事一覧この著者の
記事一覧

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

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