SQL Server - 相関副問い合わせ(相関サブクエリ)のソースを表示

== 概要 ==
相関サブクエリとは、外部クエリと内部クエリ(サブクエリ)が互いに関連付けられており、サブクエリが外部クエリの値を参照するSQLクエリのことである。<br>
<br>
例えば、主問い合わせのFROM句にテーブルA、副問い合わせのFROM句にテーブルBがあるとする、<br>
副問い合わせのWHERE句の条件に主問い合わせのテーブルAのカラムがある時、そのSQL文を相関副問い合わせ (相関サブクエリ) という。<br>
<br>
相関副問い合わせは、主問い合わせのSQL文を実行した後、取得した行を1行ずつ副問い合わせの条件に設定してSQL文を実行する。<br>
(主問い合わせのSQL文 -> 副問い合わせのSQL文)<br>
<br>
副問い合わせのみの場合は、副問い合わせのSQL文を実行した後、主問い合わせのSQL文を実行する。<br>
(副問い合わせのSQL文→主問合せのSQL文)<br>
 <syntaxhighlight lang="sql">
 SELECT * FROM <テーブルA>
 WHERE <テーブルAのカラム> >
 (
    SELECT <テーブルBのカラム> FROM <テーブルB>
    WHERE <テーブルAのカラム> = <テーブルBのカラム>
 );
 </syntaxhighlight>
<br><br>

== 実行プロセス ==
相関サブクエリの実行プロセスを以下に示す。
# まず、外部クエリが実行されて、処理対象の行レコードが特定される。
# 外部クエリの各レコードに対して、
#* そのレコードの値を使用して内部クエリ (サブクエリ) が実行される。
#* サブクエリの結果を使用して、その行を最終結果に含めるかどうかが判断される。
# このプロセスが外部クエリの全てのレコードに対して繰り返される。
<br>
==== WHERE句を使用する場合 ====
# 外部クエリがemployeesテーブルから最初の従業員レコードを取得する。
# その従業員のdepartmentカラムを使用して、サブクエリがその部門の平均給与を計算する。
# 従業員のsalaryカラムと計算された平均給与を比較する。
# 条件を満たせば結果セットに含める。
# 次の従業員レコードに移動して、2〜4を繰り返す。
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ employeesテーブル
! style="background-color:#00ffff;" | employee_id 
! style="background-color:#00ffff;" | employee_name 
! style="background-color:#00ffff;" | salary 
! style="background-color:#00ffff;" | department 
! style="background-color:#00ffff;" | 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
|}
</center>
<br>
 <syntaxhighlight lang="sql">
 SELECT e1.employee_name, e1.salary
 FROM employees e1
 WHERE e1.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department = e2.department
 );
 </syntaxhighlight>
<br>
このような動作特性から、以下に示すことがわかる。<br>
* 外部クエリの各レコードに対してサブクエリが実行されるため、処理時間が長くなる。
* データ量が多い場合、パフォーマンスへの影響を考慮する必要がある。
* インデックスの適切な設定が重要になる。
<br>
====  グループ化する場合 ====
グループ化する場合は、JOINを使用する必要がある。<br>
<br>
 <syntaxhighlight lang="sql">
 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;
 </syntaxhighlight>
<br>
このクエリの特徴を以下に示す。<br>
これは、大量のデータを処理する場合に有効である。<br>
* 相関サブクエリと比べて効率的である。
*: 部門ごとの平均計算は1回のみ
*: インデックスを効果的に使用可能
* 可読性が高い。
* 拡張性が高い。 (他の条件や計算を追加しやすい)
<br>
 <syntaxhighlight lang="sql">
 -- 内部クエリ (サブクエリ) の部分
 -- 部門ごとの平均給与を計算する
 SELECT department, AVG(salary) as dept_avg
 FROM employees
 GROUP BY department
 
 -- 結果
 department  | dept_avg
 ------------+---------
 営業部      | 400,000
 経理部      | 350,000
 人事部      | 380,000
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="sql">
 -- 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
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="sql">
 -- WHERE句の部分
 -- 結合後のデータから、給与が部門平均を上回る従業員のみを抽出する
 WHERE e1.salary > e2.dept_avg
 
 -- 最終結果
 name     | salary
 ---------+---------
 山田太郎  | 450,000
 佐藤花子  | 370,000
 </syntaxhighlight>
