SQLの相関サブクエリ：仕組みと例で学ぶ

リレーショナル・データベースでは、行同士が相互に依存していることが多く、複雑な問いに答えるには、現在処理しているテーブルにクエリが立ち戻る必要が生じることがあります。

このようなテーブルを照会するために、SQLでは相関サブクエリが利用できます。これは、内側のクエリが外側のクエリの値に依存するという特定の関係を定義するものです。標準的なサブクエリが一度だけ実行されて終了するのに対し、相関サブクエリは動的で、メインクエリが評価する各行ごとに繰り返し実行されます。

本チュートリアルでは、SQLにおける相関サブクエリの仕組み、パフォーマンス上の考慮点、そしてJOINやウィンドウ関数と比較してどのような場面で適切かを解説します。SQLが初めての場合は、まず Introduction to SQL コースから始めてください。ある程度の経験がある場合は Intermediate SQL コースをご覧ください。 

相関サブクエリとは？

相関サブクエリは、実行にあたって外側のクエリの値に依存するタイプのサブクエリです。 

固定の結果を一度返して終わるのではなく、外側のクエリで処理される各行ごとに内側のクエリが評価されます。これは、内側のクエリが外側のクエリの列を参照しており、両者の間に直接的な結びつきがあるために起こります。

これに対し、非相関サブクエリは外側のクエリとは独立して実行されます。一度実行して結果セットまたは値を返し、外側のクエリはその結果を各行でサブクエリを再実行することなく利用します。

相関サブクエリの動作

SQLにおける典型的な相関サブクエリのワークフローは次のとおりです。

相関サブクエリの仕組み。画像提供：Gemini。

• 外側のクエリが行を選ぶ: SQLは外側のクエリのテーブルを走査し、最初の行を選択します。 
• 参照: 内側のクエリは、その特定の行から値を取得します。多くの場合、別名を用います。
• 実行: 内側のクエリは、その値を使って実行されます。
• フィルタ／更新: 結果は外側のクエリに返され、その行を含めるかどうかが判断されます。
• 反復: テーブルの終わりまで、この処理が次の行に対して繰り返されます。

SQLにおける相関サブクエリの例

ここまでの説明は概念的なものです。学ぶ最良の方法は、例を通して手を動かすことです。

例1：部署平均を上回る給与の従業員

employees テーブルに従業員の給与と部署IDがあるとします。各部署の平均給与よりも多く稼いでいる従業員を見つけたいとします。

次のクエリを使用します。ここで：

• 外側のクエリは employees テーブルから従業員を選択します。

• サブクエリは同じ部署の平均給与を計算します。

• e2.department_id = e.department_id の条件は、外側のクエリの別名 e を参照します。

 -- Fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)                 -- Calculate the average salary
    FROM employees e2
    WHERE e2.department_id = e.department_id  
    -- Correlation: references the outer query's department_id
);

例2：相関サブクエリとEXISTS()の併用

相関サブクエリに EXISTS() 演算子を組み合わせて、別のテーブルに関連レコードが存在するかどうかを確認することもできます。

たとえば、customers と orders テーブルがあるとします。少なくとも1回注文を行った顧客を一覧表示したい場合、次のクエリを用います。ここで：

• 外側のクエリは customers テーブルの行を走査します。

• サブクエリは、その顧客に対して少なくとも1件の注文が存在するかを確認します。

• o.customer_id = c.customer_id の条件が、サブクエリを外側のクエリに結びつけます。

-- Fetch customers with at least one order
SELECT 
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id  
    -- Correlation: references the outer query customer_id
);

上記のクエリでは、SQLはordersテーブルに一致する行が存在するかどうかを確認します。存在すれば EXISTS() 演算子は true を返し、その顧客は結果に含まれます。

相関サブクエリと非相関サブクエリの比較

先に見たとおり、SQLのサブクエリは非相関サブクエリと相関サブクエリのいずれかに分類されます。内側のクエリが外側のクエリに依存するかどうかが重要な違いです。

非相関サブクエリでは、データベースはそれを一度実行し、その結果を外側のクエリで利用します。

たとえば、次のクエリは全体の平均給与より多く稼いでいる従業員を見つけます。

-- Query employees who earn more than the overall average salary
SELECT 
    employee_id,
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

上記のクエリでは、サブクエリがテーブル全体の平均給与を計算し、これが1回だけ実行されます。外側のクエリは各従業員の給与をその1つの値と比較します。

非相関サブクエリは一度だけ実行されるため、結果を使い回せる場合は通常より高速です。全体平均や合計などのグローバルな比較に最適です。

一方で、相関サブクエリは大規模テーブルでは遅くなることがあります。部署単位の比較や存在確認のように、各行に相対的な条件を評価する必要がある場合に有用です。

さらに詳しく学ぶには、 Introduction to SQL Server コースを受講し、グルーピングと集約、テーブルの結合について理解を深めてください。

相関サブクエリ vs. JOIN

多くの相関サブクエリはJOIN を使って書き換えることができます。リレーショナル・データベースでは、JOINは行ごとではなく集合単位で関係を処理できるため、より高いパフォーマンスを発揮します。

