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

== 概要 ==
副問い合わせ(サブクエリ)とは、<code>SELECT</code>文の結果を別のSQL文で使用することである。<br>
副問い合わせは、<code>INSERT</code>文、<code>UPDATE</code>文、<code>DELETE</code>文でも使用できる。<br>
<br>
以下に、<code>SELECT</code>文の使用時の副問合せが記述できる箇所を示す。<br>
* SELECT句に記述する副問合せ
* FROM句に記述する副問合せ
* WHERE句に記述する副問合せ
* HAVING句に記述する副問合せ
<br>
ここでは、例で使用するテーブルを下表に示す。<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|+ T_Sampleテーブル
|-
! style="background-color:#66CCFF;" | Name 
! style="background-color:#66CCFF;" | Date 
! style="background-color:#66CCFF;" | Sales
|-
| A || 20210101 || 200
|-
| B || 20210102 || 150
|-
| C || 20210103 || 100
|}
</center>
<br><br>

== SELECT句にある副問合せ ==
副問合せで記述すべきクエリとは、以下に示すようなものである。<br>
* インデックスを活用できるシンプルなサブクエリ
* 明確な目的を持った相関サブクエリ
* 集計関数を使用した効率的な計算
<br>
<code>SELECT</code>句にある副問い合わせの例を示す。<br>
 <syntaxhighlight lang="sql">
 SELECT Name, Date, Sales,
        Sales > (SELECT AVG(Sales) FROM T_Sample ) as AboveAverage  -- 売上の平均を求める副問い合わせ
                                                                    -- AboveAverageカラムには、売上の平均を超えている場合は1、それ以外の場合は0が代入される
 FROM T_Sample;
 </syntaxhighlight>
<br>
 # 結果
 Name     Date     Sales     AboveAverage
 A        20210101 200       1
 A        20210102 150       0
 B        20210103 100       0
<br>
==== スカラーサブクエリ (単一の値を返す) ====
 <syntaxhighlight lang="sql">
 -- 最小売上との差分を計算
 SELECT Name, Sales, 
        Sales - (SELECT MIN(Sales) FROM T_Sample) as DiffFromMin
 FROM T_Sample;
 </syntaxhighlight>
<br>
==== 相関サブクエリ (外部クエリの値を参照) ====
* サブクエリで外部クエリのテーブル (t1) を参照している。
*: t2.Date < t1.Date
* サブクエリが外部クエリの各行に対して実行される。
* スカラサブクエリとして使用している。
*: 単一の値を返す。
<br>
# 外部クエリがT_Sampleテーブルの各レコード (t1) を処理する。
# 各レコードに対して、その行の日付より前の全ての売上の平均を計算する。
# 結果として、各レコードの名前、日付、売上、その日付以前の平均売上が表示される。
<br>
これは時系列分析でよく使用されるパターンであり、各時点での過去の平均値を計算するものである。<br>
<br>
 <syntaxhighlight lang="sql">
 -- 各レコードより前の日付の平均売上を計算
 SELECT Name, Date, Sales,
        (SELECT AVG(Sales) 
         FROM T_Sample t2 
         WHERE t2.Date < t1.Date) as PriorAvgSales
 FROM T_Sample t1;
 </syntaxhighlight>
<br>
* サブクエリで外部クエリのテーブル (t1) を参照している。
*: t2.Sales = t1.Sales
* サブクエリが外部クエリの各行に対して実行される。
* EXIST句を使用している。
<br>
 <syntaxhighlight lang="sql">
 -- 売上が存在するレコードを取得
 SELECT * FROM T_Sample t1
 WHERE EXISTS (
    SELECT 1 FROM T_Sample t2
    WHERE t2.Sales = t1.Sales
 );
 </syntaxhighlight>
<br>
==== IN句 / EXISTS句 / ANY句 / ALL句 ====
 <syntaxhighlight lang="sql">
 -- 平均以上の売上のレコードを取得
 SELECT * FROM T_Sample 
 WHERE Sales > ANY (
    SELECT Sales FROM T_Sample 
    WHERE Sales > 150
 );
 </syntaxhighlight>
