【SQL実践ドリル 第4回】サブクエリ ― クエリの中にクエリを書く

はじめに

【SQL実践ドリル】 シリーズ第4回では、サブクエリ（副問い合わせ） を扱います。サブクエリとは、SQL文の中に埋め込まれた別のSELECT文のことです。「平均より高い給与の社員を抽出したい」「特定の条件を満たす部署だけ取得したい」といった、単純なSELECTでは書けないクエリを実現できます。

このシリーズでは共通のサンプルデータベースを使用します。初回（第1回）でCREATE TABLE文を掲載しています。

サンプルデータの概要

テーブル	件数	主な列
departments	5件	department_id, department_name, location
employees	10件	employee_id, name, department_id, hire_date, salary, manager_id
customers	5件	customer_id, customer_name, email, prefecture, registered_date
products	8件	product_id, product_name, category, price, stock
orders	10件	order_id, customer_id, employee_id, order_date
order_details	20件	detail_id, order_id, product_id, quantity

---

サブクエリの分類

サブクエリは使い方によっていくつかの種類に分類されます。

1. スカラーサブクエリ

1行1列の値を返すサブクエリです。SELECT句・WHERE句・HAVING句など、単一の値が必要な場所に書けます。

-- 全社員の平均給与を取得するスカラーサブクエリ
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

2. テーブルサブクエリ（FROM句サブクエリ）

FROM句に書いて仮想テーブル（導出テーブル） として使います。MySQLではエイリアスが必須です。

SELECT sub.department_id, sub.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    WHERE department_id IS NOT NULL
    GROUP BY department_id
) AS sub
WHERE sub.avg_salary > 330000;

3. 相関サブクエリ

外側のクエリの値を参照するサブクエリです。外側の行ごとにサブクエリが評価されます。

-- 自部署の平均より高い給与の社員
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

4. EXISTS / NOT EXISTS

サブクエリの結果が存在するかどうかで絞り込みます。相関サブクエリと組み合わせて使うのが一般的です。

-- 注文実績がある顧客
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

5. IN サブクエリ

サブクエリが返す値のリストに含まれるかどうかで絞り込みます。

-- 東京にある部署の社員
SELECT name FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location = '東京'
);

MySQL と PostgreSQL の違い

• 基本的なサブクエリの構文は両DBで共通です。
• MySQL 8.0以降ではラテラル導出テーブル（LATERAL）がサポートされています。PostgreSQL では以前から利用可能です。
• MySQLのFROM句サブクエリには必ずエイリアスが必要です。PostgreSQLでも付けるのが推奨されます。

---

問題

問1 ⭐（基本）：全社員の平均給与を求める

全社員の平均給与を求めてください。結果の列名は avg_salary としてください。

期待される結果：

avg_salary
338000.0000

模範解答

SELECT AVG(salary) AS avg_salary
FROM employees;

解説：

全10名の給与合計は 350000 + 420000 + 300000 + 380000 + 450000 + 280000 + 330000 + 310000 + 270000 + 290000 = 3380000 です。3380000 / 10 = 338000 となります。この値を次の問題でサブクエリとして利用します。

---

問2 ⭐（基本）：平均給与より高い給与の社員を取得する

スカラーサブクエリを使い、全社員の平均給与（338000）より高い給与の社員の employee_id、name、salary を取得してください。給与の降順で並べてください。

期待される結果：

employee_id	name	salary
5	伊藤健太	450000
2	佐藤花子	420000
4	高橋美咲	380000
1	田中太郎	350000

模範解答

SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

解説：

サブクエリ (SELECT AVG(salary) FROM employees) は 338000 を返します。350000、420000、380000、450000 の4人がこの値を超えています。

---

問3 ⭐（基本）：INサブクエリ ― 東京にある部署の社員

INサブクエリを使い、所在地が「東京」の部署に所属する社員の employee_id、name、department_id を取得してください。employee_id の昇順で並べてください。

期待される結果：

employee_id	name	department_id
1	田中太郎	1
3	鈴木一郎	1
4	高橋美咲	3
8	中村真理	1
9	小林誠	3