次の相関サブクエリを使ったクエリを考えます。これは各部署の平均給与を上回って支払われている従業員を一覧表示します。

-- Use subquery to fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_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
);

同じ結果を得るために、JOIN 句を用いてクエリを書き換えることができます。

-- Use JOIN to fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    -- Precompute the department average once per department
) dept_avg
ON e.department_id = dept_avg.department_id
-- Match employees with their department averages
WHERE e.salary > dept_avg.avg_salary;
-- Compare salary with the computed department average

以下の表は、SQLにおける相関サブクエリとJOINの違いをまとめたものです。

おすすめとして、Joining Data in SQL コースで、SQLにおけるさまざまなJOINの種類や、データベース内の関連テーブルの扱い方を学んでください。

相関サブクエリ vs. ウィンドウ関数

近年のSQLでは、ウィンドウ関数（AVG() や OVER (PARTITION BY) など）を使えば、1回の走査で行ごとの集約を計算できます。 

例えば次のクエリは、所属部署の平均給与より高い給与の従業員を返します。サブクエリ内で OVER () によって集約をウィンドウ関数に変換し、PARTITION BY department_id でテーブルを部署ごとのグループ（パーティション）に分割しています。

-- Use window function to get employees earning more than dept average salary
SELECT 
    employee_id,
    employee_name,
    salary,
    department_id
FROM (
    SELECT 
        employee_id,
        employee_name,
        salary,
        department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
        -- Window function calculates department average once per partition
    FROM employees
) t
WHERE salary > dept_avg_salary;

ただし、EXISTS() や NOT EXISTS() を使ってテーブル間の関係を検証したい場合には、相関サブクエリは依然として有用です。ウィンドウ関数が使えないデータベースや状況でも相関サブクエリを用いることがあります。

相関サブクエリのパフォーマンス

相関サブクエリは強力ですが、しばしばパフォーマンス面での課題を伴います。 

相関サブクエリは繰り返し実行される

外側のクエリの各行ごとに実行されるため、内側のデータを何度も再走査してしまい、大規模テーブルではクエリが遅くなる場合があります。外側のテーブルが10万行あるなら、データベースは10万回のサブタスクを実行することになります。

ボトルネック

適切に最適化しないと、相関クエリはCPU使用率の上昇や長い待ち時間を招くことがあります。特に、内側のクエリが複雑な計算を行ったり、大きなテーブルを走査している場合に顕著です。

列へのインデックス付与

相関に用いる列にインデックスを付けると、データベースはサブクエリ内の関連行をテーブル全体を毎回走査することなく、ほぼ即座に見つけられるようになります。

クエリプランナの最適化

最新のデータベースは、内部で相関サブクエリを最適化することがよくあります。クエリプランナは、JOINやキャッシュされた集約など、より効率的な形にクエリを変換し、実行時間を大幅に短縮することがあります。

相関サブクエリを使うべき場面

次のような場合に相関サブクエリを使用できます。

• 行固有の集約に基づくフィルタリング: 各行に相対的な値を比較する必要がある場合に使用します（例：部署平均を上回る給与の従業員）。

• EXISTS()で関連データを確認: EXISTS() と組み合わせて、関連行の有無を検証できます。

• 複雑な入れ子のロジックの表現: 複雑な条件を、長い JOIN の連鎖よりも読みやすく表現できることがあります。

ただし、次のような場合は相関サブクエリの使用を避けてください。

• 単純なJOINで済む: LEFT JOIN や INNER JOIN で同じ結果が得られるなら、常にそちらの方が高速です。

• ビッグデータを扱う: 相関条件がインデックスのない大きなテーブルを参照する場合、繰り返しの評価によりクエリが大幅に遅くなることがあります。

相関サブクエリのよくあるミス

相関サブクエリを使用する際によく遭遇する問題と、その対処法は次のとおりです。

• 相関条件の書き忘れ: 相関サブクエリは外側のクエリの列を参照しなければなりません。この条件が欠けると、サブクエリは独立したものになり、誤った結果を生む可能性があります。
• 実行順序の誤解: 常に外側のクエリが先に走り、その後に内側のクエリが続きます。この論理を取り違えると、誤った結果につながります。
• 不要な入れ子: 外側の行に依存しない値を相関サブクエリで包んでしまうことがあります。内側のクエリが外側の行を必要としないなら、相関を外してパフォーマンスを改善してください。
• パフォーマンス影響の無視: 小規模データでは問題なく動いても、テーブルが大きくなると遅くなることがあります。これを避けるため、常に現実的な規模のデータでテストし、必要に応じてインデックス付与やクエリの書き換えを検討してください。

まとめ

相関サブクエリを使う場面と使い方、そして他の手法に置き換えるべき場面を見極めることは、明快で効率的なSQLを書くうえで重要なスキルです。

次のステップとして、SQL Associate Certification の取得をおすすめします。SQLを用いたデータ分析のスキルを証明し、他のデータプロフェッショナルの中で差別化できます。最後に、Database Design コースでは、データベースの作成・管理方法や、要件に合ったDBMSの選定について学べます。