高度な副問合せの構文：Webブラウザで気軽に学ぶ実践SQL講座（12）

前回は、複雑な条件を指定したい場合に役立つ「副問合せ」を紹介しました。今回は、副問合せを応用したさまざまな構文を紹介します（編集部）

» 2012年09月14日 00時00分公開

さらに高度な「副問合せ」の条件指定

連載バックナンバー

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

　連載第11回では、SQL文を入れ子にして条件指定をする構文である「副問い合せ」を紹介しました。今回は、副問合せを応用したさまざまな構文例をご紹介します。

　まずは、前回の復習として、「全社員の平均給与よりも、給与が高い社員は誰か」を調べてみましょう。副問合せで全社員の平均給与値を求め、それよりも給与額が高い社員を調べるには、以下のように指定すればよかったですね。

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

図1　「全社員の平均給与よりも、給与が高い社員は誰か」を調べる副問合せ

　では、次にもう少し複雑な例を考えてみましょう。

　「自分が所属する部門の平均給与よりも、給与が高い社員は誰か」を考えてみます。ポイントは、「自分が所属する部門」と比較しなければならない点です。

　今までの例（図1）では、「全社員の平均給与」という単一の値と比較すればよかったのに対して、この問合せでは、人によって比較する条件値が異なります。つまり、部門10に所属する人であれば部門10の平均給与と、部門20に所属する人であれば、部門20の平均給与と比較する必要があります。

　このような場合には、どのように条件を指定すればよいのでしょうか。

相関副問合せを使ってみよう

　「自分の所属する部門の平均給与よりも、給与が高い社員は誰か」を調べるための、1つ目の構文は「相関副問合せ」です。「相関副問合せ」とは、主問合せで処理したい行によって副問合せの条件を変えながら検索することができる構文です。

　通常の副問合せでは、Oracle Databaseはまず副問合せに記述されたSQLを実行して結果データを取得し、その値を使って主問合せを実行します。

　しかし、今回のように「自分が所属する部門の平均給与」と比較したい場合は、図2のイメージのように、社員ごとに条件（部門）を変えて副問合せを実行する必要があります。

図2　社員ごとに副問合せの条件（部門）を変えて条件値を取得するイメージ（クリックで拡大）

　図2のイメージを、1つのSQL文で表したのが図3です。図3のイメージのように、副問合せの条件に「主問合せで検索したい人（候補行）の所属する部門」を指定すればよいのです。候補行とは、主問合せで評価される行のことです。条件に一致した場合には出力結果となることから、結果の「候補」という意味で使われます。

図3　副問合せの条件に「その人の所属する部門」を指定するイメージ（クリックで拡大）

　このような条件指定をすることができるのが、「相関副問合せ」の構文です。相関副問合せでは、図4のように、副問合せの中に主問合せで使用する表の名前（別名）を指定することで、主問合せと副問合せの関係を記述します。

図4　「副問合せの条件で使用する部門は、主問合せの候補行の社員が所属する部門である」という条件を表したイメージ

　この例では、主問合せでも副問合せでも同じ「EMP」表を使っているので、比較するために、主問合せのEMP表を「o（外側を表すouterの頭文字）」、副問合せのEMP表を「i（内側を表すinnerの頭文字）」と、別名を付けて指定しています（別名は何でも構いません）。

　このように記述すると、「副問合せ（i）の条件で使用する部門番号は、主問合せ（o）の候補行の部門番号を使う」という条件を指定できます。これによって、社員ごとに、その人の所属する部門の平均給与と比較できるようになるのです。

　それでは、実行して確認してみましょう。

SELECT ename, deptno, sal
FROM   emp o
WHERE  sal > (SELECT AVG(sal)
              FROM   emp i
              GROUP BY deptno
              HAVING i.deptno = o.deptno );

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

　最後に、相関副問合せ実行時の動作についてまとめます。相関副問合せの構文を実行すると、Oracle Database内部では、以下のような処理が行われます。

主問合せに指定されたEMP表から、1行を選択（候補行）
その人が所属する部門番号を特定
その人が所属する部門番号を使って、副問合せを実行（所属する部門の平均給与を求める）
所属する部門の平均給与と、候補行となっている社員の給与を比較し、社員の給与の方が高ければ、検索結果として選択
表の次の候補行に対しても、上記1から4の処理を繰り返す

　このような処理が行われた結果、社員ごとに、その人の所属する部門の平均給与との比較が可能になります。今回のように「行によって異なる条件で副問合せを実行したい」という場合には、相関副問合せを使うと便利です。

インライン・ビューを使ってみよう

　「自分が所属する部門の平均給与よりも、給与が高い社員は誰か」を調べるためには、相関副問合せ以外にも「インライン・ビュー」を使用することもできます。「インライン・ビュー」とは、FROM句で副問合せを使用する構文です。

