相関サブクエリ

では次に、単純に会社全体で一番高齢の社員を抽出する代わりに、男女別に最高齢の社員を抽出することを考えます。

手続き型言語で解く場合

もしEmployeesテーブルがCSV（Comma Separated Values：カンマ区切り）や固定長といった、ただのフラットファイル[1]で、手続き型言語でこの問題を解くとすれば、次のようなアルゴリズムを使うことになるでしょう。

1. max_ageという整数型の変数を用意し、0で初期化する
2. レコードを1行ずつ走査する
3. カレントレコードのsexが「男」ならば、ageとmax_ageを比較し、ageのほうが大きければ、max_ageにageを代入する
4. すべてのレコードについて走査したら、max_ageを男性の最高齢値として返す
5. 2.からの処理を、女性についても繰り返す

ここでは、男性と女性について2回分のループが発生しています。変数max_ageを男女別に用意しておくと、一度のループで済ませるよう改良できますが、いずれにせよレコードを1行ずつ処理することは必要です。

SQLで解く場合

一方、これと同じことをSQLで行おうとした場合、明示的にループを記述することはしません[2]⁠。代わりに、最高齢の値を求める集合の範囲を分割（限定）します。そのための方法が、相関サブクエリです（リスト2⁠）⁠。

SELECT *
  FROM Employees E1
 WHERE age = (SELECT MAX(age)
                FROM Employees E2
               WHERE E1.sex = E2.sex);

結果（図3）から明らかなように、このSELECT文は、正しく男女別に最高齢の社員を求めています。それでは、このクエリがどのようなロジックによって実行されているのか、段階を追って見ていきましょう。

emp_id  name   dept   sex    age
------- ------ ------ ------ -----
003     石川   営業   男     40
006     藤田   総務   女     50

行われている動作

リスト2の2つ目のSELECT文においても、最初に評価、実行されるのがサブクエリであるという点は変わりません。ただし、サブクエリ内のWHERE句で「E1.sex = E2.sex」という縛り（バインド）が行われることで、性別に基づく最高年齢が選択されることになります。このクエリを全行について展開すると、実は中では図4のような動作をしています。ベン図で図示すると図5のようになります。

SELECT '001', '橋本',   '営業', '男', 37 FROM Employees WHERE 37 = 40;
SELECT '002', '山下',   '営業', '男', 24 FROM Employees WHERE 24 = 40;
SELECT '003', '石川',   '営業', '男', 40 FROM Employees WHERE 40 = 40; ←ヒット！
SELECT '004', '中島',   '総務', '男', 35 FROM Employees WHERE 35 = 40;
SELECT '005', '前田',   '総務', '男', 26 FROM Employees WHERE 26 = 40;
―――――――――――――――――――――――――――――――――――― ←男女を分ける境界線
SELECT '006', '藤田',   '総務', '女', 50 FROM Employees WHERE 50 = 50;←ヒット！
SELECT '007', '小川',   '開発', '女', 29 FROM Employees WHERE 29 = 50;
SELECT '008', '長谷川', '開発', '女', 29 FROM Employees WHERE 29 = 50;

2つの図から明らかなように、サブクエリの戻り値が性別によって異なります。このように相関サブクエリの難しさの1つは、単純なサブクエリと違って単独で実行して戻り値を取り出して検証できない、という点にあります（せいぜいやるとしても、性別を「男」のように決めうちしてデバッグする程度でしょう⁠）⁠。

以上のことから、相関サブクエリには2つの側面があることがわかります。手続き言語的な観点から見た場合、相関サブクエリの持つ役割は「ループ隠し」です。一方、集合指向言語的な観点から見た場合、「⁠集合のカット」です。どちらの方向から理解しても別にかまわないのですが、たぶん最初に頭に入りやすいのは前者ではないでしょうか。

集合をカットする基準を変化させてみる

さて、相関サブクエリの基礎は理解できたら、集合をカットする基準をいろいろと変化させてみましょう。たとえば、性別の代わりに部署を使えば、リスト3、図6のようになります。図示すると図7のようになります。

SELECT *
  FROM Employees E1
 WHERE age = (SELECT MAX(age)
                FROM Employees E2
               WHERE E1.dept = E2.dept);

emp_id  name     dept   sex    age
------- -------- ------ ------ -----
003     石川     営業   男     40
006     藤田     総務   女     50
007     小川     開発   女     29
008     長谷川   開発   女     29

開発部は、最高齢の社員が2人（小川さんと長谷川さん）いるので、1つの部署につき複数行の結果が選択されます。これは、普通に考えれば当たり前のことのように思われます。しかし、カットされた部分集合について、複数の戻り値が設定されることは、基本的には論理的ですっきりしていますが、場合によって少し危険な事態を引き起こします。次に、その危険性について見ていきましょう。