MySQL - 相関副問い合わせ

概要

相関副問い合わせ (相関サブクエリ) は、外部クエリの列を参照するサブクエリである。

相関サブクエリは、外部クエリの各行に対して異なる値を返すことができるため、非相関サブクエリよりも柔軟な条件指定が可能である。
ただし、外部クエリの各行ごとにサブクエリが実行されるため、パフォーマンスへの影響に注意が必要である。

相関サブクエリは、SELECT 句、WHERE 句、HAVING 句で使用でき、また UPDATE 文や DELETE 文でも使用できる。
典型的な用途として、各行に対する集計値の取得、グループごとの最大値を持つ行の抽出、存在チェックによるフィルタリングなどがある。

相関サブクエリの実行回数は外部クエリの行数に比例する (O(M×N) の計算量) ため、大量のデータに対してはパフォーマンス問題が発生する可能性がある。
MySQL 8.0以降では、オプティマイザーが相関サブクエリを自動的に最適化する機能があり、特定の条件下では JOIN や派生テーブルに変換される。
MySQL 9.0では、LIMIT 1 を持つスカラー相関サブクエリの最適化も導入されている。

副問い合わせ (サブクエリ) 全般については、MySQL - 副問い合わせのページを参照すること。

基本構文と実行の流れ

基本構文

相関サブクエリは、サブクエリ内で外部クエリのテーブルの列を参照する。

外部クエリのテーブルにはエイリアスを付け、サブクエリ内でそのエイリアスを使用して列を参照する。

 # 基本構文
 SELECT column1, column2
    FROM table1 AS outer_table
    WHERE column3 operator (SELECT aggregate_function(column4)
                              FROM table2
                              WHERE table2.column5 = outer_table.column6);
 
 # 例 : 各従業員と所属部署の平均給与を比較
 SELECT name, salary
    FROM employees AS e1
    WHERE salary > (SELECT AVG(salary)
                      FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);

サブクエリ内で外部クエリのテーブル (e1) の列 (e1.department_id) を参照している点が特徴である。

同一テーブルに対する相関サブクエリでは、外部テーブルと内部テーブルで異なるエイリアスを付ける必要がある。

 # 同一テーブルへの相関サブクエリ (エイリアス必須)
 SELECT e1.name, e1.salary
    FROM employees AS e1
    WHERE e1.salary > (SELECT AVG(e2.salary)
                         FROM employees AS e2
                         WHERE e2.department_id = e1.department_id);
 
 # エイリアスがないとエラーになる
 SELECT name, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary)
                      FROM employees
                      WHERE department_id = department_id);
 # サブクエリ内のdepartment_idがどちらのテーブルを指すか不明確

実行の流れ

相関サブクエリの実行は、以下に示すステップで行われる。

 # 例: 部署平均給与より高い従業員を取得
 SELECT e1.name, e1.salary
    FROM employees AS e1
    WHERE e1.salary > (SELECT AVG(e2.salary)
                         FROM employees AS e2
                         WHERE e2.department_id = e1.department_id);

実行フローを以下に示す。

1. 外部クエリが最初の行をフェッチ (例: 従業員ID=1、department_id=10)

   外部クエリのテーブル e1 から1行目を読み取る。

2. サブクエリがその行の値を使用して実行

   サブクエリは e1.department_id (=10) を受け取り、部署10の平均給与を計算する。

3. サブクエリの結果と外部クエリの列を比較

   従業員の給与 (e1.salary) が部署平均給与より大きいかを判定する。

4. 条件を満たせば結果セットに含める

   条件が TRUE なら、その行を結果に含める。

5. 外部クエリの次の行に対してステップ1〜4を繰り返す

   全ての行が処理されるまで繰り返す。

このように、相関サブクエリは外部クエリの各行ごとに実行される。

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

相関サブクエリと非相関サブクエリの違いを以下に示す。

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

