便利で危険な相関サブクエリ② スカラサブクエリと組み合わせる
相関サブクエリの便利な応用方法として、
本来、
dept name age max_age_dept ←部署別の最高年齢
---- ---- --- ------------
営業 橋本 37 40
営業 山下 24 40
営業 石川 40 40
総務 中島 35 50
総務 前田 26 50
総務 藤田 50 50
開発 小川 29 29
開発 長谷川 29 29
このクエリに構文上の問題はありません。問題が起きるのは、
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;繰り返すまでもなく、
このエラーもやはり、
dept name age max_age_dept max_age_name ←部署別の最高年齢の社員
---- ---- --- ------------ ------------
営業 橋本 37 40 石川
営業 山下 24 40 石川
営業 石川 40 40 石川
総務 中島 35 50 藤田
総務 前田 26 50 藤田
総務 藤田 50 50 藤田
開発 小川 29 29 小川
このように、
相関サブクエリが返す結果を常に単一行に制限する安全な方法は、
終わりに
本稿で紹介した相関サブクエリは、
相関サブクエリは単独で使ったとしても十分便利ですが、
それではまとめです。
- 相関サブクエリは手続き的な観点から見ると
「ループ隠し」 の手段 - 相関サブクエリは集合指向的な観点から見ると
「集合のカット」 の手段 - 更新やスカラサブクエリと組み合わせるときは、
結果が第1正規形を満たすか、 厳重にチェックしないと危険
最後に、
演習問題
- 問題：更新と相関サブクエリを組み合わせた問題で、
EmpMaxAgeへUPDATEによってエラーなく更新を行うSQLを考えてください。 ヒントを出しておくと、
「相当に場当たり的でエラーを回避することだけを考えた方法」 です。現実にこの方法を採るかどうかは別問題として、 純粋に練習用のパズルとして考えてみてください。 ちなみに、
この問題の解答とまったく同じやり方で、 スカラサブクエリのときに生じたエラーを回避することもできます。余裕のある方は、 そちらについても確認してみてください。 - ※ 回答は、
筆者のWebページ内にある “ 「SQLアタマアカデミー」 ”サポートページ に掲載しています。
- ※ 回答は、
参考資料
- Joe Celko
『SQLパズル 第2版』 （翔泳社、 2007） 相関サブクエリは多くの問題で利用されているため、
練習問題には事欠きませんが、 特に薦めるとすれば、 「パズル38 記録の更新」 「パズル70 株価の動向」 に、 ぜひチャレンジしてみてください。 また、
なぜSQLがそもそも真理値に3つ目の値を持ち込むことになったのか、 という歴史的な事情を知りたい人は 「2-8 神のいない論理」 を参照してください。 - ミック
『達人に学ぶ SQL徹底指南書』 （翔泳社、 2008） 相関サブクエリの一歩進んだ応用方法については
「1-6相関サブクエリで行と行を比較する」 を参照。行間比較のツールという観点から相関サブクエリの便利な使い方を紹介しています。