模範解答

SELECT employee_id, name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = '東京'
)
ORDER BY employee_id;

解説：

東京にある部署は営業部（department_id = 1）と人事部（department_id = 3）です。これらの部署に所属する社員は5名です。加藤優子（employee_id = 10）は department_id が NULL のため、IN の結果に含まれません（NULLはINで一致しません）。

---

問4 ⭐⭐（応用）：各社員の給与と所属部署の平均給与を比較する

相関サブクエリを使い、全社員について name、salary、department_name、所属部署の平均給与（dept_avg_salary）を取得してください。employee_id の昇順で並べてください。department_id が NULL の社員の department_name と dept_avg_salary は NULL としてください。

期待される結果：

name	salary	department_name	dept_avg_salary
田中太郎	350000	営業部	320000.0000
佐藤花子	420000	開発部	383333.3333
鈴木一郎	300000	営業部	320000.0000
高橋美咲	380000	人事部	325000.0000
伊藤健太	450000	開発部	383333.3333
渡辺さくら	280000	開発部	383333.3333
山本大輔	330000	経理部	330000.0000
中村真理	310000	営業部	320000.0000
小林誠	270000	人事部	325000.0000
加藤優子	290000	NULL	NULL

模範解答

SELECT
    e.name,
    e.salary,
    d.department_name,
    (SELECT AVG(e2.salary)
     FROM employees e2
     WHERE e2.department_id = e.department_id) AS dept_avg_salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;

解説：

相関サブクエリは外側の e.department_id を参照し、同じ部署の平均給与を計算します。各部署の平均給与は以下のとおりです。

• 営業部（dept 1）: (350000 + 300000 + 310000) / 3 = 320000
• 開発部（dept 2）: (420000 + 450000 + 280000) / 3 ≒ 383333.3333
• 人事部（dept 3）: (380000 + 270000) / 2 = 325000
• 経理部（dept 4）: 330000 / 1 = 330000

加藤優子は department_id が NULL なので、サブクエリの WHERE e2.department_id = NULL は常に偽となり、AVG は NULL を返します。

---

問5 ⭐⭐（応用）：各部署で最も給与が高い社員を取得する

相関サブクエリを使い、各部署で給与が最も高い社員の name、salary、department_name を取得してください。department_id の昇順で並べてください（department_id が NULL の社員は除外します）。

期待される結果：

name	salary	department_name
田中太郎	350000	営業部
伊藤健太	450000	開発部
高橋美咲	380000	人事部
山本大輔	330000	経理部

模範解答

SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

解説：

相関サブクエリで各部署の最大給与を取得し、それに一致する社員を抽出します。JOIN を使用しているため department_id が NULL の加藤優子は除外されます。各部署の最大給与は、営業部: 350000（田中太郎）、開発部: 450000（伊藤健太）、人事部: 380000（高橋美咲）、経理部: 330000（山本大輔）です。

---

問6 ⭐⭐（応用）：平均注文金額を超える注文を取得する

FROM句サブクエリを使い、注文ごとの合計金額が全注文の平均合計金額を超える注文の order_id、order_date、total_amount を取得してください。total_amount の降順で並べてください。

期待される結果：

order_id	order_date	total_amount
1	2024-07-01	413500
10	2024-11-01	295000
8	2024-10-01	288000
4	2024-08-01	224000
7	2024-09-10	202000

模範解答

SELECT o.order_id, o.order_date, order_totals.total_amount
FROM orders o
JOIN (
    SELECT od.order_id, SUM(p.price * od.quantity) AS total_amount
    FROM order_details od
    JOIN products p ON od.product_id = p.product_id
    GROUP BY od.order_id
) AS order_totals ON o.order_id = order_totals.order_id
WHERE order_totals.total_amount > (
    SELECT AVG(sub.total_amount)
    FROM (
        SELECT od.order_id, SUM(p.price * od.quantity) AS total_amount
        FROM order_details od
        JOIN products p ON od.product_id = p.product_id
        GROUP BY od.order_id
    ) AS sub
)
ORDER BY order_totals.total_amount DESC;

解説：

