Visual Basic データベース講座

Visual Basic 中学校 > データベース講座 >

第１１回　サブクエリ

複数のSELECT文を組み合わせて使用するサブクエリを手法を説明します。

１．SELECT × SELECT

これまでの説明で既にSELECT文の使用方法は一通り解説しました。後は用意されているいろいろな関数やオプションの使用方法をヘルプなどを利用して覚えていけば問題ないレベルになるはずです。

とは言え、これまでは説明をシンプルにするために常に１つのSELECT文を対象として扱ってきました。しかし、SQLには複数のSELECT文を組み合わせて効果を発揮するサブクエリと呼ばれる手法があります。個々のSELECT文の内容はこれまでの解説通りですが、どのようにSELECT文を組み合わせ、どのように効果を発揮するのかと言う点は説明しておく必要があるでしょう。これが今回のテーマです。

２．WHERE句でのサブクエリ

まずは比較的シンプルな例を紹介します。データベースにはAnimals.mdbを使用します。

■リスト１

この例では、オーストラリア区に生息する動物の「目」を一覧表示します。生息地ID = 8とはオーストラリア区を指しています。

実行すると次の結果が得られます。

このSQL文ではWHERE句の条件でサブクエリを指定しています。目IDに条件を付けるときに、「オーストラリア句に生息している動物の目ID」という指定をするわけです。

オーストラリア区に生息している動物の目IDを取得するだけならSELECT文の基本であり、次のように書くだけです。

■リスト２

ここで取得した目IDを対象にすると言う表現はキーワード IN を使用して次のようになります。

■リスト３

１つのSQL文の中にSELECT文が２つ登場するのでぱっと見は複雑な感じがしますが、人間の思考経路に沿った直観的でわかりやすいSQL文になります。もちろん、どんどん条件を複雑にしていくと「わかりやすい」とは言えなくなるのですが、人間の思考経路に忠実に書けるところから比較的好まれる書き方のようです。

テーブルの結合を利用すれば同じ意味のSQL文をサブクエリなしで書くこともできます。テーブルの結合については前回説明しているので、余裕のある方は正解を見るまでに自分で挑戦してみてください。

サブクエリなしで書いた場合の例を次に紹介します。

■リスト４

この他にもいろいろな書き方があります。

なお、結果が同じSQL文でも今回の例のように結果に至る操作が異なる場合には、実行速度やリソースの消費量に差が出る場合があります。今回の例ではSQL Server 2005の推定実行プランと言う機能を使って比較したところサブクエリ版の方が結合版より若干高速に作動するようでした。

このケースではサブクエリ版の方がわかりやすい感じもしますね。場合によって切り分けて上手く使いこなしてください。

SQL文の実行方法はデータベースエンジンによって異なりますし、また、同じデータベースエンジンを使用していたも状況によって変わる場合がありますので今回の比較は参考程度とお考えください。

SQL Serverの推定実行プラン SQL ServerやOracleにはSQL文をどのように実行するのか、データベースエンジン内部での実行方法を表示する機能があります。これが推定実行プランです。Oracleの方はあまり経験がないのでわかりませんが、SQL Serverの推定実行プランはビジュアルに表示され、どの操作にどのくらい負荷がかかるのかよくわかるので重宝します。 以下は上記の例の結合版のSQL文をSQL Server 2005の推定実行プラン機能で表示したものです。 ■画像１ まず、T_動物マスタとT_目マスタを個別にスキャンしてから、INNER JOINで結合し、GROUP BYのために並び替え処理を行ってから、最後に結果を抽出すると言う実行の過程がわかります。さらに知識が増えてくると、「T_動物マスタをスキャンする方法がClustered Index Scanになっているので効率がいいな」などという分析も可能になります。

WHERE句で使用するサブクエリの注意点として、「必ず１つの項目のみを選択する」ことが挙げられます。

■リスト５

考えてみればすぐにわかるのですが、上記の例では目IDの抽出条件を示しているのですから、サブクエリでは抽出したい目IDの一覧を返す必要があります。

もちろん、名前は「目ID」でなくても構いません。たとえば、次の書き方は意味はありませんが有効です。

■リスト６