<br>
==== FROM句のサブクエリ ====
 <syntaxhighlight lang="sql">
 -- 日付ごとの累計売上を計算
 SELECT t1.Date, t1.Sales,
        (SELECT SUM(t2.Sales) 
         FROM T_Sample t2 
         WHERE t2.Date <= t1.Date) as CumulativeSales
 FROM T_Sample t1;
 </syntaxhighlight>
<br>
==== アンチパターン ====
* 過度に複雑なネストされたサブクエリ
 <syntaxhighlight lang="sql">
 -- 避けるべき例
 SELECT * FROM T_Sample 
 WHERE Sales > (
    SELECT AVG(Sales) FROM (
       SELECT Sales FROM T_Sample 
       WHERE Sales > (
          SELECT MIN(Sales) FROM T_Sample
       )
    )
 );
 </syntaxhighlight>
<br>
* JOINで代替可能な非効率なサブクエリ
 <syntaxhighlight lang="sql">
 -- 非効率な例
 SELECT * FROM T_Sample t1
 WHERE EXISTS (
    SELECT 1 FROM T_Sample t2
    WHERE t2.Name = t1.Name
 );
 
 -- 推奨される記述方法
 SELECT DISTINCT t1.* 
 FROM T_Sample t1
 JOIN T_Sample t2 ON t1.Name = t2.Name;
 </syntaxhighlight>
<br>
* 不必要なサブクエリの使用
 <syntaxhighlight lang="sql">
 -- 非効率な例
 SELECT Name, 
        (SELECT MAX(Sales) FROM T_Sample) as MaxSales
 FROM T_Sample;
 
 -- 推奨される記述方法
 SELECT Name, MAX(Sales) OVER() as MaxSales
 FROM T_Sample;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>パフォーマンスの観点から、以下に示すことに注意が必要である。</u><br>
* サブクエリは適切なインデックスが存在するか確認する。
* 可能な限りJOINやウィンドウ関数で代替することを検討する。
* 実行計画を確認し、パフォーマンスへの影響を評価する。
<br><br>

== FROM句にある副問い合わせ ==
<code>FROM</code>句にある副問い合わせの例を示す。<br>
 <syntaxhighlight lang="sql">
 SELECT b.Name, b.Sales, b.Date
 FROM (SELECT AVG(Sales) as Sales_Average FROM T_Sample) a, T_Sample b
 WHERE a.Sales_Average < b.Sales;
 </syntaxhighlight>
<br>
2行目は、<code>FROM</code>句にSalesカラムの平均を求める副問い合わせである。<br>
副問い合わせの結果をテーブルのように使用している。<br>
<br>
結果は、以下の通りである。<br>
 Name     Date     Sales
 A        20210101 200
<br>
また、<code>FROM</code>句にある副問い合わせのSQL文は、<code>WITH</code>句を使用して記述することもできる。<br>
* [[SQL_Server_-_WITH|WITH句の例]]
<br><br>

== WHERE句にある副問い合わせ ==
<code>WHERE</code>句にある副問合せの例を示す。<br>
 <syntaxhighlight lang="sql">
 SELECT * FFROM T_Sample
 WHERE Sales > (SELECT AVG(Sales) FROM T_Sample);
 </syntaxhighlight>
<br>
2行目は、<code>WHERE</code>句に売上の平均を求める副問い合わせである。<br>
2行目の副問い合わせのSQL文を実行した後、1行目の主問い合わせのSQL文が実行される。<br>
<br>
結果は、以下の通りである。<br>
 Name     Data     Sales
 A        20210101 200
<br>
また、<code>WHERE</code>句に<code>EXISTS</code>句を接続することにより、相関副問い合わせを記述できる。<br>
相関副問い合わせは、主問い合わせのSQL文を実行した後、副問い合わせのSQL文を実行する。<br>
* [[SQL_Server_-_EXISTS|EXISTS句の例 (存在判定 / 相関副問い合わせ)]]
* [[SQL Server - 相関副問い合わせ(相関サブクエリ)|相関副問い合わせ(相関サブクエリ)の例]] 
<br><br>

