SQLアタマアカデミー

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

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

便利で危険な相関サブクエリ① 更新と組み合わせる

まずは,更新と相関サブクエリを組み合わせるケースを考えます。図1の社員マスタテーブルに加えて,図8のような新しいテーブルを用意します。

部署別の最高齢社員の情報を記録するテーブルです。

図8 部署別の最高齢社員の情報を記録するテーブルを作成

EmpMaxAge

dept
(部署)
name
(社員名)
age
(年齢)
営業  
総務  
開発  

まずはそのまま書いてみる

さて,このテーブルはまだ情報が登録されていません。先ほど使った社員テーブル図1から,部署別の最高齢の社員と年齢を選択して更新するのが今回の問題です。

すでにレコードが存在するので,更新の道具としては迷わずUPDATE文が選択されるところです。相関サブクエリはUPDATE文の中でも使うことが可能ですので,単純に考えればリスト4のように,先に作った相関サブクエリをそのままSET句の右辺に代入したSQL文ができあがります。

リスト4 部署ごとの最高齢社員を更新する(エラー!)

画像

しかしこのUPDATE文はエラーになります。理由は,Oracleでこれを実行したときに出るエラーメッセージが語るとおりです――「単一行副問合せにより2つ以上の行が戻されます」⁠つまり,1行しか結果が返るべきでない個所で,2行以上の結果が返されている,ということです。それがどこであるか,もうおわかりでしょう。そう,最高齢の社員が2人いる開発部ですね図9)⁠

図9 エラーになっているテーブルのイメージ

EmpMaxAge

画像

なぜエラーになるのか

リレーショナルデータベースの基本原則の1つに「テーブルに保持されるデータはすべて第1正規形を満たさなければならない」というものがあります。第1正規形とは,言い換えれば「すべてのデータが分割不可能なスカラ値である」ということです。したがって,図10のようなデータの持ち方は,⁠少なくとも現在の)関係モデルにおいては許されていないのです。

図10 このようなテーブルは存在しえない

dept
(部署)
name
(社員名)
age
(年齢)
営業石川40
総務藤田50
開発小川
長谷川
29
29

裏を返すと,社員テーブルに小川さんと長谷川さんのどちらか1人だけしか存在しなかった場合には,上のUPDATE文はエラーなく実行できます。したがって,このエラーは,テーブルの状態によって出たり出なかったりする幽霊のように厄介な存在です。ときどき,試験のときにデータのバリエーションが不足していてこのエラーを見過ごし,実運用に入ってから出現することがあるので注意が必要です。

対処法

この幽霊に対処する方法は,大きく2通りあります。

  • ① 情報の登録方法をINSERTに変えてしまう
  • ② 小川さんと長谷川さんのうち,適当にどちらか1人だけを代表に選んで登録する

どちらを取るのがよいのかは,その場その場の判断です。INSERTを使った洗い替え方式は,論理的ですっきりしていますが,常に全行DELETE(またはTRUNCATE)して全行INSERTする必要があるので,更新コストが多くかかります。一方,代表選抜方式は,更新コストは少ないのですが,どういう基準で代表を選ぶかが不明瞭で場当たり的です。

ここでは,INSERTを使った洗い替え方式のコードを紹介しておきましょうリスト5)⁠代表選抜方式をどうやって実現するかは演習問題としますので,みなさん考えてください(稿末の「演習問題」参照)⁠

なお,このコードを利用するときは,テーブル定義においても,主キーを(部署)だけから(部署, 社員名)に変更しておく必要がある点を忘れないでください。

リスト5 ① INSERTを使った洗い替え方式

まずは全行削除(TRUNCATEでも可)
DELETE FROM EmpMaxAge;

全行登録
INSERT INTO EmpMaxAge
SELECT dept, name, age
  FROM Employees E1
 WHERE age = (SELECT MAX(age)
                FROM Employees E2
               WHERE E1.dept = E2.dept);

COLUMN グローバル変数とローカル変数

筆者はあるとき,同僚から相関サブクエリの構文として,次のような書き方はなぜ認められていないのかという,唐突な質問を受けたことがあります。

画像

見た瞬間,思わず「なんだこのスジの悪いコードは」と感じたものの,クエリの意味としてはカット条件がサブクエリの内側にある場合と変わりません。とすれば,確かにこのクエリは適法な構文として認められてもよさそうなものです。

しかし残念なことに,上のクエリは,どんなDBMSでもエラーになります。エラーのメッセージとしては,⁠E2.sex という列が存在しない」というものが一般的でしょう(大雑把なDBMSだと「WHERE句にエラーがあります」程度しか言わないかもしれません)⁠⁠でも,なぜこの構文がエラーになってしまうのか。E1とE2のバインドを内側でやるか外側でやるかは,本質的な問題とは思えないのに」というのが同僚の疑問だったのです。

これはなかなかに虚を突く,しかも本質的な質問でした。みなさんは,この疑問に即答できるでしょうか?

実は,テーブルに付けられる相関名には,プログラミング言語でいうところのグローバル変数とローカル変数のような生存範囲が決められているのです。つまり,サブクエリ内で宣言されたE2という変数の生存範囲は,サブクエリ内のみだったのです。

画像

したがって,サブクエリの外側でE2を参照することはできず,エラーとなってしまうわけです。⁠内側から外側を見ることはできても,外側から内側を見られない」というこのルールは,サブクエリの深さが3層以上になった場合でも同様です。

SQL には「変数」の概念は存在しないのですが,相関名にはちょっと変数っぽい性質があるのです。

著者プロフィール

ミック

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

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

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

コメント

コメントの記入