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. このプロセスが外部クエリの全てのレコードに対して繰り返される。

WHERE句を使用する場合

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

employeesテーブル

employee_id	employee_name	salary	department	hire_date
1	山田太郎	450,000	営業部	2020-04-01
2	鈴木一郎	380,000	営業部	2021-04-01
3	佐藤花子	370,000	経理部	2019-04-01
4	田中次郎	420,000	営業部	2018-04-01
5	高橋美咲	340,000	経理部	2022-04-01
6	伊藤健一	390,000	人事部	2020-04-01
7	渡辺直美	360,000	人事部	2021-04-01
8	小林正和	430,000	営業部	2019-04-01
9	加藤裕子	350,000	経理部	2022-04-01
10	山本和夫	400,000	人事部	2018-04-01

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

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

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

グループ化する場合

グループ化する場合は、JOINを使用する必要がある。

 SELECT e1.employee_name, e1.salary
 FROM employees e1
 JOIN (
    SELECT department, AVG(salary) as dept_avg
    FROM employees
    GROUP BY department
 ) e2 ON e1.department = e2.department
 WHERE e1.salary > e2.dept_avg;

このクエリの特徴を以下に示す。
これは、大量のデータを処理する場合に有効である。

• 相関サブクエリと比べて効率的である。

  部門ごとの平均計算は1回のみ

  インデックスを効果的に使用可能

• 可読性が高い。
• 拡張性が高い。 (他の条件や計算を追加しやすい)

 -- 内部クエリ (サブクエリ) の部分
 -- 部門ごとの平均給与を計算する
 SELECT department, AVG(salary) as dept_avg
 FROM employees
 GROUP BY department
 
 -- 結果
 department  | dept_avg
 ------------+---------
 営業部      | 400,000
 経理部      | 350,000
 人事部      | 380,000

 -- JOINの部分
 -- 元の従業員テーブル (e1) と、部門ごとの平均給与 (e2) を結合
 -- 各従業員のレコードに、その従業員の所属部門の平均給与が付加される 
 FROM employees e1
 JOIN (
    -- ...略
 ) e2 ON e1.department = e2.department
 
 -- 結合後のイメージ
 e1.name  | e1.salary | e1.department | e2.dept_avg
 ---------+-----------+---------------+-----------
 山田太郎  | 450,000   | 営業部         | 400,000
 鈴木一郎  | 380,000   | 営業部         | 400,000
 佐藤花子  | 370,000   | 経理部         | 350,000

 -- WHERE句の部分
 -- 結合後のデータから、給与が部門平均を上回る従業員のみを抽出する
 WHERE e1.salary > e2.dept_avg
 
 -- 最終結果
 name     | salary
 ---------+---------
 山田太郎  | 450,000
 佐藤花子  | 370,000

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

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

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