各注文の合計金額は以下のとおりです。

order_id	内訳	total_amount
1	198000×2 + 3500×5	413500
2	45000×1 + 12000×3	81000
3	89000×1 + 4800×2	98600
4	198000×1 + 6500×4	224000
5	3500×10 + 12000×2	59000
6	65000×1 + 3500×3	75500
7	89000×2 + 4800×5	202000
8	198000×1 + 45000×2	288000
9	12000×1 + 6500×3	31500
10	89000×3 + 3500×8	295000

合計: 1768100、平均: 176810。この平均を超える注文は5件です。

MySQLのFROM句サブクエリにはエイリアス（AS order_totals、AS sub）が必須です。

---

問7 ⭐⭐（応用）：EXISTS ― パソコンを注文したことがある顧客

EXISTSを使い、カテゴリが「パソコン」の商品を1回でも注文したことがある顧客の customer_id と customer_name を取得してください。customer_id の昇順で並べてください。

期待される結果：

customer_id	customer_name
1	株式会社ABC
3	GHI商事
4	JKLテクノロジー
5	MNOサービス

模範解答

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    WHERE o.customer_id = c.customer_id
      AND p.category = 'パソコン'
)
ORDER BY c.customer_id;

解説：

パソコンカテゴリの商品は「ノートPC Pro」（product_id = 1）と「ノートPC Light」（product_id = 6）です。

• 株式会社ABC（cust 1）: 注文1でproduct 1、注文3でproduct 6、注文8でproduct 1 → 該当
• GHI商事（cust 3）: 注文4でproduct 1 → 該当
• JKLテクノロジー（cust 4）: 注文10でproduct 6 → 該当
• MNOサービス（cust 5）: 注文7でproduct 6 → 該当
• DEFコーポレーション（cust 2）: 注文2でproduct 4,5、注文6でproduct 8,2 → パソコンなし

EXISTS は条件を満たす行が1行でも見つかればTRUEを返すため、IN より効率的な場合があります。

---

問8 ⭐⭐（応用）：NOT EXISTS ― モニターを注文したことがない顧客

NOT EXISTSを使い、カテゴリが「モニター」の商品を一度も注文したことがない顧客の customer_id と customer_name を取得してください。customer_id の昇順で並べてください。

期待される結果：

customer_id	customer_name
3	GHI商事
4	JKLテクノロジー
5	MNOサービス

模範解答

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    WHERE o.customer_id = c.customer_id
      AND p.category = 'モニター'
)
ORDER BY c.customer_id;

解説：

モニターカテゴリの商品は「4Kモニター 27インチ」（product_id = 4）と「ゲーミングモニター」（product_id = 8）です。

• 株式会社ABC（cust 1）: 注文8でproduct 4（4Kモニター）→ モニター注文あり
• DEFコーポレーション（cust 2）: 注文2でproduct 4、注文6でproduct 8 → モニター注文あり
• GHI商事（cust 3）: 注文4でproduct 1,7、注文9でproduct 5,7 → モニターなし
• JKLテクノロジー（cust 4）: 注文5でproduct 2,5、注文10でproduct 6,2 → モニターなし
• MNOサービス（cust 5）: 注文7でproduct 6,3 → モニターなし

---

問9 ⭐⭐⭐（チャレンジ）：2人以上の部下を持つマネージャー

相関サブクエリを使い、部下（manager_id が自分の employee_id である社員）を2人以上持つマネージャーの employee_id、name、subordinate_count（部下数）を取得してください。subordinate_count の降順で並べてください。

期待される結果：

employee_id	name	subordinate_count
1	田中太郎	3
2	佐藤花子	2
4	高橋美咲	2

模範解答

SELECT
    e.employee_id,
    e.name,
    (SELECT COUNT(*)
     FROM employees e2
     WHERE e2.manager_id = e.employee_id) AS subordinate_count
FROM employees e
WHERE (
    SELECT COUNT(*)
    FROM employees e2
    WHERE e2.manager_id = e.employee_id
) >= 2
ORDER BY subordinate_count DESC;

解説：

各社員の部下数は以下のとおりです。