　社員の給与を「自分が所属する部門の平均給与」と比較するためには、「部門の平均給与」との比較が必要です。相関副問合せでは、これを副問合せに記述しましたが、図6のように、「部門の平均給与」を求めた結果データを表に見立てて処理を記述することもできます。

　（図6の「部門の平均給与」では、見やすいように小数以下を切り捨てて表示しています）

図6　「部門の平均給与」を表に見立て、結合するイメージ（クリックで拡大）

　それでは、図6に示した処理を、SQL文で記述してみましょう。

　「部門の平均給与」のデータセットを表のように扱うため、FROM句に部門の平均給与を求める副問合せを記述します。このようにFROM句に問合せを記述する構文を「インライン・ビュー」と呼びます。図7の例では、赤枠で囲まれた部分がインライン・ビューです。

SELECT  e.ename, e.deptno, e.sal
FROM    emp e 
JOIN    (SELECT deptno, AVG(sal) avg_sal
         FROM   emp
         GROUP BY deptno) i
ON      e.deptno = i.deptno
WHERE   e.sal > i.avg_sal;

図7　インライン・ビューを使って「自分の所属する部門の平均給与よりも、給与が高い社員は誰か」を調べる問合せ

　図7の例では、インライン・ビューに別名「i」を指定しています。つまり、「部門の平均給与」というデータセットに「i」という名前を付け、主問合せで表のように使えるようにしているのです。あとは主問合せで、EMP表の社員の給与とインライン・ビュー「i」を結合し、「i」の部門平均給与（AVG_SAL）よりも給与の高い社員を検索しています。

　なお、ここで、「AVG(sal)」に別名「avg_sal」を指定していることに注意してください。インライン・ビューで問合せ結果を表に見立てる際には、SELECT句で指定した列名が表の列名として扱われます。つまり、別名を指定しなければ、「deptno」と「AVG(sal)」がi表の列名となります。

　しかし、Oracle Databaseの命名規則上、括弧を含む列名を定義することはできません（エラーになります）。そのため、この例では「AVG(sal)」に別名「avg_sal」を指定しています。

　このように、問合せの中で使いたいデータセットを「インライン・ビュー」としてFROM句で定義し、主問合せで使うことで、複雑な問合せをシンプルに考えることができます。

相関副問合せの応用編　EXISTSを使ってみよう

　相関副問合せを使った構文の1つに、「EXISTS」という条件の指定方法があります。

　これは、副問合せに該当する行が存在するかどうかをテストできる構文です。例えば、フラグが「1」であるデータがあるかないかなど、副問合せで条件に一致するデータの存在有無を調べる際に便利です。

　EXISTS句は、副問合せで、結果行が存在すれば「真(TRUE)」、存在しなければ「偽(FALSE)」を戻します。それでは、EXISTS句を使った検索例を見てみましょう。

　例として、「部下が少なくとも一人はいる社員は誰か」という処理を考えてみましょう。過去の連載でも何度か紹介しましたが、EMP表のmgr列には、上司のempno番号が入っています。つまり自分のempnoが誰かのmgr列に入っていれば、その社員は上司です。

図8　EMPNO列とMGR列を使って部下がいるかを探すイメージ

　今回も、「自分のempno番号は誰かのmgr番号になっているか」のように、社員によって条件を変えなければならないため、相関副問合せを使用します。ただし、「存在有無」だけを調べればよいので、EXISTS句を使うことができます。EXISTS句の構文は少し特殊なので、次の実行例を見ながら考えてみましょう。

SELECT empno, ename
FROM   emp o
WHERE  EXISTS ( SELECT 'X'
                FROM   emp i
                WHERE  i.mgr = o.empno);

図9　EXISTS句を使って「部下少なくとも一人はいる社員は誰か」を検索する例

　図9の実行例で、副問合せのSELECT句に列名ではなく定数‘X’を指定していることに注意してください。EXISTS句では、一般的に列名ではなく定数‘X’を指定します。

　これは、EXISTS句では副問合せを実行した結果、行が戻されるかどうかのみが判定されるためです。つまり、戻る列値が何であっても問題ではない（値自体が処理で使われるわけではない）ため、特定の列名を指定する必要はありません。

　もちろん、通常の副問合せと同じように列名を指定することもできますが、定数を指定することで「存在有無の確認」であることをより明確に表現できます。また、指定する定数に特に決まりはありませんが、慣習的に‘X’を指定することが多いです。

　EXISTS句を使うことには、次の3つのメリットがあります。

　1つは構文が分かりやすいことです。値の存在チェックをする方法は、EXISTS句を使用する以外にもいくつかあります。例えば、EXISTS句の代わりにIN句を使用したり、COUNTで件数を調べたり、条件に一致するレコードに対してDISTINCTで重複行を排除したりする方法も考えられますし、実際にそのように記述されているSQL文を見ることもよくあります。しかし、このような書き方よりも、EXISTSの方が、構文としても「存在有無の確認」であることが分かりやすいでしょう。

