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のカラム>
 );

実行プロセス

相関サブクエリの実行プロセスを以下に示す。

1. まず、外部クエリが実行されて、処理対象の行レコードが特定される。
2. 外部クエリの各レコードに対して、
   • そのレコードの値を使用して内部クエリ (サブクエリ) が実行される。
   • サブクエリの結果を使用して、その行を最終結果に含めるかどうかが判断される。
3. このプロセスが外部クエリの全てのレコードに対して繰り返される。

 SELECT e1.employee_name, e1.salary
 FROM employees e1
 WHERE e1.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department = e2.department
 );

1. 外部クエリがemployeesテーブルから最初の従業員レコードを取得する。
2. その従業員のdepartmentカラムを使用して、サブクエリがその部門の平均給与を計算する。
3. 従業員のsalaryカラムと計算された平均給与を比較する。
4. 条件を満たせば結果セットに含める。
5. 次の従業員レコードに移動して、2〜4を繰り返す。

このような動作特性から、以下に示すことがわかる。

• 外部クエリの各レコードに対してサブクエリが実行されるため、処理時間が長くなる。
• データ量が多い場合、パフォーマンスへの影響を考慮する必要がある。
• インデックスの適切な設定が重要になる。

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

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

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