SQLアタマアカデミー

第9回 SQLでループ! 相関サブクエリの使い方~切れ過ぎるナイフにご用心~ (4)便利で危険な相関サブクエリ②

この記事を読むのに必要な時間:およそ 2 分

便利で危険な相関サブクエリ② スカラサブクエリと組み合わせる

相関サブクエリの便利な応用方法として,スカラサブクエリと組み合わせる方法もよく知られています。たとえば,社員テーブルリスト1のEmployeesから社員を一覧表示するとき,ついでに部署別(あるいは性別)の最高年齢の情報も追加したい,という場合を考えます。

本来,名前など社員個人に付属する属性と,最高年齢という社員の集合(⁠部署」とか「性別」⁠に付属する属性を同じレベルで表示するのは少し奇妙なのですが,現実の要件としてときどき見かけます。こういうケースでは,最高年齢の情報をスカラサブクエリを使って取得することで対応できますリスト6,図11)⁠

リスト6 社員の一覧表示に部署の最高年齢も追加(エラーは起きない)

画像

図11 リスト6の実行結果

dept name   age max_age_dept ←部署別の最高年齢
---- ----   --- ------------
営業 橋本   37  40
営業 山下   24  40
営業 石川   40  40
総務 中島   35  50
総務 前田   26  50
総務 藤田   50  50
開発 小川   29  29
開発 長谷川 29  29

このクエリに構文上の問題はありません。問題が起きるのは,この一覧のリストに最高年齢の社員の名前も追加したい,と思ったときです。すると,先ほどの相関サブクエリと組み合わせた場合リスト4とまったく同様に,スカラ値しか(=単一行しか)認められない個所に複数行を返してしまう,という現象が生じますリスト7)⁠

リスト7 社員の一覧表示に部署の最高年齢の社員名を追加(エラー発生!)

SELECT dept,
       name,
       age,
       (SELECT name
          FROM Employees E2
         WHERE E1.dept = E2.dept
           AND E2.age = (SELECT MAX(age)
                           FROM Employees E3
 WHERE E2.dept = E3.dept)) AS max_age_name
   FROM Employees E1;

繰り返すまでもなく,開発部の最高年齢の社員は2人います。したがって,リストの小川さんと長谷川さんの行については,1行に2行を表示できずエラーとなります。

このエラーもやはり,テーブル状態によって発生したりしなかったりします。長谷川さんの情報が社員テーブルにない場合は,上のクエリはエラーなく実行できて,図12のような結果を返します。

図12 部署ごとの最高年齢の社員が1人しかいない場合は正常に実行される

dept name age max_age_dept max_age_name ←部署別の最高年齢の社員
---- ---- --- ------------ ------------
営業 橋本 37  40           石川
営業 山下 24  40           石川
営業 石川 40  40           石川
総務 中島 35  50           藤田
総務 前田 26  50           藤田
総務 藤田 50  50           藤田
開発 小川 29  29           小川

このように,相関サブクエリをスカラサブクエリと組み合わせる場合にも,結果が第1正規形を常に満たすかどうかについて,やはり厳重な注意が必要です。相関サブクエリは集合を自在にカットできる切れ味鋭い刃ですが,使い方を誤ると危険性も大きい道具であることを忘れないでください。

相関サブクエリが返す結果を常に単一行に制限する安全な方法は,集約した結果のみを返すか,あるいはサブクエリ内の結合条件で一意キーを指定することです。こうすれば,複数行を返すエラーを確実に回避できるので,要件上可能な場合にはまずこの方法を検討することを勧めます。

終わりに

本稿で紹介した相関サブクエリは,SQLで制御構造を記述できるという点で,CASE式と並んで重要な機能です。最初に理解するときは,冒頭でも述べたようにループの代用という観点から見るのがわかりやすいと思いますが,そこから次第に集合のカットという,よりSQLらしい理解のしかたへシフトしていくことで,深い理解を得られると思います。

相関サブクエリは単独で使ったとしても十分便利ですが,本稿で見たように,更新やスカラサブクエリと組み合わせたときに絶大な力を発揮します。しかし,エラーが発生する危険も増えるため,使うときは注意深くあり得るデータのバリエーションを考えなければなりません。

それではまとめです。

  • 相関サブクエリは手続き的な観点から見ると「ループ隠し」の手段
  • 相関サブクエリは集合指向的な観点から見ると「集合のカット」の手段
  • 更新やスカラサブクエリと組み合わせるときは,結果が第1正規形を満たすか,厳重にチェックしないと危険

最後に,今回の演習問題です。

演習問題
問題:更新と相関サブクエリを組み合わせた問題で,EmpMaxAgeへUPDATEによってエラーなく更新を行うSQLを考えてください。

ヒントを出しておくと,⁠相当に場当たり的でエラーを回避することだけを考えた方法」です。現実にこの方法を採るかどうかは別問題として,純粋に練習用のパズルとして考えてみてください。

ちなみに,この問題の解答とまったく同じやり方で,スカラサブクエリのときに生じたエラーを回避することもできます。余裕のある方は,そちらについても確認してみてください。

回答は,筆者のWebページ内にある「SQLアタマアカデミー」サポートページに掲載しています。
参考資料
Joe Celko『SQLパズル 第2版』
(翔泳社,2007)

相関サブクエリは多くの問題で利用されているため,練習問題には事欠きませんが,特に薦めるとすれば,⁠パズル38 記録の更新」⁠パズル70 株価の動向」に,ぜひチャレンジしてみてください。

また,なぜSQLがそもそも真理値に3つ目の値を持ち込むことになったのか,という歴史的な事情を知りたい人は「2-8 神のいない論理」を参照してください。

ミック『達人に学ぶ SQL徹底指南書』
(翔泳社,2008)

相関サブクエリの一歩進んだ応用方法については「1-6相関サブクエリで行と行を比較する」を参照。行間比較のツールという観点から相関サブクエリの便利な使い方を紹介しています。

相関サブクエリのパフォーマンス

相関サブクエリを使うとき注意すべき点として,本稿で解説したような正規形違反の返却結果となるもののほか,パフォーマンスの問題もあります。というのも,DBエンジンが相関サブクエリを実行する際,内部的なアルゴリズムとしてループが使用される可能性が高いからです。それはつまり,サブクエリの外側のテーブル1行ごとに対して,内側のサブクエリが実行されるということです。これは,本稿の図4で見たように,外側のテーブルのレコードによってサブクエリが返す値が異なるため,どうしてもサブクエリだけを単独で実行できないことによる制限です。相関サブクエリは,あくまでループを「隠す」道具であって,ループを「代替」する道具ではない,ということです。

このことからわかるのは,単純なサブクエリに比べて相関サブクエリの実行コストは相当高い,ということです。この問題を軽減するための方法として有効なことは,サブクエリ内の結合キーにインデックスを作成する(あるいは主キーを結合キーとして使う)ことですが,それでも相関サブクエリがパフォーマンス上の大きなリスクファクターであることは変わりません。特に,相関サブクエリのネストが三重以上の場合,実行コストは指数関数的に増加するため,厳重な注意が必要になります。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入