SQLサブクエリパターン — スカラー、テーブル、相関

SQLのサブクエリパターン

サブクエリは別のクエリ内にネストされたSELECT文です。3つの主要なタイプを理解することで、各状況に適切なパターンを選択できます。

1. スカラーサブクエリ

単一の値を返します。単一の値が期待される場所で使用できます。

-- 会社の平均より高い給与の従業員
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2. テーブルサブクエリ（派生テーブル）

FROM句で仮想テーブルとして使用される結果セットを返します。

SELECT dept_name, avg_salary
FROM (
  SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 70000;

3. 相関サブクエリ

外部クエリのカラムを参照します。外部クエリの各行に対して1回実行されます。

-- 所属部署の平均より高い給与の従業員
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

EXISTSとNOT EXISTS

-- 少なくとも1人の従業員がいる部署
SELECT d.name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.id
);

-- 従業員がいない部署
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.id
);

INとEXISTSの比較

• INはサブクエリの結果を実体化し、メンバーシップをチェックします。サブクエリが小さなセットを返す場合に適しています。
• EXISTSは一致が見つかるとすぐにショートサーキットします。相関チェックを持つ大きな外部テーブルには一般的に高速です。

代わりにCTEを使用すべき場合

サブクエリが複雑であったり、複数回参照される場合は、共通テーブル式（WITH）の方が通常読みやすく保守しやすいです。詳細はCTEのトピックを参照してください。