項目	非相関サブクエリ	相関サブクエリ
外部クエリへの参照	外部クエリの列を参照しない	外部クエリの列を参照する
実行回数	1回のみ	外部クエリの各行ごと (N回)
独立性	単独で実行可能	外部クエリなしでは実行不可
パフォーマンス	一般的に高速	外部行数が多いと遅い
使用場面	全体の集計値との比較	行ごとの動的な比較

 # 非相関サブクエリ (全従業員の平均給与と比較)
 SELECT name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);
 # サブクエリは1回のみ実行される
 
 # 相関サブクエリ (各従業員が所属部署の平均給与と比較)
 SELECT name, salary FROM employees AS e1
    WHERE salary > (SELECT AVG(salary) FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);
 # サブクエリは外部クエリの各行ごとに実行される

WHERE句での相関サブクエリ

比較演算子との組み合わせ

WHERE 句での相関サブクエリは、比較演算子と組み合わせて使用されることが多い。

 # 各従業員の給与と所属部署の平均給与を比較
 SELECT name, salary, department_id
    FROM employees AS e1
    WHERE salary > (SELECT AVG(salary)
                      FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);
 
 # 各商品の価格とそのカテゴリの最高価格を比較
 SELECT product_name, price
    FROM products AS p1
    WHERE price = (SELECT MAX(price)
                     FROM products AS p2
                     WHERE p2.category_id = p1.category_id);
 
 # 各注文の金額とそのユーザの平均注文金額を比較
 SELECT order_id, amount
    FROM orders AS o1
    WHERE amount > (SELECT AVG(amount)
                      FROM orders AS o2
                      WHERE o2.user_id = o1.user_id);
 
 # 各従業員の入社日と同じ部署の最早入社日を比較
 SELECT name, hire_date
    FROM employees AS e1
    WHERE hire_date = (SELECT MIN(hire_date)
                         FROM employees AS e2
                         WHERE e2.department_id = e1.department_id);

グループごとの最大値を持つ行を取得するパターンは、MySQLの公式ドキュメント 5.6.4で紹介されている典型的な手法である。

 # グループごとの最大値を持つ行を取得
 SELECT product_id, product_name, category_id, price
    FROM products AS p1
    WHERE price = (SELECT MAX(p2.price)
                     FROM products AS p2
                     WHERE p2.category_id = p1.category_id);
 
 # 各部署で最も給与の高い従業員を取得
 SELECT department_id, name, salary
    FROM employees AS e1
    WHERE salary = (SELECT MAX(e2.salary)
                      FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);

EXISTS / NOT EXISTS

EXISTS 演算子は、相関サブクエリと組み合わせて使用されることが多い。

EXISTS は、サブクエリが1行以上返す場合に TRUE を返す。
NOT EXISTS は、サブクエリが0行の場合に TRUE を返す。

 # 注文がある従業員を取得
 SELECT name FROM employees AS e
    WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.employee_id = e.id);
 
 # 注文のない従業員を取得
 SELECT name FROM employees AS e
    WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.employee_id = e.id);
 
 # 特定条件を満たす注文がある顧客を取得
 SELECT name FROM customers AS c
    WHERE EXISTS (SELECT 1 FROM orders AS o
                     WHERE o.customer_id = c.id
                       AND o.status = 'completed'
                       AND o.amount > 10000);
 
 # 2024年以降に注文のないユーザを取得
 SELECT name FROM users AS u
    WHERE NOT EXISTS (SELECT 1 FROM orders AS o
                         WHERE o.user_id = u.id
                           AND o.ordered_at >= '2024-01-01');

EXISTS では、慣習として SELECT 1 を使用する。

EXISTS は行の存在のみをチェックするため、サブクエリで選択する列は結果に影響しない。
SELECT 1、SELECT *、SELECT NULL のいずれも同じ結果となるが、SELECT 1 の方が意図が明確である。

 # 以下は全て同じ結果
 WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
 WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);
 WHERE EXISTS (SELECT NULL FROM orders WHERE orders.user_id = users.id);