<br><br>

== 自己結合での相関副問い合わせ ==
下表に示すテーブルがある。<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|+ T_Productテーブル
|-
! style="background-color:#66CCFF;" | ID 
! style="background-color:#66CCFF;" | Name 
! style="background-color:#66CCFF;" | Category 
! style="background-color:#66CCFF;" | Num
|-
| 1 || Apple || Fruit || 10
|-
| 2 || Orange || Fruit || 20
|-
| 3 || Carrote || Vegetable || 30
|-
| 4 || Eggplant || Vegetable || 40
|}
</center>
<br>
以下の例では、各カテゴリごとに最も個数の多い商品の名前を表示している。<br>
<br>
5行目のWHERE句の条件で、主問い合わせのa.categoryを条件にしているため、相関副問い合わせとなる。<br>
# 1行目を実行した後、その結果(レコード)を4行取得する。
# 取得したレコードの1行目のCategoryカラムの値"Fruit"を3行目の条件とする。
 <syntaxhighlight lang="sql">
 SELECT ID, Name from T_Product a
 WHERE a.Num =
 (
    SELECT MAX(b.Num) FROM T_Product b
    WHERE a.Category = b.Category
 );
 </syntaxhighlight>
<br>
結果は、以下の通りである。<br>
フルーツで個数が最も大きい商品および野菜で個数が最も大きい商品を取得している。<br>
 ID     Name
 2      Orange
 4      Eggplant
<br>
以下のSQL文は、上記の相関副問い合わせのイメージである。<br>
 <syntaxhighlight lang="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'
 );
 </syntaxhighlight>
<br><br>

== EXISTS句で相関副問い合わせ ==
下表に示すテーブルがある。<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|+ T_Productテーブル
|-
! style="background-color:#66CCFF;" | ID 
! style="background-color:#66CCFF;" | Name 
! style="background-color:#66CCFF;" | Category 
! style="background-color:#66CCFF;" | Num
|-
| 1 || Apple || Fruit || 10
|-
| 2 || Orange || Fruit || 20
|-
| 3 || Carrote || Vegetable || 30
|-
| 4 || Eggplant || Vegetable || 40
|}
</center>
<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|+ T_Salesテーブル
|-
! style="background-color:#66CCFF;" | ID 
! style="background-color:#66CCFF;" | Name 
|-
| 2 || Orange 
|-
| 4 || Eggplant 
|}
</center>
<br>
以下の例では、T_Salesテーブルに存在する商品で、T_Productテーブルでの個数が30以上の商品名を表示している。<br>
<br>
5行目のWHERE句の条件において、主問い合わせのa.IDを条件にしているため、相関副問い合わせとなる。<br>
# まず、1行目を実行して、T_Salesテーブルの2行を取得する。<br>
# 次に、4行目〜5行目を実行して、T_ProductテーブルのIDとT_ProductテーブルのIDが同じ商品かつ個数が30個を超える商品を取得する。<br>
<br>
4行目のSELECT文の後の1は使用されない。(任意の値を設定できる)<br>
 <syntaxhighlight lang="sql">
 SELECT Name FROM T_Sales a
 WHERE EXISTS
 (
    SELECT 1 FROM T_Product b
    WHERE b.Num > 30 AND a.ID = b.ID
 );
 </syntaxhighlight>
<br>
以下のSQL文は、上記の副問い合わせの実行イメージである。<br>
条件は、まず、IDが2かつ個数が30以上で、次は、IDが4かつ個数が30以上の商品である。<br>
 <syntaxhighlight lang="sql">
 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)
 );
 </syntaxhighlight>
<br>
結果は、以下の通りである。<br>
 Name
 Eggplant
<br><br>

{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,Podman,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:SQL_Server]]