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

前回と異なる副問い合わせの活用法として、IN と EXISTS を取り扱う。

IN

IN は入門編で既に出てきたが、IN ( 副問い合わせ ) の形で副問い合わせが活躍する。次の例は「初春飾利」と同じ組織に属する構成員の名前とその所属組織を表示する。

SELECT NAME, TEAM FROM AFF AS A WHERE TEAM IN ( SELECT TEAM FROM AFF AS B WHERE B.NAME=’初春飾利’ ) ORDER BY TEAM ;

副問い合わせで「初春飾利」の所属する組織（ジャッジメント、柵川中）が列挙される。IN の中身は複数であっても良いので、副問い合わせの結果が複数でも問題ない。所属しない組織を列挙する場合は否定の NOT IN でよい。（ NOT ( TEAM IN \( \cdots \) ) でも良いが NOT IN の方が簡潔だろう）

SELECT NAME, TEAM FROM AFF AS A WHERE TEAM NOT IN ( SELECT TEAM FROM AFF AS B WHERE B.NAME=’初春飾利’ ) ORDER BY TEAM ;

解答例
(1) SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE TEAM IN ( SELECT TEAM FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE B.SKILL = ‘エアロハンド’ ) ORDER BY TEAM, SKILL ;

(2) SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE TEAM IN ( SELECT DISTINCT TEAM FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE B.SKILL IS NULL ) ORDER BY TEAM ;

(3) SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE TEAM NOT IN ( SELECT DISTINCT TEAM FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE B.SKILL IS NULL ) ORDER BY TEAM ;

(4) SELECT NAME, LEVEL, TEAM FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE TEAM IN ( SELECT DISTINCT TEAM FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE B.LEVEL = 0 ) ORDER BY TEAM, LEVEL DESC ;

EXISTS

EXISTS は直後の副問い合わせの結果が空であるかどうかを判定する。何か検索結果があれば真であり、なければ偽である。結果の有無しか確認しないので、副問い合わせは常に SELECT * で始めればよい。具体例で見よう。IN で見た例を EXISTS を使って書き直す。

SELECT NAME, TEAM FROM AFF AS A WHERE EXISTS ( SELECT * FROM AFF AS B WHERE A.TEAM = B.TEAM AND B.NAME = ‘初春飾利’ ) ORDER BY TEAM ;

「初春飾利」が所属していない組織を検索するなら、次のように EXISTS の前に NOT をつけて否定すればよい。

SELECT NAME, TEAM FROM AFF AS A WHERE NOT EXISTS ( SELECT * FROM AFF AS B WHERE A.TEAM = B.TEAM AND B.NAME = ‘初春飾利’ ) ORDER BY TEAM ;

解答例
(1) SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE EXISTS ( SELECT * FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE A.TEAM = B.TEAM AND B.SKILL = ‘エアロハンド’ ) ORDER BY TEAM, SKILL ;

(2) SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE EXISTS ( SELECT * FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE A.TEAM = B.TEAM AND B.SKILL IS NULL ) ORDER BY TEAM ;

(3) SELECT NAME, SKILL FROM STATUS AS A WHERE EXISTS ( SELECT * FROM STATUS AS B WHERE A.SKILL = B.SKILL AND A.NAME <> B.NAME ) ORDER BY SKILL ;

(4) SELECT NAME, LEVEL, TEAM FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE EXISTS ( SELECT * FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE A.LEVEL = B.LEVEL AND A.TEAM = B.TEAM AND A.NAME <> B.NAME ) ORDER BY TEAM, LEVEL DESC ;

(5) SELECT NAME, LEVEL FROM STATUS AS A WHERE NOT EXISTS ( SELECT * FROM STATUS AS B WHERE A.LEVEL = B.LEVEL AND A.NAME <> B.NAME ) ORDER BY LEVEL ;

(6) SELECT NAME FROM STATUS WHERE NOT EXISTS ( SELECT * FROM AFF WHERE STATUS.NAME = AFF.NAME ) ;
(以前に求めたときは左外部結合を用いた)

EXISTS, IN, 集合関数

課題(2) 「能力名が登録されていない構成員が所属している組織に属する人物の名前、所属組織名、能力名を表示せよ」では IN, EXISTS を用いて解答したが、集合関数を用いることも可能である。３つを比較してみよう。上から順に、EXISTS, IN, 集合関数を使った問い合わせで、赤字の部分が異なる。

SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE EXISTS ( SELECT * FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE A.TEAM = B.TEAM AND B.SKILL IS NULL ) ORDER BY TEAM ;

SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE TEAM IN ( SELECT DISTINCT TEAM FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE B.SKILL IS NULL ) ORDER BY TEAM ;

SELECT NAME, TEAM, SKILL FROM ( STATUS NATURAL JOIN AFF ) AS A WHERE ( SELECT COUNT(*) FROM ( STATUS NATURAL JOIN AFF ) AS B WHERE A.TEAM = B.TEAM AND B.SKILL IS NULL ) > 0 ORDER BY TEAM ;

下の２つは副問い合わせで全ての行をチェックしなければ完結できないが、SELECT の場合は１つでも該当するものが見つかればそこで打ち切ることが可能である。IN の場合は ( ) 内を求めるために、集合関数の場合も COUNT(*) を求めるために、全ての行を調べる必要がある。SELECT の方が探索時間が短いと期待され、SELECT を利用する方が望ましいだろう。