SQLサブクエリ: スカラー、相関、派生テーブル

SQLサブクエリ: スカラー、相関、派生テーブル

サブクエリは別のクエリ内にネストされたSQLクエリです。サブクエリはSELECT、FROM、WHERE、HAVING句に記述でき、1つのステートメントで複雑なデータ取得を可能にします。

スカラーサブクエリ

単一の値を返し、単一の値が求められるあらゆる場所で使用できます:

SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

WHERE句でのサブクエリ

INサブクエリ:

SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

EXISTSサブクエリ:

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS は一致が見つかった時点で短絡評価するため、大規模データセットでは IN より効率的なことが多いです。

相関サブクエリ

外部クエリのカラムを参照し、外部行ごとに1回実行されます:

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

これにより、部門平均を超える給与の従業員が検索されます。

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

SELECT dept_stats.department, dept_stats.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE dept_stats.avg_salary > 75000;

サブクエリとJOINの比較

多くのサブクエリはJOINに書き換えることができ、その逆も可能です。一般に、JOINは複数テーブルからのデータ結合により適しており、サブクエリは集約条件に基づくフィルタリングでより明確です。

ベストプラクティス

• 複雑なサブクエリや繰り返し使用するサブクエリには、可読性向上のためCTE（WITH句）を使用する
• 大規模テーブルでの存在チェックには IN より EXISTS を優先する
• 適切なインデックスなしの大規模テーブルでの相関サブクエリは避ける
• EXPLAIN を使用してサブクエリのパフォーマンスをテストし、潜在的なボトルネックを特定する

サブクエリは、複雑なロジックを管理可能なネストされたコンポーネントに分割するための強力なツールです。