SQL Server - 相関副問い合わせ(相関サブクエリ)

概要

相関サブクエリとは、外部クエリと内部クエリ(サブクエリ)が互いに関連付けられており、サブクエリが外部クエリの値を参照するSQLクエリのことである。

例えば、主問い合わせのFROM句にテーブルA、副問い合わせのFROM句にテーブルBがあるとする、
副問い合わせのWHERE句の条件に主問い合わせのテーブルAのカラムがある時、そのSQL文を相関副問い合わせ (相関サブクエリ) という。

相関副問い合わせは、主問い合わせのSQL文を実行した後、取得した行を1行ずつ副問い合わせの条件に設定してSQL文を実行する。
(主問い合わせのSQL文 -> 副問い合わせのSQL文)

副問い合わせのみの場合は、副問い合わせのSQL文を実行した後、主問い合わせのSQL文を実行する。
(副問い合わせのSQL文→主問合せのSQL文)

 SELECT * FROM <テーブルA>
 WHERE <テーブルAのカラム> >
 (
    SELECT <テーブルBのカラム> FROM <テーブルB>
    WHERE <テーブルAのカラム> = <テーブルBのカラム>
 );

自己結合での相関副問い合わせ

下表に示すテーブルがある。

T_Productテーブル

ID	Name	Category	Num
1	Apple	Fruit	10
2	Orange	Fruit	20
3	Carrote	Vegetable	30
4	Eggplant	Vegetable	40

以下の例では、各カテゴリごとに最も個数の多い商品の名前を表示している。

5行目のWHERE句の条件で、主問い合わせのa.categoryを条件にしているため、相関副問い合わせとなる。

1. 1行目を実行した後、その結果(レコード)を4行取得する。
2. 取得したレコードの1行目のCategoryカラムの値"Fruit"を3行目の条件とする。

 SELECT ID, Name from T_Product a
 WHERE a.Num =
 (
    SELECT MAX(b.Num) FROM T_Product b
    WHERE a.Category = b.Category
 );

結果は、以下の通りである。
フルーツで個数が最も大きい商品および野菜で個数が最も大きい商品を取得している。

ID     Name
2      Orange
4      Eggplant

以下のSQL文は、上記の相関副問い合わせのイメージである。

 SELECT ID, Name FROM T_Product a
 WHERE Num =
 (
    SELECT MAX(Num) FROM T_Product b
    WHERE b.Category = 'Fruit' OR b.Category = 'Vegetable'
 );

EXISTS句で相関副問い合わせ

下表に示すテーブルがある。

T_Productテーブル

ID	Name	Category	Num
1	Apple	Fruit	10
2	Orange	Fruit	20
3	Carrote	Vegetable	30
4	Eggplant	Vegetable	40

T_Salesテーブル

ID	Name
2	Orange
4	Eggplant

以下の例では、T_Salesテーブルに存在する商品で、T_Productテーブルでの個数が30以上の商品名を表示している。

5行目のWHERE句の条件において、主問い合わせのa.IDを条件にしているため、相関副問い合わせとなる。

1. まず、1行目を実行して、T_Salesテーブルの2行を取得する。
   
2. 次に、4行目〜5行目を実行して、T_ProductテーブルのIDとT_ProductテーブルのIDが同じ商品かつ個数が30個を超える商品を取得する。
   

4行目のSELECT文の後の1は使用されない。(任意の値を設定できる)

 SELECT Name FROM T_Sales a
 WHERE EXISTS
 (
    SELECT 1 FROM T_Product b
    WHERE b.Num > 30 AND a.ID = b.ID
 );

以下のSQL文は、上記の副問い合わせの実行イメージである。
条件は、まず、IDが2かつ個数が30以上で、次は、IDが4かつ個数が30以上の商品である。

 SELECT Name FROM T_Sales a
 WHERE EXISTS
 (
    SELECT 1 FROM T_Product b
    WHERE b.Num > 30 AND (2 = b.ID OR 4 = b.ID)
 );

結果は、以下の通りである。

Name
Eggplant