SQL Server - 副問い合わせ(サブクエリ)
概要
副問い合わせ(サブクエリ)とは、SELECT文の結果を別のSQL文で使用することである。
副問い合わせは、INSERT文、UPDATE文、DELETE文でも使用できる。
以下に、SELECT文の使用時の副問合せが記述できる箇所を示す。
- SELECT句に記述する副問合せ
- FROM句に記述する副問合せ
- WHERE句に記述する副問合せ
- HAVING句に記述する副問合せ
ここでは、例で使用するテーブルを下表に示す。
| 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
- サブクエリが外部クエリの各行に対して実行される。
- スカラサブクエリとして使用している。
- 単一の値を返す。
- 外部クエリがT_Sampleテーブルの各レコード (t1) を処理する。
- 各レコードに対して、その行の日付より前の全ての売上の平均を計算する。
- 結果として、各レコードの名前、日付、売上、その日付以前の平均売上が表示される。
これは時系列分析でよく使用されるパターンであり、各時点での過去の平均値を計算するものである。
-- 各レコードより前の日付の平均売上を計算
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句を使用して記述することもできる。
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文を実行する。
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)
副問い合わせの結果が複数件の場合
ここでは、例で使用するテーブルを下表に示す。
| Name | Date | Sales |
|---|---|---|
| A | 20210101 | 200 |
| B | 20210102 | 150 |
| C | 20210103 | 100 |
| 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
その他の使用例
サブクエリと集約関数の組み合わせ
| カラム名 | データ型 | 説明 | 制約 |
|---|---|---|---|
| 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 |
| 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;