SQL 相関副問合せのサンプル(相関サブクエリ)

SQLの相関副問合せのサンプルです。

相関副問合せとは

主問い合わせのfrom句に「テーブルA」があります。
副問合せのfrom句に「テーブルB」があります。
→副問合せのwhere句の条件に主問い合わせの「テーブルAの項目」があります。
　→SQLは相関副問合せです。
相関副問合せは、主問い合わせのSQLを実行し、そこで取得した行を1行ずつ副問合せの条件にセットしてSQLを実行します。(主問い合わせのSQL→副問合せのSQL)
副問合せのみの場合は、副問合せのSQLを実行してから主問い合わせのSQLを実行します。
(副問い合わせのSQL→主問合せのSQL)
相関サブクエリとも呼ばれます。
SQL 副問合せのサンプル(サブクエリ)

自己結合での相関副問合せのサンプルです。

以下のテーブルがあるとします。

syouhinテーブル
id	name	category	kosuu
1	りんご	フルーツ	10
2	みかん	フルーツ	20
3	にんじん	野菜	30
4	大根	野菜	40

以下のSQLを実行します。

各カテゴリごとで最も個数の多い商品の名前を表示します。

select id,name from syouhin a
where kosuu =
 (select max(kosuu)from syouhin b where a.category = b.category);

3行目のwhereの条件で、主問い合わせのa.categoryを条件にしているので相関副問合せになります。

１．1行目を実行し、その結果として4行取得します。
２．その取得した行の1行目のcategoryの値「フルーツ」を3行目の条件とします。
a.categoryのaは、副問合せの中にありませんが可能です。

以下は、SQLの実行イメージです。

select id,name from syouhin a
where kosuu =
 (select max(kosuu)from syouhin b where フルーツ = b.category);

条件はカテゴリがフルーツで、個数が最大値のものが条件です。
次もカテゴリがフルーツで、個数が最大値のものが条件です。
次はカテゴリが野菜で、個数が最大値のものが条件です。
次もカテゴリが野菜で、個数が最大値のものが条件です。

結果は、以下のとおりです。

id	name
2	みかん
4	大根

フルーツで個数が最大のものと野菜で個数が最大のものを取得しました。

EXISTS句で相関副問合せのサンプルです。

以下のテーブルがあるとします。

salesテーブル
id	name
2	みかん
4	大根

syouhinテーブル
id	name	category	kosuu
1	りんご	フルーツ	10
2	みかん	フルーツ	20
3	にんじん	野菜	30
4	大根	野菜	40

salesテーブルに存在し、syouhinテーブルでの個数が30以上の商品の名前を表示します。

select name from sales a
    where exists (select 1 from syouhin b
        where b.kosuu > 30 and a.id = b.id);

3行目のwhereの条件で、主問い合わせのa.idを条件にしているので相関副問合せになります。
1行目を実行し、その結果としてsalesテーブルの2行を取得します。
2行目のselectの後の「1」は使用されません。任意の値を設定できます。1はよく使われます。

以下は、SQLの実行イメージです。

select name from sales a
    where exists (select 1 from syouhin b
        where b.kosuu > 30 and 2 = b.id);

条件はidが2で、個数が30以上のものが条件です。
次はidが4で、個数が30以上のものが条件です。

結果は、以下のとおりです。

name
大根