SQL副問合わせ・相関副問合わせの基礎と応用

今回は副問合せと相関副問合せの紹介です。
この文法を用いると、問い合わせの結果を別の問い合わせに使い回すことができるようになり、複雑な問い合わせを１本のSQLで書けるようになります。

副問合せは相関副問合せよりも文法的に簡単で、実務、特に運用作業では頻出です。
また、性能面を考慮すると相関副問合せを使った方が良いケースが多いため、相関副問合せもプログラミングでは良く使われます。

なお、今回は紹介しませんが、副問合せはFROM句にも書くことができます。

【文法】

・副問合せ（単一行を返す場合のみ使用可能）

…where カラム名 比較演算子 (select…)

1	…where カラム名比較演算子 (select…)

・副問合せ（副問合せの何れかの結果と一致することを確認）

…where カラム名 IN (select…)

1	…where カラム名 IN (select…)

・副問合せ（副問合せのどの結果とも一致しないことを確認）

…where カラム名 NOT IN (select…)

1	…where カラム名 NOT IN (select…)

・副問合せ（副問合せの全ての結果と比較し、全ての結果よりも（大きい、小さい等））

…where カラム名 比較演算子 ALL (select…)

1	…where カラム名比較演算子 ALL (select…)

・副問合せ（副問合せの何れかの結果よりも（大きい、小さい等））

…where カラム名 比較演算子 SOME (select…)

1	…where カラム名比較演算子 SOME (select…)

※SOMEの代わりにANYを使用しても良い

・相関問い合わせ（副問合せ部で存在する行のみ抽出）

…from テーブル１
…where EXISTS (select 1 FROM テーブル２ WHERE カラム名 = テーブル１.カラム名 …)

1 2	…from テーブル１ …where EXISTS (select 1 FROM テーブル２ WHERE カラム名 = テーブル１.カラム名 …)

※上記は正式な文法ではないが、通常は上記のように使う
※EXISTSの前のNOTをつけると存在しない行のみ抽出することができる
※EXISTSの中のSELECT文のカラム指定は問わない（1でなくても良い）

【対象テーブル例】

・商品

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 1      | 商品１ |
 | 2      | 商品２ |
 | 3      | 商品３ |
 | 4      | 商品４ |
 | 5      | 商品５ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 1 | 商品１ |

| 2 | 商品２ |

| 3 | 商品３ |

| 4 | 商品４ |

| 5 | 商品５ |

+--------+--------+

・消費者テスト結果

 +--------+------+
 | 商品ID | 得点 |
 +--------+------+
 | 1      | 80   |
 | 2      | 85   |
 | 3      | 90   |
 | 5      | 95   |
 +--------+------+

+--------+------+

| 商品ID | 得点 |

+--------+------+

| 1 | 80 |

| 2 | 85 |

| 3 | 90 |

| 5 | 95 |

+--------+------+

・合格基準点

 +------+
 | 得点 |
 +------+
 | 90   |
 +------+

+------+

| 得点 |

+------+

| 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.得点 = (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.得点 = 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.得点＞ (select k.得点

from 合格基準点 as k);

↓

select s.商品名
from   商品 as s
join   消費者テスト結果 as t
on    (s.商品ID = t.商品ID)
where  t.得点 ＞ 90;

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 (select t.商品ID

from 消費者テスト結果 as t);

↓

select s.商品名
from   商品 as s
where  s.商品ID IN (1,2,3,5);

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);

select s.商品名

from 商品 as s

where EXISTS (select 1

from 消費者テスト結果 as t

where t.商品ID = s.商品ID);

↓

+--------+
| 商品名 |
+--------+
| 商品１ |
| 商品２ |
| 商品３ |
| 商品５ |
+--------+

+--------+

| 商品名 |

+--------+

| 商品１ |

| 商品２ |

| 商品３ |

| 商品５ |

+--------+

※上記のIN句の例と同じ結果となる

【性能について補足】

EXISTS句と同様のことはIN句でも可能だが、性能面で違いが出ることが多い。
細かい所はRDBMSによって異なるため、最終的にはRDBMSの特性を調べたり実行計画を取得したりしながらチューニングするべきであるが、一般的に以下のようなことが言える。

１．実行順の違い

原則として件数の絞り込みはなるべく早い段階で行うべきであり、少ない件数に絞り込むことができる条件は先に実行するべきである。
例えば、100件中10件まで絞り込める条件と、100件中90件までしか絞り込めない条件がある場合、100件中10件まで絞り込める条件を先に実行すれば後に実行される条件はその10件（以下）のみ対象とすれば良くなるので、その方が性能が良くなる。
IN句とEXISTS句ではこの実行順が異なり、先に副問合せが実行されるのがIN句、後で副問合せが実行されるのはEXISTS句なので、副問合せで条件を絞り込めるか否かでどちらの句を使うのかを決めるべきである。

２．インデックスの使用有無

IN句、特にNOT IN句については、インデックスを使用しない逐次検索が行われてしまうことが多い。
そのため、一般論として、インデックスが使われやすいEXISTS句の方が性能が良くなることが多い。

いかがでしたでしょうか。

今回は、SQLでの副問合せと相関副問合せについて記事を書きました。
プログラムでSQLを発行する場合は複数回問い合わせを発行してその結果をプログラム側で突き合わせることの方が多いかもしれませんが、運用作業では重宝します。

複数の問い合わせを１つの問い合わせで済ませる文法としては、UNIONのような集合演算子も良く使います。
これは次回取り上げたいと思います！