普通のサブクエリと相関サブクエリの違い
「超人強度（powrer）が、全体の平均の超人強度よりも高い超人」を選び出すには、サブクエリを使えば実現できる。今度は少しこの条件を変えて「超人分類（category）ごとに平均超人強度より高い超人」を、categoryのグループから選び出すことを考えてみる。
超人分類ごとに平均超人強度を比較する
グループごとに「小分け」にしたうえで、そのグループ内の平均超人強度と各超人の超人強度を比較したい。
超人分類別に、平均超人強度を求めること自体、難しくはない。
SELECT AVG(power) FROM chojin GROUP BY category;
結果
+------------+ | AVG(power) | +------------+ | 3268.1364 | | 448.4615 | | 72.5556 | +------------+
しかし、スカラ・サブクエリのやり方にならって、このSELECT文をそのままサブクエリとしてWHERE句に書いてしまうと、エラーになってうまくいかない。
SELECT id, name, power FROM chojin WHERE power > (SELECT AVG(power) FROM chojin GROUP BY category);
結果
ERROR 1242 (21000): Subquery returns more than 1 row
エラーになる理由は、このサブクエリが3行を返してしまい、スカラ・サブクエリにならないからである。WHERE句でサブクエリを使用する場合は、必ず結果は1行である必要がある。
しかし、超人分類というグループ単位で超人強度と平均超人強度を比較する以上、これ以外書きようはない気がするが、どのようにすればよいか。
相関サブクエリを使った解決方法
ここで登場するのが、相関サブクエリです。
先ほどのSELECT文に1行追加するだけで、求められる結果を得られる正しいSELECT文に変身させることができる。
SELECT id, name, power FROM chojin AS C1 WHERE power > (SELECT AVG(power) FROM chojin AS C2 WHERE C1.category = C2.category GROUP BY category);
結果
+------+--------------------------------+-------+ | id | name | power | +------+--------------------------------+-------+ | 0001 | キン肉マン | 95 | | 0002 | テリーマン | 95 | | 0003 | ロビンマスク | 96 | | 0004 | ウォーズマン | 100 | | 0005 | ラーメンマン | 97 | | 0006 | ブロッケンＪｒ | 90 | | 0007 | ウルフマン | 80 | | 0014 | バッファローマン | 1000 | | 0016 | プラネットマン | 500 | | 0017 | ジャンクマン | 600 | | 0019 | アシュラマン | 1000 | | 0020 | サンシャイン | 700 | | 0021 | マックス・ラジアル | 4800 | | 0022 | ターボメン | 4000 | | 0026 | ピークア・ブー | 4200 | | 0027 | ストロング・ザ・武道 | 9999 | | 0029 | ジャック・チー | 3500 | | 0031 | ネメシス | 6800 | | 0032 | ポーラマン | 7200 | | 0039 | ガンマン | 3800 | +------+--------------------------------+-------+
これで正義超人、悪魔超人、完璧超人の3つの分類について、各グループの平均超人強度より高い超人を選択することができた。
ポイントは、サブクエリ内に追加したWHERE句の条件である。この意味を日本語で表現するならば、「各超人の超人強度と平均超人強度の比較を、同じ超人分類の中で行う」となる。
C1、C2というテーブルの別名は、今回、比較対象となるテーブル同士が同じchojinテーブルだったので、区別するために必要なものである。相関サブクエリの場合、こういったテーブルの別名を列名の前に「＜テーブル名＞.＜列名＞」の形式で記述する必要がある。
このように、相関サブクエリは、テーブル全体ではなく、テーブルの一部のレコード集合に限定した比較をしたい場合に使用する。したがって、相関サブクエリを使うとき、俗に「縛る」とか「制限する」という言い方をする。今回の例で言えば、「超人分類に縛って」平均超人強度との比較を行っている。
相関サブクエリも、結局は集合のカットをしている
相関サブクエリも、GROUP BY句と同じく、集合の「カット」という機能を持っていることが分かる。
それぞれの超人分類の中で平均超人強度が計算され、それが超人テーブルの各レコードと比較されるため、相関サブクエリは、レコードに対して実質的に1行しか返していない、とみなされる。これが相関サブクエリがエラーにならないカラクリである。
超人分類が変わると、比較する平均超人強度も変わる。このようにして各超人の超人強度と平均超人強度が比較される訳である。相関サブクエリは内部動作が見えにくいため、初心者にとっては理解しにくい機能として有名であるが、内部動作を見える化して追ってみると、意外に簡単であることが分かる。
結合条件は必ずサブクエリの中に書く
ここで、SQLの初心者が相関サブクエリを使うときに、よくやってしまう間違いを1つ紹介する。それは「縛る」ための結合条件をサブクエリの内側ではなく、外側に書いてしまうというものである。具体的には、以下。
SELECT id, name, power FROM chojin AS C1 WHERE C1.category = C2.category WHERE power > (SELECT AVG(power) FROM chojin AS C2 GROUP BY category);
結果
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE power > (SELECT AVG(power) FROM chojin AS C2 ' at line 4
これは、サブクエリの中にあった条件を、外側に移動させただけで、その他は何の変更も加えていない。ところが、このSELECT文はエラーになって正しく実行できない。こういう書き方が許されてもおかしくなさそうなのに、SQLのルールで禁止されている。
ではいったいそれはどのようなルールかというと、相関名のスコープである。相関名というのは、C1やC2など、テーブルの別名としてつけた名前である。そして、スコープとは、生存範囲である。つまり、相関名には、それが通用する範囲に制限がある、ということなのである。
具体的には、サブクエリ内部でつけられた相関名は、そのサブクエリ内でしか使用できない、ということである。別の言い方をすると、「内から外は見えるが、外から内は見えない」ということである。
このように、相関名には、それが有効な範囲が存在する、ということを忘れないでいただきたい。SQLは、以前も説明したように、内側のサブクエリから外側へ向かって実行される。そうすると、サブクエリが実行され終わったときには、実行結果だけが残って、抽出元となったテーブルC2は消えてなくなるのである。そのため、サブクエリの外側が実行されるタイミングでは、もうC2は存在しなくなっていて、「そんな名前のテーブルはありません」というエラーが返されることになるのである。
では、今度は超人分類別に平均身長を求めてみる。
SELECT category, AVG(height) FROM chojin GROUP BY category;
結果
+--------------+-------------+ | category | AVG(height) | +--------------+-------------+ | 完璧超人 | 230.6364 | | 悪魔超人 | 215.4615 | | 正義超人 | 199.4286 | +--------------+-------------+
では相関サブクエリを使って、各超人分類の平均身長より高い身長の超人を選択する。
SELECT category, name, height FROM chojin AS C1 WHERE height > (SELECT AVG(height) FROM chojin AS C2 WHERE C1.category = C2.category GROUP BY category);
結果
+--------------+--------------------------------+--------+ | category | name | height | +--------------+--------------------------------+--------+ | 正義超人 | ロビンマスク | 217 | | 正義超人 | ウォーズマン | 210 | | 正義超人 | ラーメンマン | 209 | | 悪魔超人 | ザ・魔雲天 | 285 | | 悪魔超人 | バッファローマン | 250 | | 悪魔超人 | プラネットマン | 254 | | 悪魔超人 | サンシャイン | 300 | | 完璧超人 | マックス・ラジアル | 256 | | 完璧超人 | クラッシュマン | 283 | | 完璧超人 | ストロング・ザ・武道 | 290 | | 完璧超人 | ポーラマン | 278 | | 完璧超人 | ミラージュマン | 263 | | 完璧超人 | ガンマン | 302 | | 完璧超人 | シングマン | 298 | | 完璧超人 | カラスマン | 260 | +--------------+--------------------------------+--------+
