連載

2012年8月6日

副問合せを使った複雑な条件指定：Webブラウザで気軽に学ぶ実践SQL講座（11）

前回はデータのレポート化や集計に役立つ便利な構文を紹介しました。今回は、複雑な条件を指定したい場合に役立つ「副問合せ」を紹介します（編集部）

[須々木尚子（すすきなおこ），日本オラクル] PC用表示関連情報

LINE

Hatena

複雑な条件指定

連載バックナンバー

Webブラウザで気軽に学ぶ実践SQL講座記事一覧

　これまでの連載では、条件に「列」と「値」を指定したシンプルな条件指定の方法を紹介してきました。

　例えば「給与が3000ドル以上（WHERE sal>3000）」や「部門番号が10（WHERE deptno=10）」のように、条件に「列」と「値」を指定して、必要なデータを取得することができます。

　しかし、業務システムで必要なSQL文を書くときには、もう少し複雑な条件を指定したい場面もあるでしょう。例えば、「ある社員と同じ部門に所属する社員は誰か」や「全社員の平均給与より給与が高い人は誰か」を調べたい場合には、どのように条件を指定すればよいでしょうか。

　今回は、このような場合に便利な「副問合せ」について説明します。

副問合せとは

　副問合せを使った複雑な条件指定の方法について、実際の例を使って考えてみましょう。

　「社員SCOTTと同じ部門に所属する社員は誰か」を調べるには、どのようなSQLを書けば良いのでしょうか。

　これまでに紹介した基本的な構文を使って考えると、以下のような手順で結果を得ることができます。

まず「社員SCOTTの部門番号は何か」を求める
SCOTTの社員番号を使って、SCOTTと同じ部門の社員の名前を求める

図1　2つのSQL文を順番に実行した例

　このように、2つのSQL文を順番に実行しても結果を得ることはできますが、どうにかして、これを1つのSQL文で実行することはできないでしょうか？

　ここで登場するのが、関連するSQL文を1つにまとめて記述できる構文「副問合せ」です。

　副問合せは、SELECT文を入れ子にして記述する構文です。下記の構文例のように、WHERE句に値を指定する代わりに、条件値を検索するためのSELECT文を記述します。

図2　副問合せのイメージ

　このとき、最終的に求めたい結果であるSQL文（先ほどの例では（2））を「主問合せ」、主問合せの結果を得るための副次的な問合せ（1）を、「副問合せ」と呼びます。

　副問合せは括弧で囲んで指定します。副問合せを実行すると、Oracle Databaseの内部的には、まず括弧内に指定されたSQL文（副問合せ）を実行し、その結果を使って主問合せを実行します。

　それでは、副問合せを使って、社員SCOTTと部門が同じ社員の名前を調べてみましょう。

SELECT ename, deptno
FROM   emp
WHERE  deptno = (SELECT deptno 
                 FROM   emp
                 WHERE  ename='SCOTT');

図3　「社員SCOTTと同じ部門に所属する社員は誰か」を調べる副問合せ

　上記の例のように、（1）と（2）2つのSQL文を入れ子にして記述し、結果を得ることができました。

複数列を使った副問合せ

　副問合せを使うことによって、より複雑な条件を記述することができるようになります。

　先ほどの例を応用し、「社員SCOTTと、部門と職種が同じ社員は誰か」を調べてみましょう。

　副問合せで使用する列は、1つでなくても構いません。「部門と職種」の両方を比較したい場合には、WHERE句に2列を指定します。

SELECT ename, deptno, job
FROM   emp
WHERE  (deptno,job)  = (SELECT deptno,job 
                        FROM   emp
                        WHERE  ename='SCOTT');

図4　「社員SCOTTと、部門と職種が同じ社員は誰か」を調べる副問合せ

複数行を戻す副問合せ

　次の例では、「給与が2900以上になる可能性のある職種についている人は誰か」を調べてみましょう。

　まず、副問合せで給与が2900以上の人がいる職種を調べ、次に主問合せで、同じ職種にいる人を調べます。

　これまでの例を参考にして考えると、次のようなSQL文を書くことができそうです。しかし、これを実行するとエラーになってしまいます。

SELECT ename, job, sal
FROM   emp
WHERE  job = (SELECT job
              FROM   emp
              WHERE  sal >= 2900);

図5　「単一行副問合せにより2つ以上の行が戻されます」というエラーが発生する例

　エラーメッセージを見ると、「単一行副問合せにより2つ以上の行が戻されます」というメッセージが表示されています。どうやら、「副問合せから戻る行（副問合せだけを実行した時の結果行）」が問題になっているようです。このような場合には、副問合せ部分だけを実行して、原因を考えてみましょう。

　比較するために、図3と図5のSQL文の、副問合せ部分だけを実行して比べてみましょう。

　すると、以下のように、図3の副問合せの結果が1行だけであるのに対して、図5の結果は4行であることが分かります。

図6　「図3と図5のSQL文の、副問合せ部分だけを実行したイメージ（クリックで拡大）

　つまり、図5のSQL文では、「jobが‘PRESIDENT’か’MANAGER’か’ANALYST’のいずれかである」という条件が指定されていることになります（この例では、たまたまANALYSTが2行検索されています）。

　このように、複数の値と比較する場合には、連載第4回の「比較演算子はイコールだけではない」で紹介したように、「＝」条件ではなく、「IN」条件を指定する必要がありましたね。

　そこで、次のように「＝」の代わりに「IN」条件を指定すると、正しく実行することができます。

図7　「給与が2900以上になる可能性のある職種についている人は誰か」を調べる副問合せ

