SQL相関サブクエリ

概要：このチュートリアルでは、外部クエリの値を使用するサブクエリであるSQL相関サブクエリについて学習します。

SQL相関サブクエリ入門

例から始めましょう。

サンプルデータベースの次のemployeesテーブルを見てください。

次のクエリは、給与が全従業員の平均給与よりも高い従業員を検索します。

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees);
Code language: SQL (Structured Query Language) (sql)

この例では、サブクエリはWHERE句で使用されています。このクエリからわかる点がいくつかあります。

まず、すべての従業員の平均給与を返すサブクエリを独立して実行できます。

SELECT 
    AVG(salary)
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

次に、データベースシステムはサブクエリを一度だけ評価する必要があります。

第三に、外部クエリはサブクエリから返された結果を利用します。外部クエリはその値のためにサブクエリに依存します。ただし、サブクエリは外部クエリに依存しません。時々、このサブクエリをプレーンサブクエリと呼びます。

プレーンサブクエリとは異なり、相関サブクエリは外部クエリの値を使用するサブクエリです。また、相関サブクエリは、外部クエリによって選択された各行に対して一度評価される場合があります。このため、相関サブクエリを使用するクエリは遅くなる可能性があります。

相関サブクエリは、繰り返しサブクエリまたは同期サブクエリとしても知られています。

SQL相関サブクエリの例

相関サブクエリをよりよく理解するために、いくつかの例を見てみましょう。

WHERE句のSQL相関サブクエリの例

次のクエリは、給与が自分の部署の従業員の平均給与よりも高いすべての従業員を検索します。

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary, 
    department_id
FROM
    employees e
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees
        WHERE
            department_id = e.department_id)
ORDER BY 
    department_id , 
    first_name , 
    last_name;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

この例では、外部クエリは次のとおりです。

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary, 
    department_id
FROM
    employees e
WHERE
    salary >
...
Code language: SQL (Structured Query Language) (sql)

相関サブクエリは次のとおりです。

SELECT
    AVG( list_price )
FROM
    products
WHERE
    category_id = p.category_id
Code language: SQL (Structured Query Language) (sql)

従業員ごとに、データベースシステムは相関サブクエリを一度実行して、現在の従業員の部署の従業員の平均給与を計算する必要があります。

SELECT句のSQL相関サブクエリの例

次のクエリは、従業員と、自分の部署のすべての従業員の平均給与を返します。

SELECT 
    employee_id,
    first_name,
    last_name,
    department_name,
    salary,
    (SELECT 
            ROUND(AVG(salary),0)
        FROM
            employees
        WHERE
            department_id = e.department_id) avg_salary_in_department
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
ORDER BY 
    department_name, 
    first_name, 
    last_name;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

従業員ごとに、データベースシステムは相関サブクエリを一度実行して、従業員の部署ごとの平均給与を計算する必要があります。

EXISTS演算子を使用したSQL相関サブクエリの例

相関サブクエリをEXISTS演算子とよく使用します。たとえば、次のクエリは、扶養家族がいないすべての従業員を返します。

SELECT 
    employee_id, 
    first_name, 
    last_name
FROM
    employees e
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            dependents d
        WHERE
            d.employee_id = e.employee_id)
ORDER BY first_name , 
         last_name;
Code language: SQL (Structured Query Language) (sql)

次の図は出力を示しています。

このチュートリアルでは、SQL相関サブクエリとその複雑なクエリの形成への適用方法について学びました。