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;

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

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

IN句 / EXISTS句 / ANY句 / ALL句

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

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

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