　ちなみに、図2のように副問合せだけを実行した時の結果が1行である副問合せを「単一行副問合せ」、図7のように複数行である副問合せを「複数行副問合せ」と言います。

　複数行副問合せは、「＝」ではなく「IN」などの、複数の値を比較するための演算子を使う必要があることに注意してください。

グループ関数と組み合わせて使ってみよう

　副問合せは、グループ化した結果を条件値に使いたい場合にも便利です。

　例えば、「社員の中で最も給与が低い社員は誰か」を調べるには、どのように条件を指定すれば良いのでしょうか。

　次のような記述例を見かけることがありますが、これはエラーになってしまいます。

図8　「社員の中で最も給与が低い社員は誰か」を検索しようとして間違ったSQLの例（クリックで拡大）

　「給与の最小値」を条件にしたい場合には、副問合せで、給与の最小値（MIN(sal)）の値を調べ、その値と同じ給与の社員を検索する必要があります。

SELECT ename, sal
FROM   emp
WHERE  sal = (SELECT MIN(sal) 
              FROM   emp);

図9　「社員の中で最も給与が低い社員は誰か」を調べる副問合せ

GROUP BY句と組み合わせて使ってみよう

　ここまでの例を応用して、さらに複雑な副問合せを考えてみましょう。

　「部門の中で、最も平均給与が低い部門はどこか」を調べるには、どのような副問合せを記述すれば良いでしょうか。

　この例は少し複雑なので、次のように分解して考えてみましょう。

（1）副問合せで、部門ごとの平均給与の最低値を調べる

（2）主問合せで、副問合せで調べた給与が平均給与である部門を調べる

図10　「部門の中で、最も平均給与が低い部門はどこか」を調べるための副問合せのイメージ

　主問合せの条件にHAVING句を使用する点に注意してください。連載第9回の「特定のグループだけを検索してみよう」で説明した通り、グループ化した結果（平均給与）を条件にしたい場合には、HAVING句を使用します。

　（1）と（2）を組み合わせて、副問合せを実行してみましょう。結果が小数点になることを避けるため、次の例ではTRUNC関数を使って、小数以下を切り捨てています。

SELECT deptno, TRUNC(AVG(sal)) AVG_SAL
FROM   emp
GROUP BY deptno
HAVING AVG(sal) = (SELECT MIN(AVG(sal))
                   FROM   emp
                   GROUP BY deptno);

図11　「部門の中で、最も平均給与が低い部門はどこか」を調べる副問合せ

　このように、副問合せを使うと、今までより柔軟に検索条件を指定することができます。

　今回の連載では、サンプル表であるEMP表だけを使っていますが、例えば、部品表と製品表を基に、「ある製品と同じ部品を使っている製品は何か」といった検索も、副問合せを使えば簡単に実行することができます。ぜひ使ってみてください。

コラム――副問合せとNULL値

　副問合せの結果に「NULL」が含まれている場合には注意が必要です。例えば、次のような副問合せを考えてみましょう。

　まずは、社員の上司の名前を調べる副問合せを考えてみます。

　社員の上司を検索するには、副問合せで部下社員のマネージャ列（mgr）を調べ、そのマネージャ番号を社員番号に持つ上司社員を検索します。

※EMP表のマネージャ列（mgr）には、上司に当たる社員の社員番号（empno）が格納されています。詳しくは、連載第8回「複数の表からデータを取り出して表示させる（2）」の「図1　部下「BLAKE」と上司「KING」の関係を示す表」で説明しています。

　以下のような副問合せを実行すると、社員「SCOTT」の上司は正しく表示されますが、社員「KING」の上司は表示されません。実は、「KING」は社長であるため、上司が存在しない（マネージャ番号が「NULL」である）ためです。このように副問合せの結果が「NULL」である場合には、エラーではなく「データが見つかりません」というメッセージが表示されます。

図12　「社員の上司は誰か」を調べる副問合せ（クリックで拡大）

　もうひとつ例を考えてみましょう。「部下のいない（上司ではない）社員は誰か」を検索してみます。以下のように、まず副問合せでマネージャ番号（mgr）のリストを取得し、「マネージャ番号リストの中に自分の番号がない社員」を探せば良いことになります。

　しかし、図12のように実行すると、結果が表示されません。

※NOT IN条件は、「比較する値リストのすべての値と異なる」という条件です。

図13　「部下がいない社員は誰か」を調べる副問合せで結果が正しく表示されない例

　「部下がいない社員」が存在しない、のはおかしいですよね。（全員が誰かの上司ということになってしまいます。）

　これも、副問合せから返る「NULL」が原因です。

　この副問合せは複数行副問合せであるため、副問合せから複数の値が返りますが、その中に、KINGのマネージャ番号である「NULL」が入っています。「NULL」は「値が存在しない」ということであり、値と比較することができません。そのため、図12の実行例のように、結果としてデータが返されないのです。

　そこで、下記のように、副問合せに「NULL」が含まれないようにすれば、結果を表示することができます。

図14　「部下がいない社員は誰か」を調べる副問合せ

　このように、「あるはずのデータが検索されない」という場合には、副問合せの中に「NULL」データが入っていないかを確認してみると良いでしょう。

須々木尚子（すすきなおこ）

日本オラクルオラクルダイレクト所属。オンラインセミナーの講師や、お客様への提案、案件の支援などを担当。著書に「Oracle SQLクイズ」（翔泳社）があります。

「Webブラウザで気軽に学ぶ実践SQL講座」バックナンバー