この場合は生息地ID=8の動物IDと一致する目IDが抽出条件になります。「意味がない」というのは、このAnimals.mdbでは動物IDと目IDには何の関係もないからです。

しかし、次の書き方は意味がないだけではなくエラーになります。

■リスト７

これは、目IDと動物IDの２つをSELECTしているので、どちらが目IDの抽出条件になるのか機械的に判断できないからです。

３．SELECT句でのサブクエリ

今度はSELECT句の中で、SELECT句を使う方法を紹介します。私はこの手法はあまり好きではないのですが、たびたび使用されることのある手法ではあります。私が好きではない理由は後で説明します。

■リスト８

このSQL文は、動物の名前と目名を並べて取得するものです。実行すると次の結果が得られます。結果は件数が多いので一部のみを掲載します。

龜鼈目って… 「龜鼈目」とは「きべつもく」と読んで、カメやスッポンの類のことなのですが、漢字が難しいのでちょっと気になって調べてみたらどこにも「龜鼈目」などという言葉は出ていませんでした。広辞苑にはかろうじて「龜鼈」(きべつ)という言葉はでていました。Wikipediaでは「カメ目」となっており、どうもカメ目の方が一般的なようです。 ただし、これは1988年の文部省の方針によるものと思われるので私自身は龜鼈目の方が好きです。1988年の改定ではたとえば「食肉目」を「ネコ目」としているので、結果としてイヌはネコ目に属することになり、ひどい場合には「イヌはネコの一種である」という誤解をしている人もいるようです。 このあたりの事情はWikipediaに少し載っていますので、興味がある方は参考にしてください。 http://ja.wikipedia.org/wiki/%E5%93%BA%E4%B9%B3%E9%A1%9E#.E7.9B.AE.E5.90.8D.E3.81.AE.E5.95.8F.E9.A1.8C カメ目の場合も、「スッポンはカメの一種である」という誤解を生まないでしょうか？龜鼈目は確かに難しい言葉ですが実態には沿っています。(…ひょっとしたらスッポンはカメの一種で正しいのかも知れないと いう気がちょっとしてきました。私の知識では判断不能です。)

なお、このSQL文を実際にVB2005のクエリデザイナで実行すると、下記のようなエラーメッセージが表示されます。

■画像２

これは、このクエリが複雑すぎてデザイナに上手く反映できないことを示しているのですがSQL文自体は実行できるので[OK]で問題ありません。場合のよっては「続行」か「キャンセル」か選択肢が出る場合もあるようですが、その場合も「続行」で大丈夫です。

SQL文を見ると、SELECT句の２つ目の項目をSELECT文によって取得しています。特徴的なのは、このSELECT文のWHERE句での条件指定です。

■リスト９

このWHERE句の意味はテーブルを結合する場合のON句の指定と同じ意味を持つことになります。お互いの目IDが等しいものを見つけてきて、その目名をSELECTするという意味になるわけです。

見落としがちなのですが、このような条件を指定すると目名はかならず0個または1個だけヒットします。もし、２つ以上の目名がヒットする場合はこのSQL文はエラーになります。１行に２つの目名はSELECTできないからです。

さて、すでにお気づきの方も多いと思いますが、このSQL文もテーブルの結合を使って書き換えることができます。次のようになります。

■リスト１０

私は、最初にも書きましたが、今回のケースではサブクエリ版は好きではなく、このような結合を使って書くべきだと思っています。

では、なぜ私がこの書き方が好きではないのか説明しましょう。理由は２つあります。１つは見にくいからです。今回はシンプルなSQL文を用意したのでそれほど見にくくないかもしれませんが、もっと項目がたくさんある場合のことを考えてみてください。そのうち、いくつかの項目で今回のようにSELECT文を使用していたとしたら、SQL文の中にはいくつものSELECT文が埋め込まれることになり、とても見にくくなってしまうことでしょう。

もう１つの理由は、実行効率が悪いからです。これも今回の例ではサブクエリが１つしかないので問題にはならないのですが、複数の項目でこのようなサブクエリを使用した場合、１つのSQL文を実行するために、いくつものSELECT文を実行することになってしまいます。これでは非効率的です。

