SQL緊急救命室

第5回 時代錯誤症候群~進化し続けるSQLに取り残されるな!

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

時代錯誤症候群は冗長性症候群を併発する

次の患者も,SQLではシンプルさが正義であることがよくわかる。新機能の力も発揮できる。


カルテ2:図4のような製造業者のテーブルManufacturersを追加する。部品の要求をかけている製造業者と同じ地域(=都市および地区が同じ)に存在する供給業者を,先のSuppliersテーブル(図1)から選択したい。もちろん,出荷が可能な供給業者に限る。

図4 Manufacturersテーブル

図4 Manufacturersテーブル

冗長さはコードをわかりにくくする

部品を要求している製造業者は,a,b,c,fの4つ。うち,⁠東京,江戸川区」のcと,⁠大阪,福島区」のfについては,マッチする供給業者がいない,と。すると結果は,図5のようになればいいですね。ようしリスト4,図6⁠。

図5 求める結果

sup | city | area
----+------+--------
A   | 東京 | 北区
C   | 東京 | 荒川区

リスト4 ワイリーの解(結果が正しくない⁠⁠ G | 大阪 | 北区

SELECT sup,
       city,
       area
  FROM Suppliers
 WHERE ship_flg = '可'
   AND city IN (SELECT city
                  FROM Manufacturers
                 WHERE req_flg = '要')
   AND area IN (SELECT area
                  FROM Manufacturers
                  WHERE req_flg = '要');

図6 リスト4の実行結果

sup | city | area
----+------+--------
A   | 東京 | 北区
C   | 東京 | 荒川区
G   | 大阪 | 北区

お前の解は,効率以前に結果が違うぞ。


あれ……本当だ。余計な「大阪,北区」の供給業者Gが入ってしまっている。製造業者eは部品を要求していないのに。

あなたの解は,こういう動きをするの。最初のINのサブクエリで部品を要求している製造業者の都市として,東京と大阪が選ばれる。次のINのサブクエリでは,部品を要求している地区として,北区,荒川区,江戸川区,福島区が選ばれる。すると結局,条件に合致する地域の組み合わせはこうなるの。

  • 東京 - 北区

  • 東京 - 荒川区

  • 東京 - 江戸川区

  • 東京 - 福島区 ←ありえない組み合わせ

  • 大阪 - 北区 ← 要求フラグが不要になっているため,論理的にありえない組み合わせ

  • 大阪 - 荒川区 ←ありえない組み合わせ

  • 大阪 - 江戸川区 ←ありえない組み合わせ

  • 大阪 - 福島区

ああっ,それで「大阪,北区」という条件に合致する供給業者Gが選ばれてしまったのか。偶然,東京と大阪に「北区」という同じ名前の区があったから。

比較できるのは列だけではない

そういうこと。いい? この問題では,⁠都市,地区」はこれで一続きのキーとして扱うべきなの。あなたみたいに分離したらダメリスト5⁠。city もareaも文字列型の値だから,連結演算子で結合してしまえば,これで1列のキーとして扱えるようになるわ注6⁠。

リスト5 ヘレンの解:複数の列を連結して一つのキーとして扱う

SELECT sup,
       city,
       area
  FROM Suppliers
 WHERE ship_flg = '可'
   AND city || area IN (SELECT city || area
                          FROM Manufacturers
                         WHERE req_flg = '要');

うむ。そしてここからがようやく本題だ。リスト5はさらにリスト6のように書き換えるのが正しい。これはSQL-92で標準化された行式(row expression)だ。列と列の比較を複数列の比較に一般化したもので,原理的には比較演算子全体に(=,<>,<,>)拡張できる。実装されているかどうかはまた別問題だが注7⁠。

リスト6 ロバートの解:行式を利用

SELECT sup,
       city,
       area
  FROM Suppliers
 WHERE ship_flg = '可'
   AND (city, area) IN (SELECT city, area
                          FROM Manufacturers
                         WHERE req_flg = '要');

なるほど。しかしこれってヘレンさんの解(リスト5)から書き換えるメリットって何なんでしょう? 実質的に同じことをやっているように見えるのですが。

メリットは大きいぞ。まず左辺の列に連結演算子を使わなくてよいため,インデックスの利用が期待できる。そして列のデータ型が文字列型以外でも汎用的に使える。比較したい列が数値型や日付型の場合,連結子を使うには型変換を使わねばならない。

ああ,そういうことか!


SQLの標準化の中で最も画期的だったのは,SQL-92(1992年改訂)です。もう20年近く前の話ですが,ここで導入された機能には重要なものが多く含まれています。例を挙げれば,本稿で取り上げた行式やCASE式,ほかにも日付型やトランザクション分離レベル,一時表なども導入されました。⁠ということはそれ以前には,そうした機能がなかったの?!」と驚く人もいると思います。昔のSQLが「欠陥言語」と悪口を叩かれていた理由がおわかりいただけるでしょう。これを言うと年輩の方から怒られてしまうかもしれませんが,SQLに悪いイメージを持っている人は,えてしてSQL-92以前のイメージで時が止まってしまっています

また,ロバートが言うように,SQLに追加される新機能を観察していると,そこには単純さの原則が存在していることが見て取れます。かつては難しい構文を使わなければ実現できなかった処理を,ずっと簡略的で,直観的にわかりやすく記述できるように進歩してきています。UNIONを連ねなければできなかった分岐はCASE式で,同じくUNIONを使っていた完全外部結合はFULL OUTER JOINで,複雑な相関サブクエリはウィンドウ関数で,簡単に書けるようになりました。かつ,SQLの長所は,そうした新機能のほうが概してパフォーマンスも優れた実行計画が立てられることです。利用しない手はありません。

注6)
「||」はSQL標準の演算子ですが,一部のDBMSで未サポートのため,SQL Serverでは「+⁠⁠,MySQLでは「CONCAT」という実装依存の機能を使う必要があります。
注7)
たとえば,Oracle,PostgreSQLは行式をサポートしていますが,SQL Serverは2008 R2でも行式をサポートしていません。

著者プロフィール

ミック

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

著書:『SQL ゼロからはじめるデータベース操作』(翔泳社,2010)『達人に学ぶ SQL徹底指南書』(翔泳社,2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社,2007)

Twitter:copinemickmack

SQL緊急救命室:サポートページ