前回は、複雑な条件を指定したい場合に役立つ「副問合せ」を紹介しました。11回では、SQL文を入れ子にして条件指定をする構文である「副問い合せ」を紹介しました。今回は、副問合せを応用したさまざまな構文例をご紹介します。前回の復習として、「全社員の平均給与よりも、給与が高い社員は誰か」を調べてみましょう。副問合せで全社員の平均給与値を求め、それよりも給与額が高い社員を調べるには、以下のように指定すればよかったですね。「全社員の平均給与よりも、給与が高い社員は誰か」を調べる副問合せでは、次にもう少し複雑な例を考えてみましょう。「自分が所属する部門の平均給与よりも、給与が高い社員は誰か」を考えてみます。ポイントは、「自分が所属する部門」と比較しなければならない点です。今までの例(図1)では、「全社員の平均給与」という単一の値と比較すればよかったのに対して、この問合せでは、人によって比較する条件値が異なります。部門10に所属する人であれば部門10の平均給与と、部門20に所属する人であれば、部門20の平均給与と比較する必要があります。このような場合には、どのように条件を指定すればよいのでしょうか。相関副問合せを使ってみよう「自分の所属する部門の平均給与よりも、給与が高い社員は誰か」を調べるための、1つ目の構文は「相関副問合せ」です。「相関副問合せ」とは、主問合せで処理したい行によって副問合せの条件を変えながら検索することができる構文です。Databaseはまず副問合せに記述されたSQLを実行して結果データを取得し、その値を使って主問合せを実行します。今回のように「自分が所属する部門の平均給与」と比較したい場合は、図2のイメージのように、社員ごとに条件(部門)を変えて副問合せを実行する必要があります。社員ごとに副問合せの条件(部門)を変えて条件値を取得するイメージ(クリックで拡大)図2のイメージを、1つのSQL文で表したのが図3です。図3のイメージのように、副問合せの条件に「主問合せで検索したい人(候補行)の所属する部門」を指定すればよいのです。候補行とは、主問合せで評価される行のことです。条件に一致した場合には出力結果となることから、結果の「候補」という意味で使われます。副問合せの条件に「その人の所属する部門」を指定するイメージ(クリックで拡大)このような条件指定をすることができるのが、「相関副問合せ」の構文です。相関副問合せでは、図4のように、副問合せの中に主問合せで使用する表の名前(別名)を指定することで、主問合せと副問合せの関係を記述します。「副問合せの条件で使用する部門は、主問合せの候補行の社員が所属する部門である」という条件を表したイメージこの例では、主問合せでも副問合せでも同じ「EMP」表を使っているので、比較するために、主問合せのEMP表を「o(外側を表すouterの頭文字)」、副問合せのEMP表を「i(内側を表すinnerの頭文字)」と、別名を付けて指定しています(別名は何でも構いません)。このように記述すると、「副問合せ(i)の条件で使用する部門番号は、主問合せ(o)の候補行の部門番号を使う」という条件を指定できます。これによって、社員ごとに、その人の所属する部門の平均給与と比較できるようになるのです。それでは、実行して確認してみましょう。最後に、相関副問合せ実行時の動作についてまとめます。Database内部では、以下のような処理が行われます。表の次の候補行に対しても、上記1から4の処理を繰り返すこのような処理が行われた結果、社員ごとに、その人の所属する部門の平均給与との比較が可能になります。今回のように「行によって異なる条件で副問合せを実行したい」という場合には、相関副問合せを使うと便利です。インライン・ビューを使ってみよう「自分が所属する部門の平均給与よりも、給与が高い社員は誰か」を調べるためには、相関副問合せ以外にも「インライン・ビュー」を使用することもできます。「インライン・ビュー」とは、FROM句で副問合せを使用する構文です。社員の給与を「自分が所属する部門の平均給与」と比較するためには、「部門の平均給与」との比較が必要です。相関副問合せでは、これを副問合せに記述しましたが、図6のように、「部門の平均給与」を求めた結果データを表に見立てて処理を記述することもできます。「部門の平均給与」を表に見立て、結合するイメージ(クリックで拡大)それでは、図6に示した処理を、SQL文で記述してみましょう。「部門の平均給与」のデータセットを表のように扱うため、FROM句に部門の平均給与を求める副問合せを記述します。このようにFROM句に問合せを記述する構文を「インライン・ビュー」と呼びます。図7の例では、赤枠で囲まれた部分がインライン・ビューです。インライン・ビューを使って「自分の所属する部門の平均給与よりも、給与が高い社員は誰か」を調べる問合せ図7の例では、インライン・ビューに別名「i」を指定しています。「部門の平均給与」というデータセットに「i」という名前を付け、主問合せで表のように使えるようにしているのです。あとは主問合せで、EMP表の社員の給与とインライン・ビュー「i」を結合し、「i」の部門平均給与(AVG_SAL)よりも給与の高い社員を検索しています。ここで、「AVG(sal)」に別名「avg_sal」を指定していることに注意してください。インライン・ビューで問合せ結果を表に見立てる際には、SELECT句で指定した列名が表の列名として扱われます。別名を指定しなければ、「deptno」と「AVG(sal)」がi表の列名となります。Databaseの命名規則上、括弧を含む列名を定義することはできません(エラーになります)。そのため、この例では「AVG(sal)」に別名「avg_sal」を指定しています。このように、問合せの中で使いたいデータセットを「インライン・ビュー」としてFROM句で定義し、主問合せで使うことで、複雑な問合せをシンプルに考えることができます。EXISTSを使ってみよう相関副問合せを使った構文の1つに、「EXISTS」という条件の指定方法があります。これは、副問合せに該当する行が存在するかどうかをテストできる構文です。フラグが「1」であるデータがあるかないかなど、副問合せで条件に一致するデータの存在有無を調べる際に便利です。「偽(FALSE)」を戻します。それでは、EXISTS句を使った検索例を見てみましょう。例として、「部下が少なくとも一人はいる社員は誰か」という処理を考えてみましょう。過去の連載でも何度か紹介しましたが、EMP表のmgr列には、上司のempno番号が入っています。自分のempnoが誰かのmgr列に入っていれば、その社員は上司です。EMPNO列とMGR列を使って部下がいるかを探すイメージ今回も、「自分のempno番号は誰かのmgr番号になっているか」のように、社員によって条件を変えなければならないため、相関副問合せを使用します。「存在有無」だけを調べればよいので、EXISTS句を使うことができます。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句に変えただけで性能が大幅にアップしたという例もあります。EXISTS(値が存在しないことを判断)」を使う場合に、NULL値を考慮する必要がないことです。INを使う場合には、NULL値の扱いに気をつける必要がありました。INでは、主問合せで条件に指定された列値と、副問合せから得られた結果値とを比較して、「どれとも一致しない」という評価がなされるためです。列値とNULLは比較できないため、副問合せにNULLが含まれていた場合は比較できない(結果としてデータが検索されない)問題がありました。EXISTSは副問合せの結果が「ある」か「ない」かを判断するだけで、値同士の比較を行うわけではありません。そのため、NULL値を考慮する必要がないのです。以上の理由から、値の存在チェックではEXISTS句を使うことをお勧めします。トップN分析を使ってみよう最後に、インライン・ビューを応用した応用構文についても、1つ紹介しましょう。インライン・ビューを応用して、データの上位n件(または下位n件)を調べる「トップN分析」という構文を記述できます。トップN分析を使うと、例えば「売上の上位3個の製品は何か」や「在庫の少ない部品下位10個は何か」を簡単に調べることができます。それでは、トップN分析を使って、「給与を多くもらっている社員トップ3名は誰か」を調べてみましょう。トップ3名を調べるには、まずデータを並べ替えてから、上位3件を取り出す必要があります。疑似列を使って上から3件のデータを表示するように制限します。ROWNUMは、問合せによって戻される各行に対して、上から順序を示すための疑似列です。「インライン・ビューを使わなくてもできるんじゃないの?」と疑問に思われる方もいるかもしれません。ところが、次の例のようにインライン・ビューを使わずにSQL文を書こうとすると、「上位3件」を取得した後に「並べ替え」が行われるため、期待した結果と異なるデータが取得されてしまいます。インライン・ビューを使わずにデータを並べ替えて表示した例まとめ今回は、相関副問い合わせやインライン・ビューなどの応用的な副問合せの構文を紹介しました。複雑なSQLになればなるほど、そしてデータ量が増えれば増えるほど、SQLの書き方によって問合せのパフォーマンスが変わる可能性があります。Databaseのオプティマイザ(SQLをどのように実行するかを決定する機能)も進化していますが、それでもSQLの構文自体が効率の悪い書き方になっていると、期待した通りの性能が出ないケースもあります。そのような場合には、ぜひ相関副問合せやインライン・ビュー、EXISTS句やWITH句(コラムで紹介します)の利用を検討してください。複雑なSQL文では、1つのSQL文の中に同じような問副問合せが何度も出てくることがあります。このような場合にはWITH句を使うと便利です。WITH句を使うと、副問合せを主問合せと分けて定義し、主問い合わせ内で繰り返し使うことができます。部門ごとの平均給与を調べ、さらに部門間の平均よりも平均給与の高い部門を調べる場合を考えてみましょう。1つのSQL文で記述しようとすると非常に複雑なSQL文になりそうですね。そこで、次のようにいくつかのSQLに分割して考えてみましょう。「部門間の平均よりも平均給与の高い部門を調べる」という処理を分解したイメージ(クリックで拡大)(1)と(2)のSQL文を、WITH句を使って事前に定義し、以下のように書くことができます。WITH句を使って「部門間の平均よりも平均給与の高い部門」を検索する例このように、WITH句を使うことによって、主問合せの構文をシンプルに記述することができます。1つのSQL文の中で同じ副問合せが何度も登場する場合は、WITH句で処理を一カ所に集約することによって、性能向上も期待できます。結合や集計を含む複雑な問合せでは、WITH句の使用を検討してみてください。オラクルダイレクト所属。オンラインセミナーの講師や、お客様への提案、案件の支援などを担当。SQLクイズ」(翔泳社)があります。メールマガジンをご購読ください。申し込みページへITmediaはアイティメディア株式会社の登録商標です。