• 田中太郎（emp 1）: manager_id = 1 の社員 → 佐藤花子、鈴木一郎、中村真理 = 3人
• 佐藤花子（emp 2）: manager_id = 2 の社員 → 伊藤健太、渡辺さくら = 2人
• 高橋美咲（emp 4）: manager_id = 4 の社員 → 山本大輔、小林誠 = 2人

HAVING句を使う代替解法もあります。

-- 代替解法：JOINとGROUP BYを使う方法
SELECT e.employee_id, e.name, COUNT(e2.employee_id) AS subordinate_count
FROM employees e
JOIN employees e2 ON e2.manager_id = e.employee_id
GROUP BY e.employee_id, e.name
HAVING COUNT(e2.employee_id) >= 2
ORDER BY subordinate_count DESC;

---

問10 ⭐⭐⭐（チャレンジ）：顧客別の注文回数と合計金額

相関サブクエリを使い、全顧客の customer_name、order_count（注文回数）、total_amount（合計金額）を取得してください。注文がない顧客は order_count = 0、total_amount = 0 としてください。total_amount の降順で並べてください。

期待される結果：

customer_name	order_count	total_amount
株式会社ABC	3	800100
JKLテクノロジー	2	354000
GHI商事	2	255500
MNOサービス	1	202000
DEFコーポレーション	2	156500

模範解答

SELECT
    c.customer_name,
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.customer_id = c.customer_id) AS order_count,
    (SELECT COALESCE(SUM(p.price * od.quantity), 0)
     FROM orders o
     JOIN order_details od ON o.order_id = od.order_id
     JOIN products p ON od.product_id = p.product_id
     WHERE o.customer_id = c.customer_id) AS total_amount
FROM customers c
ORDER BY total_amount DESC;

解説：

各顧客の注文内容と合計金額は以下のとおりです。

• 株式会社ABC（cust 1）: 注文1（413500）+ 注文3（98600）+ 注文8（288000）= 800100
• JKLテクノロジー（cust 4）: 注文5（59000）+ 注文10（295000）= 354000
• GHI商事（cust 3）: 注文4（224000）+ 注文9（31500）= 255500
• MNOサービス（cust 5）: 注文7（202000）= 202000
• DEFコーポレーション（cust 2）: 注文2（81000）+ 注文6（75500）= 156500

COALESCE を使うことで、注文がない場合に NULL ではなく 0 を返します（今回のデータでは全顧客に注文があります）。

代替解法（FROM句サブクエリを使う方法）：

SELECT
    c.customer_name,
    COALESCE(sub.order_count, 0) AS order_count,
    COALESCE(sub.total_amount, 0) AS total_amount
FROM customers c
LEFT JOIN (
    SELECT
        o.customer_id,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(p.price * od.quantity) AS total_amount
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY o.customer_id
) AS sub ON c.customer_id = sub.customer_id
ORDER BY total_amount DESC;

---

まとめ

サブクエリの種類	特徴	主な用途
スカラーサブクエリ	1行1列を返す	SELECT句・WHERE句での値比較
テーブルサブクエリ	表形式の結果を返す	FROM句で仮想テーブルとして利用
相関サブクエリ	外側のクエリの値を参照	行ごとの条件評価・グループ内比較
EXISTS / NOT EXISTS	存在チェック	関連データの有無で絞り込み
IN サブクエリ	値リストに含まれるか	別テーブルの条件による絞り込み

サブクエリは強力ですが、相関サブクエリは行数が多いとパフォーマンスに影響します。JOINやウィンドウ関数で書き換えられる場合は、実行計画（EXPLAIN）を確認して最適な方法を選びましょう。

---

参考

• MySQL 8.0 リファレンスマニュアル - サブクエリ
• MySQL 8.0 リファレンスマニュアル - EXISTS と NOT EXISTS によるサブクエリ
• MySQL 8.0 リファレンスマニュアル - 導出テーブル
• PostgreSQL 16 ドキュメント - サブクエリ

---

@kotaro_ai_lab
AI活用や開発効率化について発信しています。フォローお気軽にどうぞ！