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

概要

副問い合わせ(サブクエリ)とは、SELECT文の結果を別のSQL文で使用することである。
副問い合わせは、INSERT文、UPDATE文、DELETE文でも使用できる。

以下に、SELECT文の使用時の副問合せが記述できる箇所を示す。

• SELECT句に記述する副問合せ
• FROM句に記述する副問合せ
• WHERE句に記述する副問合せ
• HAVING句に記述する副問合せ

ここでは、例で使用するテーブルを下表に示す。

T_Sampleテーブル

Name	Date	Sales
A	20210101	200
B	20210102	150
C	20210103	100

SELECT句にある副問合せ

副問合せで記述すべきクエリとは、以下に示すようなものである。

• インデックスを活用できるシンプルなサブクエリ
• 明確な目的を持った相関サブクエリ
• 集計関数を使用した効率的な計算

SELECT句にある副問い合わせの例を示す。

 SELECT Name, Date, Sales,
        Sales > (SELECT AVG(Sales) FROM T_Sample ) as AboveAverage  -- 売上の平均を求める副問い合わせ
                                                                    -- AboveAverageカラムには、売上の平均を超えている場合は1、それ以外の場合は0が代入される
 FROM T_Sample;

# 結果
Name     Date     Sales     AboveAverage
A        20210101 200       1
A        20210102 150       0
B        20210103 100       0

スカラーサブクエリ (単一の値を返す)

 -- 最小売上との差分を計算
 SELECT Name, Sales, 
        Sales - (SELECT MIN(Sales) FROM T_Sample) as DiffFromMin
 FROM T_Sample;

相関サブクエリ (外部クエリの値を参照)

• サブクエリで外部クエリのテーブル (t1) を参照している。

  t2.Date < t1.Date

• サブクエリが外部クエリの各行に対して実行される。
• スカラサブクエリとして使用している。

  単一の値を返す。

1. 外部クエリがT_Sampleテーブルの各レコード (t1) を処理する。
2. 各レコードに対して、その行の日付より前の全ての売上の平均を計算する。
3. 結果として、各レコードの名前、日付、売上、その日付以前の平均売上が表示される。

これは時系列分析でよく使用されるパターンであり、各時点での過去の平均値を計算するものである。

 -- 各レコードより前の日付の平均売上を計算
 SELECT Name, Date, Sales,
        (SELECT AVG(Sales) 
         FROM T_Sample t2 
         WHERE t2.Date < t1.Date) as PriorAvgSales
 FROM T_Sample t1;

• サブクエリで外部クエリのテーブル (t1) を参照している。

  t2.Sales = t1.Sales

• サブクエリが外部クエリの各行に対して実行される。
• EXIST句を使用している。

 -- 売上が存在するレコードを取得
 SELECT * FROM T_Sample t1
 WHERE EXISTS (
    SELECT 1 FROM T_Sample t2
    WHERE t2.Sales = t1.Sales
 );

IN句 / EXISTS句 / ANY句 / ALL句

 -- 平均以上の売上のレコードを取得
 SELECT * FROM T_Sample 
 WHERE Sales > ANY (
    SELECT Sales FROM T_Sample 
    WHERE Sales > 150
 );

FROM句のサブクエリ

 -- 日付ごとの累計売上を計算
 SELECT t1.Date, t1.Sales,
        (SELECT SUM(t2.Sales) 
         FROM T_Sample t2 
         WHERE t2.Date <= t1.Date) as CumulativeSales
 FROM T_Sample t1;

アンチパターン

• 過度に複雑なネストされたサブクエリ

 -- 避けるべき例
 SELECT * FROM T_Sample 
 WHERE Sales > (
    SELECT AVG(Sales) FROM (
       SELECT Sales FROM T_Sample 
       WHERE Sales > (
          SELECT MIN(Sales) FROM T_Sample
       )
    )
 );

• JOINで代替可能な非効率なサブクエリ

 -- 非効率な例
 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;

• 不必要なサブクエリの使用

 -- 非効率な例
 SELECT Name, 
        (SELECT MAX(Sales) FROM T_Sample) as MaxSales
 FROM T_Sample;
 
 -- 推奨される記述方法
 SELECT Name, MAX(Sales) OVER() as MaxSales
 FROM T_Sample;

※注意
パフォーマンスの観点から、以下に示すことに注意が必要である。

• サブクエリは適切なインデックスが存在するか確認する。
• 可能な限りJOINやウィンドウ関数で代替することを検討する。
• 実行計画を確認し、パフォーマンスへの影響を評価する。