== HAVING句にある副問い合わせ ==
<code>HAVING</code>句にある副問い合わせの例である。<br>
 <syntaxhighlight lang="sql">
 SELECT Name, MAX(Sales) 
 FROM T_Sample
 GROUP BY Name 
 HAVING MAX(Sales) > (SELECT AVG(Sales) FROM T_Sample);
 </syntaxhighlight>
<br>
4行目は、<code>HAVING</code>句に売上の平均を求める副問い合わせである。<br>
<br>
結果は、以下の通りである。<br>
 Name     MAX(Sales)
 A        200
<br>
<code>HAVING</code>句については、以下を参照すること。<br>
* 複数の行をまとめる (集約関数 / GROUP BY / HAVING)
<br><br>

== 副問い合わせの結果が複数件の場合 ==
ここでは、例で使用するテーブルを下表に示す。<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|+ T_Sampleテーブル
|-
! style="background-color:#66CCFF;" | Name 
! style="background-color:#66CCFF;" | Date 
! style="background-color:#66CCFF;" | Sales
|-
| A || 20210101 || 200
|-
| B || 20210102 || 150
|-
| C || 20210103 || 100
|}
</center>
<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|+ T_Companyテーブル
|-
! style="background-color:#66CCFF;" | Company
|-
| A
|-
| D
|}
</center>
<br>
==== IN句 ====
<code>IN</code>句は、副問い合わせの結果が複数件の場合に使用する。<br>
 <syntaxhighlight lang="sql">
 SELECT * FROM T_Sample 
 WHERE Sales IN (SELECT Company FROM T_Company);
 </syntaxhighlight>
<br>
2行目の副問い合わせは、結果が複数件返るため(AとC)、条件はイコール(<code>=</code>)ではなく<code>IN</code>句を使用する。<br>
<br>
結果は、以下の通りである。<br>
 Name     Date     Sales
 A        20210101 200
<br>
==== = ANY句 ====
<code>= ANY</code>句は、副問い合わせの結果が複数件の場合に使用する。<br>
 <syntaxhighlight lang="sql">
 SELECT * FROM T_Sample
 WHERE Name = ANY (SELECT Company FROM T_Company);
 </syntaxhighlight>
<br>
2行目の副問い合わせは、結果が複数件返るため(AとC)、条件はイコール(<code>=</code>)ではなく<code>= ANY</code>句を使用する。<br>
<br>
結果は、以下の通りである。<br>
 Name     Date     Sales
 A        20210101 200
<br>
<u>※注意</u><br>
<u>MySQLでは、上記のSQL文の<code>= ANY</code>句は、<code>IN</code>句や<code>= SOME</code>句でも同様の結果となる。</u><br>
<br>
==== NOT IN句 ====
<code>NOT IN</code>句は、副問い合わせの結果が複数件かつ否定を行う場合に使用する。<br>
 <syntaxhighlight lang="sql">
 SELECT * FROM T_Sample 
 WHERE Name NOT IN (SELECT Company FROM T_Company);
 </syntaxhighlight>
<br>
2行目の副問い合わせは、結果が複数件返るため(AとC)、<code>NOT IN</code>句でAとD以外を抽出する。<br>
<br>
結果は、以下の通りである。<br>
 Name     Date     Sales
 B        20210102 150
 C        20210103 100
<br>
==== <> ALL句 ====
<code><> ALL</code>句は、副問い合わせの結果が複数件かつ否定を行う場合に使用する。<br>
 <syntaxhighlight lang="sql">
 SELECT * FROM T_Sample
 WHERE Name <> ALL (SELECT Company FROM T_Company);
 </syntaxhighlight>
<br>
2行目の副問合せは、結果が複数件返るため(AとC)、<code><> ALL</code>句でAとD以外を抽出する。<br>
<br>
結果は、以下の通りである。<br>
 Name     Date     Sales
 B        20210102 150
 C        20210103 100
<br><br>