EXISTS は NULL の影響を受けないため、NOT IN より安全である。

 # NOT INはNULLがある場合に問題が発生する
 SELECT name FROM employees
    WHERE department_id NOT IN (SELECT manager_id FROM departments);
 # manager_idにNULLが含まれる場合、結果は0行
 
 # NOT EXISTSはNULLの影響を受けない
 SELECT name FROM employees AS e
    WHERE NOT EXISTS (SELECT 1 FROM departments AS d
                         WHERE d.manager_id = e.department_id);
 # NULLがあっても正しく動作する

SELECT句での相関サブクエリ

SELECT 句で相関サブクエリを使用すると、外部クエリの各行に対してスカラー値を取得できる。

各行に対して集計値や関連データを取得する際に有用である。

 # 各ユーザの注文数を取得
 SELECT
    id,
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
    FROM users;
 
 # 各部署の従業員数を取得
 SELECT
    department_name,
    (SELECT COUNT(*) FROM employees WHERE employees.department_id = departments.id) AS employee_count
    FROM departments;
 
 # 各商品の最終注文日を取得
 SELECT
    product_id,
    product_name,
    (SELECT MAX(ordered_at) FROM order_items WHERE order_items.product_id = products.id) AS last_ordered
    FROM products;
 
 # 各従業員の所属部署の平均給与を取得
 SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees AS e2 WHERE e2.department_id = e1.department_id) AS dept_avg_salary
    FROM employees AS e1;

複数の相関サブクエリを組み合わせることもできる。

 # 各ユーザの注文数と合計金額を取得
 SELECT
    id,
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count,
    (SELECT SUM(amount) FROM orders WHERE orders.user_id = users.id) AS total_amount
    FROM users;
 
 # 各商品の在庫数と注文数を取得
 SELECT
    product_name,
    (SELECT SUM(quantity) FROM inventory WHERE inventory.product_id = products.id) AS stock,
    (SELECT SUM(quantity) FROM order_items WHERE order_items.product_id = products.id) AS ordered
    FROM products;

注意事項として、SELECT 句の相関サブクエリは必ずスカラー値 (1行1列) を返す必要がある。

複数行を返すとエラー (ERROR 1242: Subquery returns more than 1 row) が発生する。

 # エラーになる例 (複数行が返される可能性)
 SELECT
    name,
    (SELECT salary FROM employees WHERE department_id = departments.id) AS dept_salary
    FROM departments;
 # 各部署に複数の従業員がいる場合、エラー
 
 # 集計関数を使用してスカラー値にする
 SELECT
    name,
    (SELECT AVG(salary) FROM employees WHERE department_id = departments.id) AS avg_dept_salary
    FROM departments;
 
 # LIMIT 1で単一行に制限する
 SELECT
    name,
    (SELECT salary FROM employees WHERE department_id = departments.id LIMIT 1) AS sample_salary
    FROM departments;

SELECT 句の相関サブクエリは、外部クエリの各行ごとに実行されるため、パフォーマンスへの影響が大きい。
大量のデータに対しては JOIN への書き換えを検討する必要がある。

HAVING句での相関サブクエリ

HAVING 句で相関サブクエリを使用すると、集約結果と動的な値を比較できる。

ただし、HAVING 句の相関サブクエリには制限がある。
相関列 (外部クエリの列への参照) は、サブクエリの WHERE 句にのみ記述でき、サブクエリの SELECT 句や HAVING 句には記述できない。

 # 全体平均より多くの従業員を持つ部署を取得
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > (SELECT AVG(cnt)
                         FROM (SELECT COUNT(*) AS cnt
                                 FROM employees
                                 GROUP BY department_id) AS dept_counts);
 
 # 各ユーザの注文数が全体平均より多いユーザを取得
 SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) > (SELECT AVG(cnt)
                         FROM (SELECT COUNT(*) AS cnt
                                 FROM orders
                                 GROUP BY user_id) AS user_counts);
 
 # 各カテゴリの合計売上が全体合計の10%以上のカテゴリを取得
 SELECT category_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY category_id
    HAVING SUM(amount) >= (SELECT SUM(amount) * 0.1 FROM order_items);

