超電磁砲でデータベース（副問い合わせ編１）

副問い合わせ

副問い合わせとは入れ子になった問い合わせのことである。具体例で見てみよう。次の例は同じレベルの人物が登録されていない人物の名前とそのレベルを表示する。

SELECT NAME, LEVEL FROM STATUS AS A WHERE ( SELECT COUNT(*) FROM STATUS AS B WHERE A.LEVEL = B.LEVEL ) = 1 ;

赤字の部分が副問い合わせである。WHERE 句の中に問い合わせらしきものがある。上の場合、全体としては次の構造になっている。

SELECT NAME, LEVEL FROM STATUS AS A WHERE 条件部 ;

この問い合わせの赤字部分を解説する。まず、STATUS AS A の部分を解説しよう。妙なのは STATUS の後ろについている AS A である。以前に NAME AS ‘名前’ で現れた AS がテーブル名にも使える。これは相関名と呼ばれ、テーブル名の別名を与えている。AS は省略可である。相関名が必要になる典型的な場合は、同じテーブルが複数使われる場合である。この例の場合も STATUS が２回現れている。また、単に長いテーブル名を何度も書きたくないという理由で相関名を使うこともあるだろう。

さて、本題の副問い合わせの説明である。まずは、問い合わせのアルゴリズムを見てみよう。A と相関名をつけられた STATUS テーブルの各行が条件部を満たすかどうかを判定して、満たせばその行が（加工されて）出力される。条件部が複雑になると、更に問い合わせが必要になることがある。ここでは A の行 ( ‘初春飾利’, 1, ‘サーマルハンド’ ) を判定するとする。初春飾利はレベル１なので、レベル１が１人ならば条件を満たすことになる。そこで、レベル１の人数を数えるために次の問い合わせを行い、結果が１であればよい。

SELECT COUNT(*) FROM STATUS AS B WHERE B.LEVEL = 1 ;

一般の場合の行は ( A.NAME, A.LEVEL, A.SKILL ) と考える。すると、副問い合わせのレベルが１とした部分を A.LEVEL で置き換えればよいから

SELECT COUNT(*) FROM STATUS AS B WHERE B.LEVEL = A.LEVEL ;

となる。実際の副問い合わせは、最後の等式の順序を入れ替えただけである。この例のように、単一の値を返す副問い合わせの使い方は、比較的易しい使用法である。

解答例
(1) SELECT NAME, LEVEL FROM STATUS WHERE LEVEL = ( SELECT LEVEL FROM STATUS WHERE NAME = ‘御坂美琴’ ) ;
(参考) SELECT B.NAME, B.LEVEL FROM STATUS AS A, STATUS AS B WHERE A.LEVEL = B.LEVEL AND A.NAME = ‘御坂美琴’ ;
(参考では相関名を使ってはいるが、副問い合わせは使っていない。相関名が出てきたので、自己結合という手法を紹介してみた。)

(2) SELECT NAME, SKILL FROM STATUS AS A WHERE ( SELECT COUNT(*) FROM STATUS AS B WHERE A.SKILL = B.SKILL ) > 1 ORDER BY SKILL ;

(3) SELECT NAME, TEAM FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE ( SELECT COUNT(*) FROM AFF AS B GROUP BY TEAM HAVING A.TEAM = B.TEAM ) > 4 ORDER BY TEAM, LEVEL DESC ;

(4) SELECT NAME, LEVEL, TEAM FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE LEVEL = ( SELECT MAX(LEVEL) FROM ( STATUS NATURAL JOIN AFF ) AS B GROUP BY TEAM HAVING A.TEAM = B.TEAM ) ORDER BY LEVEL DESC, TEAM ;

(3), (4) で STATUS NATURAL JOIN AFF AS A と ( ) を外してしまうと、STATUS NATURAL JOIN ( AFF AS A ) と解釈され A は AFF の相関名となってしまう。