サブクエリとビュー
これから本ページで学ぶ「サブクエリ」は、ビューを基本とした技術である。サブクエリの特徴を一言で表すと、「使い捨てのビュー」である。
ビューは、データそのものを保存するのではなく、データを取り出すSELECT文だけを保存するという方法で、ユーザの利便性を高める道具であった。それに対してサブクエリとは、ビュー定義のSELECT文をそのままFROM句に持ち込んでしまったものである。
では、もう一度CategorySumビューのビュー定義と、ビューに対するSELECT文を見てみる。
CREATE VIEW CategorySum (category, cnt_chojin) AS SELECT category, COUNT(*) FROM chojin GROUP BY category;
ビューが作成されていることの確認。
SELECT category, cnt_chojin FROM CategorySum;
結果
+--------------+------------+ | category | cnt_chojin | +--------------+------------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+------------+
サブクエリでは以下のようになる。
SELECT category, cnt_chojin FROM (SELECT category, COUNT(*) AS cnt_chojin FROM chojin GROUP BY category) AS CategorySum;
得られる結果はどちらも同じである。
+--------------+------------+ | category | cnt_chojin | +--------------+------------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+------------+
見ての通り、ビュー定義のSELECT文をそのままFROM句の中に入れてしまったのがサブクエリである。「AS CategorySum」というのがこのサブクエリにつけられた名前であるが、これは使い捨ての名前なので、ビューのように記憶装置に保存されることはなく、SELECT文の実行終了後には消えてなくなる。サブクエリとは「下位の（sub）」の「問い合わせ（query）」という意味。
実際、このSELECT文は、入れ子構造になっていて、まずFROM句の中のSELECT文が実行され、その後に外側のSELECT文が実行される、という順番になる。
1.まずはFROM句の中のSELECT文（サブクエリ）が実行される
SELECT category, COUNT(*) AS cnt_chojin FROM chojin GROUP BY category;
2.の結果に対して、外側のSELECT文が実行される
SELECT category, cnt_chojin FROM CategorySum;
SELECT id, name, power FROM chojin WHERE power &> AVG(power);
結果
ERROR 1111 (HY000): Invalid use of group function
集約関数をWHERE句に書くことはできないという制限のため、このSELECT文は誤りとなる。
さて、ではいったい、どうすれば上記のような条件を満たすSELECT文を書くことができるのだろうか。
ここで、スカラ・サブクエリが力を発揮する。まず、chojinテーブルに含まれている超人の平均の超人強度（power）を求めるには、以下のSELECT文で可能である。
SELECT AVG(power) FROM chojin;
結果
+------------+ | AVG(power) | +------------+ | 1781.4091 | +------------+
ここで、SELECT文の検索結果がスカラ値であることは、一目瞭然である。したがって、その結果をそのまま、先程失敗したクエリの右辺に使うことが可能なのである。
SELECT id, name, power FROM chojin WHERE power &> (SELECT AVG(power) FROM chojin);
結果
+------+--------------------------------+-------+ | id | name | power | +------+--------------------------------+-------+ | 0021 | マックス・ラジアル | 4800 | | 0022 | ターボメン | 4000 | | 0023 | クラッシュマン | 3000 | | 0024 | ダルメシマン | 2500 | | 0025 | マーリンマン | 2200 | | 0026 | ピークア・ブー | 4200 | | 0027 | ストロング・ザ・武道 | 9999 | | 0028 | マーベラス | 1800 | | 0029 | ジャック・チー | 3500 | | 0031 | ネメシス | 6800 | | 0032 | ポーラマン | 7200 | | 0035 | ミラージュマン | 2200 | | 0036 | アビスマン | 2600 | | 0037 | ペインマン | 1800 | | 0039 | ガンマン | 3800 | | 0040 | シングマン | 3000 | | 0041 | カラスマン | 2000 | +------+--------------------------------+-------+
スカラ・サブクエリを書ける場所
スカラ・サブクエリを書ける場所は、何もWHERE句だけに限らない。基本的に、スカラ値が書けるところはどこにでも書くことができる。ということは、定数や列名を書くことのできる場所すべてとなり、SELECT句でもGROUP BY句でもHAVING句でもORDER BY句でも、ほとんどあらゆる場所に書くことが可能である。
例えば、SELECT句で先程の平均値を求めるスカラ・サブクエリを使えば、以下のようになる。
SELECT id, name, power, (SELECT AVG(power) FROM chojin) AS avg_power FROM chojin;
結果
+------+--------------------------------+-------+-----------+ | id | name | power | avg_power | +------+--------------------------------+-------+-----------+ | 0001 | キン肉マン | 95 | 1781.4091 | | 0002 | テリーマン | 95 | 1781.4091 | | 0003 | ロビンマスク | 96 | 1781.4091 | | 0004 | ウォーズマン | 100 | 1781.4091 | | 0005 | ラーメンマン | 97 | 1781.4091 | | 0006 | ブロッケンＪｒ | 90 | 1781.4091 | | 0007 | ウルフマン | 80 | 1781.4091 | | 0008 | ステカセキング | 250 | 1781.4091 | | 0009 | ブラックホール | 200 | 1781.4091 | | 0010 | ミスター・カーメン | 200 | 1781.4091 | | 0011 | ザ・魔雲天 | 50 | 1781.4091 | | 0012 | アトランティス | 250 | 1781.4091 | 以下省略
これは、超人一覧表の中に超人全体の平均超人強度も含めた結果、ということである。こういう帳票なども、ときとして求められることがある。
また、HAVING句に書くのなら、以下のようなSELECT文が考えられる。
SELECT category, AVG(power) FROM chojin GROUP BY category HAVING AVG(power) & > (SELECT AVG(power) FROM chojin);
結果
+--------------+------------+ | category | AVG(power) | +--------------+------------+ | 完璧超人 | 3268.1364 | +--------------+------------+
このクエリの意味は、「categoryごとに計算した平均超人強度が、超人全体の超人強度よりも高いcategoryを選択する」となる。
スカラ・サブクエリを使うときの注意点
最後に、スカラ・サブクエリを使うときに最も注意しなければならないのはことを取り上げる。それは「絶対にサブクエリが複数行を返さないようにする」ということである。というのも、サブクエリが複数行を返す時点ですでにそれはスカラ・サブクエリではなく、ただのサブクエリになってしまう。すると=、<>といったスカラ値を入力する演算子も利用できないし、SELECT句などに書くこともできなくなる。
例えば、次のサブクエリはエラーとなる。
SELECT id, name, power (SELECT AVG(power) FROM chojin GROUP BY category) AS avg_power FROM chojin;
結果
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 'SELECT AVG(power) FROM chojin GROUP BY category) AS avg_power FR' at line 2
エラーの理由は簡単で、このサブクエリは、次のような複数行を返すからである。
+------------+ | AVG(power) | +------------+ | 3268.1364 | | 448.4615 | | 72.5556 | +------------+
SELECT句の1行の中に3行を押し込むことは不可能な話である。そのため、上記SELECT文は「サブクエリが複数行を返すため実行できない」という理由のエラーが返されることになる。