FROM句にある副問い合わせ

FROM句にある副問い合わせの例を示す。

 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;

2行目は、FROM句にSalesカラムの平均を求める副問い合わせである。
副問い合わせの結果をテーブルのように使用している。

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

Name     Date     Sales
A        20210101 200

また、FROM句にある副問い合わせのSQL文は、WITH句を使用して記述することもできる。

• WITH句の例

WHERE句にある副問い合わせ

WHERE句にある副問合せの例を示す。

 SELECT * FFROM T_Sample
 WHERE Sales > (SELECT AVG(Sales) FROM T_Sample);

2行目は、WHERE句に売上の平均を求める副問い合わせである。
2行目の副問い合わせのSQL文を実行した後、1行目の主問い合わせのSQL文が実行される。

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

Name     Data     Sales
A        20210101 200

また、WHERE句にEXISTS句を接続することにより、相関副問い合わせを記述できる。
相関副問い合わせは、主問い合わせのSQL文を実行した後、副問い合わせのSQL文を実行する。

• EXISTS句の例 (存在判定 / 相関副問い合わせ)
• 相関副問い合わせ(相関サブクエリ)の例

HAVING句にある副問い合わせ

HAVING句にある副問い合わせの例である。

 SELECT Name, MAX(Sales) 
 FROM T_Sample
 GROUP BY Name 
 HAVING MAX(Sales) > (SELECT AVG(Sales) FROM T_Sample);

4行目は、HAVING句に売上の平均を求める副問い合わせである。

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

Name     MAX(Sales)
A        200

HAVING句については、以下を参照すること。

• 複数の行をまとめる (集約関数 / GROUP BY / HAVING)

副問い合わせの結果が複数件の場合

ここでは、例で使用するテーブルを下表に示す。

T_Sampleテーブル

Name	Date	Sales
A	20210101	200
B	20210102	150
C	20210103	100

T_Companyテーブル

Company
A
D

IN句

IN句は、副問い合わせの結果が複数件の場合に使用する。

 SELECT * FROM T_Sample 
 WHERE Sales IN (SELECT Company FROM T_Company);

2行目の副問い合わせは、結果が複数件返るため(AとC)、条件はイコール(=)ではなくIN句を使用する。

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

Name     Date     Sales
A        20210101 200

= ANY句

= ANY句は、副問い合わせの結果が複数件の場合に使用する。

 SELECT * FROM T_Sample
 WHERE Name = ANY (SELECT Company FROM T_Company);

2行目の副問い合わせは、結果が複数件返るため(AとC)、条件はイコール(=)ではなく= ANY句を使用する。

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

Name     Date     Sales
A        20210101 200

※注意
MySQLでは、上記のSQL文の= ANY句は、IN句や= SOME句でも同様の結果となる。

NOT IN句

NOT IN句は、副問い合わせの結果が複数件かつ否定を行う場合に使用する。

 SELECT * FROM T_Sample 
 WHERE Name NOT IN (SELECT Company FROM T_Company);

2行目の副問い合わせは、結果が複数件返るため(AとC)、NOT IN句でAとD以外を抽出する。

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

Name     Date     Sales
B        20210102 150
C        20210103 100

<> ALL句

<> ALL句は、副問い合わせの結果が複数件かつ否定を行う場合に使用する。

 SELECT * FROM T_Sample
 WHERE Name <> ALL (SELECT Company FROM T_Company);

2行目の副問合せは、結果が複数件返るため(AとC)、<> ALL句でAとD以外を抽出する。

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

Name     Date     Sales
B        20210102 150
C        20210103 100

その他の使用例

サブクエリと集約関数の組み合わせ

employees テーブル構造

カラム名	データ型	説明	制約
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

employees テーブルのサンプルデータ

id	部署名	従業員名	役職	給与	入社日
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

インデックス定義

インデックス名	対象カラム	説明
idx_department_name	department_name	部署名での検索を高速化

以下の例では、特定の部署の人数が10人以上の場合、その部署の従業員を抽出している。

• IN句を使用した方法

  可読性が良い。

  全カラムを取得する場合に適している。

• 特定のカラムを選択する方法

  必要なカラムのみを指定する。

  ORDER BYで結果を並び替えが可能

  
  

• EXISTS句を使用した方法

  大量データの場合、IN句よりもパフォーマンスが良いことがある。

  存在確認のみを行うため、メモリ使用量が少なくなる可能性がある。

 -- 基本的な書き方 : 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;