GROUP BY と組み合わせて、グループごとの集計結果を条件として使用できる。

 # 各部署の平均給与が全体平均給与より高い部署を取得
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
 
 # 各カテゴリの商品数が全カテゴリの平均商品数より多いカテゴリを取得
 SELECT category_id, COUNT(*) AS product_count
    FROM products
    GROUP BY category_id
    HAVING COUNT(*) > (SELECT AVG(cnt)
                         FROM (SELECT COUNT(*) AS cnt
                                 FROM products
                                 GROUP BY category_id) AS cat_counts);

UPDATE / DELETE文での相関サブクエリ

UPDATE文での使用

UPDATE 文で相関サブクエリを使用すると、他のテーブルや同じテーブルの他の行の値に基づいて更新できる。

SET 句と WHERE 句の両方で相関サブクエリを使用できる。

 # SET句で相関サブクエリを使用
 UPDATE employees AS e1
    SET salary = (SELECT AVG(salary)
                    FROM employees AS e2
                    WHERE e2.department_id = e1.department_id)
    WHERE hire_date < '2020-01-01';
 
 # WHERE句で相関サブクエリを使用
 UPDATE products
    SET status = 'discontinued'
    WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE order_items.product_id = products.id);
 
 # 各ユーザの累積購入金額を更新
 UPDATE users AS u
    SET total_purchased = (SELECT SUM(amount)
                             FROM orders AS o
                             WHERE o.user_id = u.id)
    WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = u.id);
 
 # 各商品の在庫を最新の入荷数で更新
 UPDATE products AS p
    SET stock = (SELECT quantity
                   FROM inventory AS i
                   WHERE i.product_id = p.id
                   ORDER BY received_at DESC
                   LIMIT 1)
    WHERE EXISTS (SELECT 1 FROM inventory WHERE inventory.product_id = p.id);

複数列を更新する場合も相関サブクエリを使用できる。

 # 複数列を相関サブクエリで更新
 UPDATE employees AS e
    SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id),
        updated_at = NOW()
    WHERE hire_date < '2020-01-01';

詳細は、MySQL - UPDATE のページを参照すること。

DELETE文での使用

DELETE 文で相関サブクエリを使用すると、他のテーブルの値に基づいて行を削除できる。

 # 注文のないユーザを削除
 DELETE FROM users
    WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
 
 # 在庫のない商品を削除
 DELETE FROM products AS p
    WHERE NOT EXISTS (SELECT 1 FROM inventory AS i WHERE i.product_id = p.id);
 
 # 最終ログイン日が1年以上前のユーザを削除
 DELETE FROM users AS u
    WHERE (SELECT MAX(logged_at) FROM login_logs WHERE login_logs.user_id = u.id) < DATE_SUB(NOW(), INTERVAL 1 YEAR);
 
 # アーカイブ済みのログを削除
 DELETE FROM logs
    WHERE EXISTS (SELECT 1 FROM archived_logs WHERE archived_logs.log_id = logs.id);

EXISTS / NOT EXISTS を使用した削除は、IN / NOT IN よりも安全である。

 # NOT INは、サブクエリにNULLがあると全て削除されない
 DELETE FROM products
    WHERE id NOT IN (SELECT product_id FROM order_items);
 # product_idにNULLがある場合、削除が行われない
 
 # NOT EXISTSは、NULLの影響を受けない
 DELETE FROM products AS p
    WHERE NOT EXISTS (SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id);
 # NULLがあっても正しく動作する

詳細は、MySQL - DELETE のページを参照すること。

相関サブクエリのパターン

グループごとの最大値を持つ行の取得

グループごとの最大値を持つ行を取得するパターンは、相関サブクエリの典型的な用途である。

