技術とか戦略とか

証券レガシーシステムを8年間いじった普通のSEによるブログ。技術のみではなく趣味の戦略考察についても。PCから見た方が色々見やすいと思います。

情報処理技術者試験対策「SQL(副問合せ・相関副問合せ)」

今回は副問合せと相関副問合せの紹介です。
副問合せは試験だけでなく実務でも頻出です。
相関副問合せについては、副問合せでも同じ検索結果を出すことが可能なので実務では忘れがちかもしれませんが、試験には出題されますし、性能面を考慮すると相関副問合せを使った方が良いケースが多いです。
 
なお、今回は紹介しませんが、副問合せはFROM句にも書くことができます。
 
【文法】
・副問合せ(単一行を返す場合のみ使用可能)
…where カラム名 比較演算子 (select…)
 
・副問合せ(副問合せの何れかの結果と一致することを確認)
…where カラム名 IN (select…)
 
・副問合せ(副問合せのどの結果とも一致しないことを確認)
…where カラム名 NOT IN (select…)
 
・副問合せ(副問合せの全ての結果と比較し、全ての結果よりも(大きい、小さい等))
…where カラム名 比較演算子 ALL (select…)
 
・副問合せ(副問合せの何れかの結果よりも(大きい、小さい等))
…where カラム名 比較演算子 SOME (select…)
 ※SOMEの代わりにANYを使用しても良い
 
・相関問い合わせ(副問合せ部で存在する行のみ抽出)
…from テーブル1
…where EXISTS (select 1 FROM テーブル2 WHERE カラム名 = テーブル1.カラム名 …)
 ※上記は正式な文法ではないが、通常は上記のように使う
 ※EXISTSの前のNOTをつけると存在しない行のみ抽出することができる
 ※EXISTSの中のSELECT文のカラム指定は何でも良い
 
【対象テーブル例】
・商品
  +--------+--------+
  | 商品ID | 商品名 |
  +--------+--------+
  | 1      | 商品1 |
  | 2      | 商品2 |
  | 3      | 商品3 |
  | 4      | 商品4 |
  | 5      | 商品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;

+--------+
| 商品名 |
+--------+
| 商品5 |
+--------+
 
・不等号演算子
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;

+--------+
| 商品名 |
+--------+
| 商品3 |
| 商品5 |
+--------+
 
・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)

+--------+
| 商品名 |
+--------+
| 商品1 |
| 商品2 |
| 商品3 |
| 商品5 |
+--------+
 
・EXISTS句
select s.商品名
from   商品 as s
where  EXISTS (select 1
               from   消費者テスト結果 as t
               where  t.商品ID = s.商品ID)

+--------+
| 商品名 |
+--------+
| 商品1 |
| 商品2 |
| 商品3 |
| 商品5 |
+--------+
 ※上記のIN句の例と同じ結果となる
 
【性能について補足】
EXISTS句と同様のことはIN句でも可能だが、一般的にはEXISTS句の方が性能は良くなる傾向にある。
上記の例で言うと、IN句の場合は副問合せの結果(1,2,3,5)に対して主問合せで全件検索をかけるが、EXISTS句の場合は「t.商品ID = s.商品ID」の検索にインデックスを適用可能だからである。
(あくまでも一般論であることに注意する。DBMSによって挙動が異なることが予想され、IN句に対してインデックスが適用可能とする文献もある。)
 
---------------------
目次

https://1drv.ms/b/s!AivF3bzWXOzuhG1Xk5hscKYqkLkM