MySQLのサブクエリとEXISTSの詳細な解説
サブクエリは、SQLの機能の一つで、ネスト（入れ子）を可能にするものです。つまり、あるクエリの結果を、別のクエリの条件として使用することができます。
このレッスンでは、サブクエリのさまざまな形式とその最適化について、体系的に解説します。
graph TB
A[Subquery Categories] --> B[Tag-Based Query<br/>Return a single value]
A --> C[Liezi Query<br/>Return a column]
A --> D[Row-based queries<br/>Return a line]
A --> E[Table Child Query<br/>Back to Table]
C --> C1[IN / NOT IN]
C --> C2[ANY / ALL]
E --> E1[FROM Derivation Table]
A --> F[EXISTS<br/>Existence Check]
A --> G[Correlated Subqueries<br/>Citation Format]
1. 学習内容
- インデックス付きクエリ（単一の値を返す）
- 列ベースのクエリ（1つの列を返す）
- 行ベースのクエリ（1行を返す）
- テーブルサブクエリ（テーブルを返す）
- 存在／非存在
2. 実際の事例
(1) 課題：1ステップでは達成できない
「平均給与よりも給与が高い従業員」を見つけるには、まず平均を算出し、それから比較を行う必要があります。
(2) サブクエリの解決策
SQL
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
3. 標準量子クエリ
1行1列のデータを返します。
▶ 例：タグ付きクエリ
SQL
-- Employees with above-average salaries
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- The highest-paid employee
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
-- Customers with the most recent orders
SELECT * FROM customers
WHERE id = (SELECT customer_id FROM orders ORDER BY order_date DESC LIMIT 1);
4. 『列子』に関する問答
複数の行を含む単一の列を返します。IN、NOT IN、ANY、およびALL と組み合わせて使用します。
▶ 例：IN サブクエリ
SQL
-- Customers with orders
SELECT * FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);
-- Customers with no orders
SELECT * FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL);
▶ 例：ANY/ALL サブクエリ
SQL
-- Salary higher than Sales Any employee in the department（Higher than the minimum）
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales');
-- Salary higher than Sales All employees in the department（Higher than the highest）
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');
5. EXISTS サブクエリ
サブクエリが結果を返すかどうかを確認してください。
▶ 例：EXISTS
SQL
-- Customers with orders
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Customers with no orders
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
(1) EXISTS と IN の比較
| 次元 | EXISTS | IN |
|---|---|---|
| 実行方法 | テーブルに対してサブクエリを1行ずつ照合する | まずサブクエリを実行し、その後照合を行う |
| ユースケース | 外側のテーブルが小さく、サブクエリが大きい場合 | 外側のテーブルが大きく、サブクエリが小さい場合 |
| NULL 値の取り扱い | セキュリティ | 「NOT IN」の NULL に関する落とし穴 |
| パフォーマンス | 概して優れている | サブクエリの結果セットが小さい場合に優れている |
6. サブテーブルのクエリ（派生テーブル）
サブクエリを一時テーブルとして扱う。
▶ 例：FROM サブクエリ
SQL
-- The highest-paid person in each department
SELECT e.* FROM employees e
INNER JOIN (
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
) dept_max ON e.department = dept_max.department AND e.salary = dept_max.max_salary;
-- Customer Order Statistics
SELECT c.name, order_stats.total_orders, order_stats.total_amount
FROM customers c
INNER JOIN (
SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
) order_stats ON c.id = order_stats.customer_id;
7. 相関サブクエリ
サブクエリは、外側のテーブルのフィールドを参照しています。
▶ 例：相関サブクエリ
SQL
-- The highest-paid employee in each department
SELECT * FROM employees e1
WHERE salary = (
SELECT MAX(salary) FROM employees e2
WHERE e2.department = e1.department
);
-- Employees whose salaries are higher than the department average
SELECT * FROM employees e1
WHERE salary > (
SELECT AVG(salary) FROM employees e2
WHERE e2.department = e1.department
);
8. サブクエリの最適化
| 戦略 | 説明 |
|---|---|
| サブクエリの代わりにJOINを使用する | MySQLのオプティマイザは通常これを処理できますが、JOINの方が直感的です |
| IN の代わりに EXISTS を使用する | 大規模なデータセットを扱う場合、通常は EXISTS の方が高速です |
| 相関サブクエリを避ける | 可能な限り JOIN に書き換える |
| サブクエリのインデックス作成 | サブクエリ内の結合/フィルタフィールドのインデックス作成 |
❓ よくある質問
Q サブクエリとJOIN、どちらが高速ですか？
A 一般的に、JOINの方が高速です（オプティマイザによる最適化がより効果的に行えるため）。ただし、データの分布によって異なりますので、EXPLAINを使用して比較してください。
Q サブクエリは最大何階層までネストできますか？
A MySQLではネストの深さに制限があります（通常は255階層）が、一般的には3階層を超えないようにすることが推奨されています。
Q NOT IN には NULL トラップはありますか？
A はい。
NOT IN (1, 2, NULL) は常に NULL を返します。代わりに NOT EXISTS を使用してください。Q サブクエリとJOIN、どちらが高速ですか？
A データ量やインデックスの状況によりますが、一般的にはJOINの方が効率的です。EXPLAINを使用して実際の実行プランを比較することをお勧めします。
Q サブクエリは最大何階層までネストできますか？
A 理論上は制限ありませんが、3階層を超えると可読性が著しく低下するため、代わりにCTE（WITH句）を使用することをお勧めします。
📖 まとめ
- 標準数量の照会 単一の値を返します。
= / > / <と比較してください。 - 『列子』のクエリは複数の行を返すため、
IN / NOT IN / ANY / ALLを使用してください - EXISTS は、サブクエリが結果を返すかどうかを確認します。一般的に、IN よりも効率的です。
- サブクエリ：
FROM句において、派生テーブルとして使用される - 相関サブクエリは、外部テーブルのフィールドを参照し、各行ごとに1回ずつ実行されます
- 最適化の原則：サブクエリの代わりにJOINを使用する、NOT INの代わりにEXISTSを使用する
📝 練習問題
-
基本問題（難易度 ⭐）：サブクエリを使用して、平均給与よりも給与が高い従業員を特定してください。
-
上級問題（難易度：⭐⭐）：
EXISTSを使用して、これまで一度も注文をしたことがない顧客を特定してください。 -
チャレンジ問題（難易度：⭐⭐⭐）：相関サブクエリを使用して、各部門で最も給与の高い社員を特定してください。