MySQL公式ドキュメント5.6.4で紹介されている手法である。

 # 各カテゴリで最も高い価格の商品を取得
 SELECT category_id, product_name, price
    FROM products AS p1
    WHERE price = (SELECT MAX(price)
                     FROM products AS p2
                     WHERE p2.category_id = p1.category_id);
 
 # 各部署で最も給与の高い従業員を取得
 SELECT department_id, name, salary
    FROM employees AS e1
    WHERE salary = (SELECT MAX(salary)
                      FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);
 
 # 各ユーザの最新注文を取得
 SELECT user_id, order_id, ordered_at
    FROM orders AS o1
    WHERE ordered_at = (SELECT MAX(ordered_at)
                          FROM orders AS o2
                          WHERE o2.user_id = o1.user_id);
 
 # 各カテゴリで最も在庫の多い商品を取得
 SELECT category_id, product_name, stock
    FROM products AS p1
    WHERE stock = (SELECT MAX(stock)
                     FROM products AS p2
                     WHERE p2.category_id = p1.category_id);

このパターンでは、同じ最大値を持つ複数の行が返される可能性がある。

 # 最高価格が同じ商品が複数ある場合、全て返される
 SELECT category_id, product_name, price
    FROM products AS p1
    WHERE price = (SELECT MAX(price)
                     FROM products AS p2
                     WHERE p2.category_id = p1.category_id);
 # カテゴリ1に価格1000円の商品が2つある場合、両方とも返される

存在チェックによるフィルタリング

EXISTS / NOT EXISTS を使用した存在チェックは、相関サブクエリの主要な用途である。

 # 注文があるユーザのみを取得
 SELECT id, name FROM users AS u
    WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);
 
 # 在庫のある商品のみを取得
 SELECT product_name FROM products AS p
    WHERE EXISTS (SELECT 1 FROM inventory AS i
                     WHERE i.product_id = p.id
                       AND i.quantity > 0);
 
 # 特定期間に注文があった顧客を取得
 SELECT name FROM customers AS c
    WHERE EXISTS (SELECT 1 FROM orders AS o
                     WHERE o.customer_id = c.id
                       AND o.ordered_at BETWEEN '2024-01-01' AND '2024-12-31');
 
 # レビューのある商品を取得
 SELECT product_name FROM products AS p
    WHERE EXISTS (SELECT 1 FROM reviews AS r WHERE r.product_id = p.id);

NOT EXISTS を使用した除外パターンも有用である。

 # 注文のないユーザを取得
 SELECT id, name FROM users AS u
    WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);
 
 # 在庫のない商品を取得
 SELECT product_name FROM products AS p
    WHERE NOT EXISTS (SELECT 1 FROM inventory AS i WHERE i.product_id = p.id);
 
 # 2024年に注文のなかった顧客を取得
 SELECT name FROM customers AS c
    WHERE NOT EXISTS (SELECT 1 FROM orders AS o
                         WHERE o.customer_id = c.id
                           AND o.ordered_at >= '2024-01-01'
                           AND o.ordered_at < '2025-01-01');

累積計算

相関サブクエリを使用して、累積合計 (ランニングトータル) や累積カウントを計算できる。

 # 各注文の累積合計金額を計算
 SELECT
    order_id,
    ordered_at,
    amount,
    (SELECT SUM(amount)
       FROM orders AS o2
       WHERE o2.user_id = o1.user_id
         AND o2.ordered_at <= o1.ordered_at) AS cumulative_amount
    FROM orders AS o1
    ORDER BY user_id, ordered_at;
 
 # 各日の累積販売数を計算
 SELECT
    sale_date,
    quantity,
    (SELECT SUM(quantity)
       FROM sales AS s2
       WHERE s2.product_id = s1.product_id
         AND s2.sale_date <= s1.sale_date) AS cumulative_quantity
    FROM sales AS s1
    ORDER BY product_id, sale_date;
 
 # 各月の累積ユーザ数を計算
 SELECT
    signup_month,
    new_users,
    (SELECT SUM(new_users)
       FROM monthly_stats AS m2
       WHERE m2.signup_month <= m1.signup_month) AS cumulative_users
    FROM monthly_stats AS m1
    ORDER BY signup_month;

ただし、累積計算は行数に比例してコストが増加するため、大量のデータに対しては窓関数 (Window Function) の使用を推奨する。

 # MySQL 8.0以降では窓関数を使用する方が効率的
 SELECT
    order_id,
    ordered_at,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY ordered_at) AS cumulative_amount
    FROM orders
    ORDER BY user_id, ordered_at;