もっとも、データベースエンジンがSQL文をうまく解析していくつものSELECT文をまとめて実行してくれればいいのですが、今のところそこまでの解析は期待できませんし、逆にそこまでデータベースエンジンにまかせてしまうと内部で何が起こっているかわかりにくくなってしまい、それも問題です。

このことをはっきりさせるために、項目を増やしてサブクエリを２つ使用するケースを考えてみましょう。

次のようなSQL文を想定します。

■リスト１１

このSQL文では目名と読みを取得するために、それぞれSELECT文を実行しています。実際にはどちらもT_目マスタから同じようにして取得できますので別々に実行するのは明らかに非効率です。

さきほどのようにSQL Server 2005の推定実行プラン機能を使って実行内容を見てみると次のようになります。

■画像３

推定実行プランの詳細は理解しなくても問題ありませんが、複雑な実行プランが生成されているなぁという印象は持たれることと思います。これは3つのSELECTを実行して、最終的にはつじつまが合うように結果を合体させると言う処理になるからです。

一方、同じ結果を得るためにテーブルの結合を使った場合のことを考えます。

■リスト１２

こちらの推定実行プランは次の通りです。

■画像４

同じ結果を得るものであるのに、サブクエリ版と比較すると明らかにシンプルなのがわかりますし、実際のところ実行速度もサブクエリ版の1.5倍になり高速に動作します。

４．FROM句でのサブクエリ

最後にFROM句でサブクエリを使う例を紹介します。これは複雑なデータ操作が必要なシーンではよく使う手法です。SELECT文はデータの集合を表形式で取得するものであるのでSELECT文自体を１つのテーブルであるとみなすことができます。そこでFROM句で本来テーブルを指定する場所にSELECT文を指定すると言う手法です。

■リスト１３

このSQL文ではT_動物マスタで体長が6番目から10番目に大きな動物の一覧を取得します。以前にも解説したようにTOPを使用した場合で同じ値のレコードが複数ある場合の取得件数はTOPで指定した件数とは異なる場合があるので必ずしも正確ではありません。結果は次のようになります。

結果が体長の小さい順に並んでいるのは気になりますが今はこれでいいとしましょう。体長で比較するとキリンよりイリエワニの方が80cmも大きいのですね。体高ではもちろんキリンの方が高いです。前にも書きましたがAnimals.mdbのデータはあくまでも参考程度なので学校に提出するレポートなどに引用するときは気をつけてください。

さて、このSQLの仕組みを考えてみます。SQL文のTOP句を使用すると上位何位までのレコードを取得することは簡単にできるのですが、何位から何位までという指定はできません。このSQL文ではTOP句を含むSELECT文２つを組み合わせることで、これを実現しています。

内側のSELECTはとても簡単です。

■リスト１４

このSELECT文では体長の大きい順に並べた場合の上位10件を取得します。結果は次の通りです。

これをSubQueryという名前の１つのテーブルとして扱うわけです。今度はこのサブクエリを「SubQuery」の置き換えて、全体のSQL文を見てみます。

■リスト１５

すると、ここもシンプルなSQL文であることがわかります。

体長の小さい順に並べて上位５件を取得すると言うわけです。それで最初の結果が得られます。体長を小さい順に並べるのはTOP句では上位何件は取得できるのですが下位何件は取得できないからです。

その影響で、最終結果も小さい順に並んでしまうわけです。

では最終結果を大きい順に並べるにはどうしたらよいでしょうか？時間がある方は少し考えてみてください。

答えは次の通りです。

■リスト１６

さて、最初の例では２つのSELECT文が両方ともT_動物マスタを対象にしていましたが、構文上は異なるテーブルを指定することもできます。また、テーブルの結合の構文と組み合わせて書くこともでき、サブクエリ同士を結合したり、本物のテーブルとサブクエリを結合することもできます。要するにテーブルを指定できるところにはテーブルの代わりにサブクエリを指定することができるのです。

注意点は１つだけあります。それは、たとえ必要がなくてもサブクエリはかっこでくくって最後にAsを使って別名をつけることです。今回の例ではAS SubQueryとして、サブクエリに「SubQuery」という名前を付けています。このように名前をつけないとSQL文はエラーになっていしまいます。