相関サブクエリ
では次に、単純に会社全体で一番高齢の社員を抽出する代わりに、男女別に最高齢の社員を抽出することを考えます。
手続き型言語で解く場合
もしEmployeesテーブルがCSV(Comma Separated Values:カンマ区切り)や固定長といった、ただのフラットファイル[1]で、手続き型言語でこの問題を解くとすれば、次のようなアルゴリズムを使うことになるでしょう。
- max_ageという整数型の変数を用意し、0で初期化する
- レコードを1行ずつ走査する
- カレントレコードのsexが「男」ならば、ageとmax_ageを比較し、ageのほうが大きければ、max_ageにageを代入する
- すべてのレコードについて走査したら、max_ageを男性の最高齢値として返す
- 2.からの処理を、女性についても繰り返す
ここでは、男性と女性について2回分のループが発生しています。変数max_ageを男女別に用意しておくと、一度のループで済ませるよう改良できますが、いずれにせよレコードを1行ずつ処理することは必要です。
SQLで解く場合
一方、これと同じことをSQLで行おうとした場合、明示的にループを記述することはしません[2]。代わりに、最高齢の値を求める集合の範囲を分割(限定)します。そのための方法が、相関サブクエリです(リスト2)。
結果(図3)から明らかなように、このSELECT文は、正しく男女別に最高齢の社員を求めています。それでは、このクエリがどのようなロジックによって実行されているのか、段階を追って見ていきましょう。
行われている動作
リスト2の2つ目のSELECT文においても、最初に評価、実行されるのがサブクエリであるという点は変わりません。ただし、サブクエリ内のWHERE句で「E1.sex = E2.sex」という縛り(バインド)が行われることで、性別に基づく最高年齢が選択されることになります。このクエリを全行について展開すると、実は中では図4のような動作をしています。ベン図で図示すると図5のようになります。
2つの図から明らかなように、サブクエリの戻り値が性別によって異なります。このように相関サブクエリの難しさの1つは、単純なサブクエリと違って単独で実行して戻り値を取り出して検証できない、という点にあります(せいぜいやるとしても、性別を「男」のように決めうちしてデバッグする程度でしょう)。
以上のことから、相関サブクエリには2つの側面があることがわかります。手続き言語的な観点から見た場合、相関サブクエリの持つ役割は「ループ隠し」です。一方、集合指向言語的な観点から見た場合、「集合のカット」です。どちらの方向から理解しても別にかまわないのですが、たぶん最初に頭に入りやすいのは前者ではないでしょうか。
集合をカットする基準を変化させてみる
さて、相関サブクエリの基礎は理解できたら、集合をカットする基準をいろいろと変化させてみましょう。たとえば、性別の代わりに部署を使えば、リスト3、図6のようになります。図示すると図7のようになります。
開発部は、最高齢の社員が2人(小川さんと長谷川さん)いるので、1つの部署につき複数行の結果が選択されます。これは、普通に考えれば当たり前のことのように思われます。しかし、カットされた部分集合について、複数の戻り値が設定されることは、基本的には論理的ですっきりしていますが、場合によって少し危険な事態を引き起こします。次に、その危険性について見ていきましょう。