JOINへの書き換え

相関サブクエリは、多くの場合で JOIN に書き換えることができる。

JOIN への書き換えにより、パフォーマンスが改善される場合がある。

EXISTS → INNER JOIN

EXISTS を使用した相関サブクエリは、INNER JOIN に書き換えることができる。

 # EXISTSを使用した相関サブクエリ
 SELECT name FROM users AS u
    WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);
 
 # INNER JOINに書き換え
 SELECT DISTINCT u.name FROM users AS u
    INNER JOIN orders AS o ON u.id = o.user_id;

ただし、INNER JOIN では重複行が発生する可能性があるため、DISTINCT が必要である。

 # 条件付きEXISTS
 SELECT name FROM customers AS c
    WHERE EXISTS (SELECT 1 FROM orders AS o
                     WHERE o.customer_id = c.id
                       AND o.status = 'completed');
 
 # INNER JOINに書き換え
 SELECT DISTINCT c.name FROM customers AS c
    INNER JOIN orders AS o ON c.id = o.customer_id
    WHERE o.status = 'completed';

スカラー相関サブクエリ → JOIN

SELECT 句の相関サブクエリは、JOIN と派生テーブルに書き換えることができる。

 # SELECT句で相関サブクエリを使用
 SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees AS e2
       WHERE e2.department_id = e1.department_id) AS dept_avg_salary
    FROM employees AS e1;
 
 # JOINに書き換え
 SELECT
    e.name,
    e.salary,
    dept_avg.avg_salary AS dept_avg_salary
    FROM employees AS e
    JOIN (SELECT department_id, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department_id) AS dept_avg
       ON e.department_id = dept_avg.department_id;

複数の相関サブクエリも同様に書き換えられる。

 # 複数の相関サブクエリ
 SELECT
    id,
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count,
    (SELECT SUM(amount) FROM orders WHERE orders.user_id = users.id) AS total_amount
    FROM users;
 
 # JOINに書き換え
 SELECT
    u.id,
    u.name,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.total_amount, 0) AS total_amount
    FROM users AS u
    LEFT JOIN (SELECT user_id,
                      COUNT(*) AS order_count,
                      SUM(amount) AS total_amount
                 FROM orders
                 GROUP BY user_id) AS o
       ON u.id = o.user_id;

NOT EXISTS → LEFT JOIN

NOT EXISTS を使用した相関サブクエリは、LEFT JOIN と IS NULL に書き換えることができる。

 # NOT EXISTSを使用した相関サブクエリ
 SELECT name FROM users AS u
    WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);

 # LEFT JOINに書き換え
 SELECT u.name FROM users AS u
    LEFT JOIN orders AS o ON u.id = o.user_id
    WHERE o.user_id IS NULL;

LEFT JOIN では、結合先のテーブルに該当行がない場合、結合先の列が NULL になる。

 # 在庫のない商品を取得
 
 # NOT EXISTSを使用
 SELECT product_name FROM products AS p
    WHERE NOT EXISTS (SELECT 1 FROM inventory AS i WHERE i.product_id = p.id);
 
 # LEFT JOINに書き換え
 SELECT p.product_name FROM products AS p
    LEFT JOIN inventory AS i ON p.id = i.product_id
    WHERE i.product_id IS NULL;

パフォーマンスと最適化

実行コストの理解

相関サブクエリの実行コストは、外部クエリの行数に比例する。

外部クエリがM行、サブクエリがN行を処理する場合、実行時間はO(M×N)になる可能性がある。

 # この相関サブクエリは、外部クエリの各行ごとに実行される
 SELECT name, salary FROM employees AS e1
    WHERE salary > (SELECT AVG(salary) FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);
 
 # 外部クエリが1000行、各部署が平均100人の場合:
 # - 外部クエリ : 1000行をスキャン
 # - サブクエリ : 各行ごとに100行をスキャン (合計100,000行)
 # - 合計       : 101,000行の処理

