今回は副問合せと相関副問合せの紹介です。
副問合せは試験だけでなく実務でも頻出です。
相関副問合せについては、副問合せでも同じ検索結果を出すことが可能なので実務では忘れがちかもしれませんが、試験には出題されますし、性能面を考慮すると相関副問合せを使った方が良いケースが多いです。
なお、今回は紹介しませんが、副問合せはFROM句にも書くことができます。
【文法】
・副問合せ（単一行を返す場合のみ使用可能）
…where カラム名 比較演算子 (select…)
・副問合せ（副問合せの何れかの結果と一致することを確認）
…where カラム名 IN (select…)
・副問合せ（副問合せのどの結果とも一致しないことを確認）
…where カラム名 NOT IN (select…)
・副問合せ（副問合せの全ての結果と比較し、全ての結果よりも（大きい、小さい等））
…where カラム名 比較演算子 ALL (select…)
・副問合せ（副問合せの何れかの結果よりも（大きい、小さい等））
…where カラム名 比較演算子 SOME (select…)
※SOMEの代わりにANYを使用しても良い
・相関問い合わせ（副問合せ部で存在する行のみ抽出）
…from テーブル１
…where EXISTS (select 1 FROM テーブル２ WHERE カラム名 = テーブル１.カラム名 …)
※上記は正式な文法ではないが、通常は上記のように使う
※EXISTSの前のNOTをつけると存在しない行のみ抽出することができる
※EXISTSの中のSELECT文のカラム指定は何でも良い
【対象テーブル例】
・商品
+--------+--------+
| 商品ID | 商品名 |
+--------+--------+
| 1 | 商品１ |
| 2 | 商品２ |
| 3 | 商品３ |
| 4 | 商品４ |
| 5 | 商品５ |
+--------+--------+
・消費者テスト結果
+--------+------+
| 商品ID | 得点 |
+--------+------+
| 1 | 80 |
| 2 | 85 |
| 3 | 90 |
| 5 | 95 |
+--------+------+
・合格基準点
+------+
| 得点 |
+------+
| 90 |
+------+
【使用例】
asは省略可能である。
実務でも試験でも使用頻度が高い文法のみ例を記載する。
・=演算子
select s.商品名
from 商品 as s
join 消費者テスト結果 as t
on (s.商品ID = t.商品ID)
where t.得点 = (select max(t_tmp.得点)
from 消費者テスト結果 as t_tmp);
↓
select s.商品名
from 商品 as s
join 消費者テスト結果 as t
on (s.商品ID = t.商品ID)
where t.得点 = 95;
↓
+--------+
| 商品名 |
+--------+
| 商品５ |
+--------+
・不等号演算子
select s.商品名
from 商品 as s
join 消費者テスト結果 as t
on (s.商品ID = t.商品ID)
where t.得点 > (select k.得点
from 合格基準点 as k)
↓
select s.商品名
from 商品 as s
join 消費者テスト結果 as t
on (s.商品ID = t.商品ID)
where t.得点 > 90;
↓
+--------+
| 商品名 |
+--------+
| 商品３ |
| 商品５ |
+--------+
・IN句
select s.商品名
from 商品 as s
where s.商品ID IN (select t.商品ID
from 消費者テスト結果 as t)
↓
select s.商品名
from 商品 as s
where s.商品ID IN (1,2,3,5)
↓
+--------+
| 商品名 |
+--------+
| 商品１ |
| 商品２ |
| 商品３ |
| 商品５ |
+--------+
・EXISTS句
select s.商品名
from 商品 as s
where EXISTS (select 1
from 消費者テスト結果 as t
where t.商品ID = s.商品ID)
↓
+--------+
| 商品名 |
+--------+
| 商品１ |
| 商品２ |
| 商品３ |
| 商品５ |
+--------+
※上記のIN句の例と同じ結果となる
【性能について補足】
EXISTS句と同様のことはIN句でも可能だが、一般的にはEXISTS句の方が性能は良くなる傾向にある。
上記の例で言うと、IN句の場合は副問合せの結果(1,2,3,5)に対して主問合せで全件検索をかけるが、EXISTS句の場合は「t.商品ID = s.商品ID」の検索にインデックスを適用可能だからである。
（あくまでも一般論であることに注意する。DBMSによって挙動が異なることが予想され、IN句に対してインデックスが適用可能とする文献もある。）
---------------------
目次