　2つ目はパフォーマンスが良いことです。EXISTSは、一致する値が1つでも見つかれば、そこで「TRUE」と判断することができるため、チェック対象のデータが多い場合にも高速に処理できます。そのため、IN句で書いていたSQLをEXISTS句に変えただけで性能が大幅にアップしたという例もあります。

　3つ目は、「NOT EXISTS（値が存在しないことを判断）」を使う場合に、NULL値を考慮する必要がないことです。連載第11回のコラム「部下が一人もいない社員は誰か」を調べる例で紹介したように、NOT INを使う場合には、NULL値の扱いに気をつける必要がありました。

　これは、NOT INでは、主問合せで条件に指定された列値と、副問合せから得られた結果値とを比較して、「どれとも一致しない」という評価がなされるためです。列値とNULLは比較できないため、副問合せにNULLが含まれていた場合は比較できない（結果としてデータが検索されない）問題がありました。NOT EXISTSは副問合せの結果が「ある」か「ない」かを判断するだけで、値同士の比較を行うわけではありません。そのため、NULL値を考慮する必要がないのです。

　以上の理由から、値の存在チェックではEXISTS句を使うことをお勧めします。

インライン・ビューの応用編　トップN分析を使ってみよう

　最後に、インライン・ビューを応用した応用構文についても、1つ紹介しましょう。

　インライン・ビューを応用して、データの上位n件（または下位n件）を調べる「トップN分析」という構文を記述できます。トップN分析を使うと、例えば「売上の上位3個の製品は何か」や「在庫の少ない部品下位10個は何か」を簡単に調べることができます。

　それでは、トップN分析を使って、「給与を多くもらっている社員トップ3名は誰か」を調べてみましょう。トップ3名を調べるには、まずデータを並べ替えてから、上位3件を取り出す必要があります。

　そこで、インライン・ビューで社員データを給与順に並べ替え、主問合せでROWNUM 疑似列を使って上から3件のデータを表示するように制限します。ROWNUMは、問合せによって戻される各行に対して、上から順序を示すための疑似列です。

SELECT ename, sal
FROM   (SELECT ename, sal
        FROM   emp
        ORDER by sal DESC)
WHERE  ROWNUM <= 3;

図10　トップN分析を使って、「給与を多くもらっている社員トップ3名は誰か」を検索する例

　「インライン・ビューを使わなくてもできるんじゃないの？」と疑問に思われる方もいるかもしれません。ところが、次の例のようにインライン・ビューを使わずにSQL文を書こうとすると、「上位3件」を取得した後に「並べ替え」が行われるため、期待した結果と異なるデータが取得されてしまいます。

図11　インライン・ビューを使わずにデータを並べ替えて表示した例

まとめ

　今回は、相関副問い合わせやインライン・ビューなどの応用的な副問合せの構文を紹介しました。

　特に複雑なSQLになればなるほど、そしてデータ量が増えれば増えるほど、SQLの書き方によって問合せのパフォーマンスが変わる可能性があります。

　Oracle Databaseのオプティマイザ（SQLをどのように実行するかを決定する機能）も進化していますが、それでもSQLの構文自体が効率の悪い書き方になっていると、期待した通りの性能が出ないケースもあります。

　そのような場合には、ぜひ相関副問合せやインライン・ビュー、EXISTS句やWITH句（コラムで紹介します）の利用を検討してください。

コラム――WITH句

　複雑なSQL文では、1つのSQL文の中に同じような問副問合せが何度も出てくることがあります。このような場合にはWITH句を使うと便利です。WITH句を使うと、副問合せを主問合せと分けて定義し、主問い合わせ内で繰り返し使うことができます。

　例えば、部門ごとの平均給与を調べ、さらに部門間の平均よりも平均給与の高い部門を調べる場合を考えてみましょう。1つのSQL文で記述しようとすると非常に複雑なSQL文になりそうですね。そこで、次のようにいくつかのSQLに分割して考えてみましょう。

（1）部門ごとの平均給与を求める（DEPT_SUM_SAL）

（2）（1）の結果（DEPT_SUM_SAL）をもとに部門間の平均給与を求める（ALL_AVG_SAL）

（3）（1）と（2）を比較して部門間の平均給与よりも平均給与の高い部門を調べる

図12　「部門間の平均よりも平均給与の高い部門を調べる」という処理を分解したイメージ（クリックで拡大）

　（1）と（2）のSQL文を、WITH句を使って事前に定義し、以下のように書くことができます。

WITH 
dept_sum_sal  AS (
   SELECT   d.dname, SUM(e.sal) AS sum_sal
   FROM     emp e JOIN dept d
   ON       e.deptno = d.deptno
   GROUP BY d.dname),
all_avg_sal    AS (
   SELECT   SUM(sum_sal)/COUNT(*) AS avg_sal
   FROM     dept_sum_sal )
SELECT * 
FROM   dept_sum_sal 
WHERE  sum_sal > (SELECT  avg_sal 
                  FROM    all_avg_sal )
ORDER BY dname;