インデックスの有無により、パフォーマンスが大きく変わる。

 # インデックスがない場合、フルテーブルスキャンが発生
 SELECT name FROM users AS u
    WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);
 # ordersテーブルのuser_idにインデックスがないと、各行ごとにフルスキャン
 
 # インデックスがある場合、高速に検索可能
 CREATE INDEX idx_user_id ON orders(user_id);
 # インデックスにより、各行の検索が高速化される

オプティマイザーの自動最適化

MySQL 8.0以降では、オプティマイザーが相関サブクエリを自動的に最適化する場合がある。

optimizer_switch の subquery_to_derived オプションにより、相関サブクエリが派生テーブルに変換される。

 # optimizer_switchの確認
 SELECT @@optimizer_switch;
 
 # subquery_to_derivedの有効化 (MySQL 8.0.21以降ではデフォルトで有効)
 SET optimizer_switch='subquery_to_derived=on';

変換の条件は以下の通りである。

• サブクエリが SELECT 句に存在する

  SELECT 句の相関スカラーサブクエリが対象。

• サブクエリが集計関数 (COUNT、SUM、AVG等) を使用している

  集計結果を返すサブクエリが対象。

• サブクエリが外部クエリのテーブルを参照している (相関サブクエリである)

  相関列を持つサブクエリが対象。

• サブクエリが単一の値を返す (スカラーサブクエリである)

  スカラー値を返すサブクエリが対象。

 # この相関サブクエリは
 SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
    FROM users;
 
 # 内部的に以下のように変換される可能性がある
 SELECT
    u.name,
    COALESCE(derived.order_count, 0) AS order_count
    FROM users AS u
    LEFT JOIN (SELECT user_id, COUNT(*) AS order_count
                 FROM orders
                 GROUP BY user_id) AS derived
       ON u.id = derived.user_id;

MySQL 9.0では、LIMIT 1 を持つスカラー相関サブクエリの最適化も導入されている。

 # MySQL 9.0では、この相関サブクエリが最適化される
 SELECT
    name,
    (SELECT product_name FROM order_items
       WHERE order_items.order_id = orders.id
       LIMIT 1) AS first_product
    FROM orders;
 # LIMIT 1により、最初の一致で停止することが保証される

EXPLAINでの確認方法

EXPLAIN 文を使用して、相関サブクエリの実行計画を確認できる。

 # 実行計画を確認
 EXPLAIN SELECT name, salary FROM employees AS e1
    WHERE salary > (SELECT AVG(salary) FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);

select_type 列で、サブクエリの種類を確認できる。

• DEPENDENT SUBQUERY

  相関サブクエリ (外部クエリの各行ごとに実行される)。

• SUBQUERY

  非相関サブクエリ (1回のみ実行される)。

• DERIVED

  派生テーブル (オプティマイザーによる変換)。

 # select_typeを確認
 EXPLAIN SELECT name FROM employees AS e
    WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.employee_id = e.id);
 # select_type: DEPENDENT SUBQUERY (相関サブクエリ)
 
 # オプティマイザーによる最適化後
 # select_type: SIMPLE (セミジョインに変換された場合)

MySQL 8.0.18以降では、EXPLAIN ANALYZE で実際の実行時間を確認できる。

 # 実際の実行時間を確認
 EXPLAIN ANALYZE SELECT name FROM users AS u
    WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.user_id = u.id);
 
 # 実行時間とコストが表示される
 # actual time, rows, loops などの情報が取得できる

EXPLAIN の Extra 列には、追加情報が表示される。

• Using where

  WHERE句による絞り込みが行われている。

• Using index

  インデックスのみで処理が完結している (カバリングインデックス)。

• Using temporary

  一時テーブルが使用されている。

• Start temporary, End temporary

  セミジョイン最適化 (Duplicate Weedout) が適用されている。

 # Extra列の確認
 EXPLAIN SELECT name FROM employees AS e1
    WHERE salary > (SELECT AVG(salary) FROM employees AS e2
                      WHERE e2.department_id = e1.department_id);
 # Extra: Using where (WHERE句による絞り込み)