== その他の使用例 ==
==== サブクエリと集約関数の組み合わせ ====
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ employees テーブル構造
! style="background-color:#00ffff;" | カラム名 
! style="background-color:#00ffff;" | データ型 
! style="background-color:#00ffff;" | 説明 
! style="background-color:#00ffff;" | 制約
|-
| id || SERIAL || 従業員ID || PRIMARY KEY
|-
| department_name || VARCHAR(50) || 部署名 || NOT NULL
|-
| employee_name || VARCHAR(100) || 従業員名 || NOT NULL
|-
| position || VARCHAR(50) || 役職 || NOT NULL
|-
| salary || INTEGER || 給与 || 
|-
| hire_date || DATE || 入社日 || NOT NULL
|}
</center>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ employees テーブルのサンプルデータ
! style="background-color:#00ffff;" | id 
! style="background-color:#00ffff;" | 部署名 
! style="background-color:#00ffff;" | 従業員名 
! style="background-color:#00ffff;" | 役職 
! style="background-color:#00ffff;" | 給与 
! style="background-color:#00ffff;" | 入社日
|-
| 1 || 営業部 || 山田太郎 || 営業担当 || 350000 || 2022-01-15
|-
| 2 || 営業部 || 鈴木一郎 || 営業担当 || 350000 || 2022-02-01
|-
| 3 || 営業部 || 佐藤花子 || 営業主任 || 400000 || 2021-04-01
|-
| 4 || 営業部 || 田中美咲 || 営業担当 || 350000 || 2022-03-15
|-
| 5 || 営業部 || 高橋健一 || 営業部長 || 600000 || 2020-01-01
|-
| 6 || 総務部 || 伊藤真理 || 総務担当 || 330000 || 2022-06-01
|-
| 7 || 総務部 || 渡辺裕子 || 総務担当 || 330000 || 2022-07-15
|-
| 8 || 総務部 || 加藤正男 || 総務主任 || 380000 || 2021-08-01
|-
| 9 || 総務部 || 中村智子 || 総務担当 || 330000 || 2022-09-01
|-
| 10 || IT部 || 小林洋介 || プログラマー || 400000 || 2022-01-15
|}
</center>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ インデックス定義
! style="background-color:#00ffff;" | インデックス名 
! style="background-color:#00ffff;" | 対象カラム 
! style="background-color:#00ffff;" | 説明
|-
| idx_department_name || department_name || 部署名での検索を高速化
|}
</center>
<br>
以下の例では、特定の部署の人数が10人以上の場合、その部署の従業員を抽出している。<br>
<br>
* IN句を使用した方法
*: 可読性が良い。
*: 全カラムを取得する場合に適している。
* 特定のカラムを選択する方法
*: 必要なカラムのみを指定する。
*: ORDER BYで結果を並び替えが可能
*: <br>
* EXISTS句を使用した方法
*: 大量データの場合、IN句よりもパフォーマンスが良いことがある。
*: 存在確認のみを行うため、メモリ使用量が少なくなる可能性がある。
<br>
 <syntaxhighlight lang="sql">
 -- 基本的な書き方 : 10人以上いる部署の全従業員情報を取得
 SELECT *
 FROM   employees
 WHERE  department_name IN (
    SELECT   department_name
    FROM     employees
    GROUP BY department_name
    HAVING   COUNT(*) >= 10
 );
 
 -- 複数のカラムを指定する場合 : 10人以上いる部署の従業員の基本情報のみを取得
 SELECT 
    t1.department_name,
    t1.employee_name,
    t1.position
 FROM  employees t1
 WHERE t1.department_name IN (
    SELECT   department_name
    FROM     employees
    GROUP BY department_name
    HAVING   COUNT(*) >= 10
 )
 ORDER BY t1.department_name;
 
 -- EXISTS句を使用した別の書き方 : 10人以上いる部署の全従業員情報を取得
 SELECT *
 FROM employees t1
 WHERE EXISTS (
    SELECT   1
    FROM     employees t2
    WHERE    t2.department_name = t1.department_name
    GROUP BY t2.department_name
    HAVING   COUNT(*) >= 10
 );
 
 -- 部署ごとの人数を確認する
 SELECT 
    department_name,
    COUNT(*) as employee_count
 FROM     employees
 GROUP BY department_name
 ORDER BY employee_count DESC;
 </syntaxhighlight>
<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]]