MySQL - 副問い合わせ

概要

副問い合わせ (サブクエリ) は、別のSQL文の内部に埋め込まれた SELECT 文である。

サブクエリは、SELECT、INSERT、UPDATE、DELETE 文のさまざまな句で使用でき、複雑な条件や動的な値を取得するための強力な機能である。
サブクエリは必ず丸括弧 () で囲み、外部クエリの一部として評価される。

サブクエリには、スカラーサブクエリ (単一の値を返す)、行サブクエリ (単一行を返す)、テーブルサブクエリ (複数行複数列を返す) といった分類がある。
また、外部クエリとの関係により、非相関サブクエリ (独立して実行可能) と相関サブクエリ (外部クエリを参照する) に分けられる。

相関サブクエリの詳細は、MySQL - 相関副問い合わせのページを参照すること。

サブクエリは可読性が高く、複雑なデータ取得を直感的に表現できるが、パフォーマンスには注意が必要である。
大量のデータに対しては JOIN への書き換えを検討する必要がある。

MySQL 8.0以降では、オプティマイザーがサブクエリを自動的に最適化し、セミジョインやマテリアライゼーション (実体化) といった技術により、効率的に実行される。

基本構文と分類

副問い合わせの基本構文

副問い合わせは、必ず丸括弧 () で囲んだ SELECT 文として記述する。

 # 基本構文
 SELECT column1, column2
    FROM table1
    WHERE column3 = (SELECT column4 FROM table2 WHERE condition);
 
 # サブクエリは必ず丸括弧で囲む
 (SELECT ... FROM ... WHERE ...)

サブクエリは、以下に示すさまざまな句で使用できる。

 # SELECT句でのサブクエリ
 SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
    FROM users;
 
 # FROM句でのサブクエリ (派生テーブル)
 SELECT dept_name, avg_salary
    FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
    JOIN departments ON dept_avg.department_id = departments.id;
 
 # WHERE句でのサブクエリ
 SELECT name FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);
 
 # HAVING句でのサブクエリ
 SELECT department_id, COUNT(*)
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM employees GROUP BY department_id) AS t);

副問い合わせの分類

副問い合わせは、返す値によって以下の3つに分類される。

サブクエリの返す値による分類

分類	説明	使用例
スカラーサブクエリ	単一の値 (1行1列) を返す	WHERE price > (SELECT AVG(price) FROM products)
行サブクエリ	単一行 (1行複数列) を返す	WHERE (col1, col2) = (SELECT col1, col2 FROM ... LIMIT 1)
テーブルサブクエリ	複数行複数列を返す	WHERE id IN (SELECT user_id FROM orders)

また、外部クエリとの関係により、以下の2つに分類される。

サブクエリの外部クエリとの関係による分類

分類	説明	実行回数
非相関サブクエリ	外部クエリから独立して実行可能	1回のみ
相関サブクエリ	外部クエリの列を参照する	外部クエリの各行ごと

 # 非相関サブクエリ (外部クエリに依存しない)
 SELECT name FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);

相関サブクエリの構文と使用方法については、MySQL - 相関副問い合わせのページを参照すること。

スカラーサブクエリ

スカラーサブクエリは、単一の値 (1行1列) を返すサブクエリである。

スカラーサブクエリは、比較演算子 (=, <>, <, >, <=, >=) と組み合わせて使用されることが多く、単一の値が必要な場面で使用される。
サブクエリが複数行を返す場合は、エラー (ER_SUBQUERY_NO_1_ROW) が発生する。

SELECT句でのスカラーサブクエリ

SELECT 句でスカラーサブクエリを使用すると、各行に対してサブクエリが評価され、その結果が列として表示される。

 # 各従業員に対して所属部署の平均給与を取得
 SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS dept_avg_salary
    FROM employees e1;
 
 # ユーザごとの注文数を取得
 SELECT
    id,
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
    FROM users;
 
 # 商品の最終注文日を取得
 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句でのスカラーサブクエリは外部クエリの列を参照する相関サブクエリになることが多いため、大量のデータに対してはパフォーマンスに影響する。
詳細は、MySQL - 相関副問い合わせのページを参照すること。

WHERE句でのスカラーサブクエリ

WHERE 句でスカラーサブクエリを使用すると、動的な値を条件として使用できる。

 # 平均給与より高い従業員を取得
 SELECT name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);
 
 # 最も高い価格の商品を取得
 SELECT product_name, price FROM products
    WHERE price = (SELECT MAX(price) FROM products);
 
 # 最も最近の注文日より古い注文を取得
 DELETE FROM orders
    WHERE ordered_at < (SELECT DATE_SUB(MAX(ordered_at), INTERVAL 1 YEAR) FROM orders);
 
 # 特定部署の平均給与より高い従業員を取得
 SELECT name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 10);

※エラーケース
スカラーサブクエリが複数行を返すとエラーになる。

 # エラーになる例 (複数行が返される)
 SELECT name FROM employees
    WHERE salary = (SELECT salary FROM employees WHERE department_id = 10);
 
 # エラーメッセージ
 # ERROR 1242 (21000): Subquery returns more than 1 row

このような場合は、IN や LIMIT 1 を使用する。

 # IN演算子を使用
 SELECT name FROM employees
    WHERE salary IN (SELECT salary FROM employees WHERE department_id = 10);
 
 # LIMIT 1で単一行に制限
 SELECT name FROM employees
    WHERE salary = (SELECT salary FROM employees WHERE department_id = 10 LIMIT 1);

行サブクエリ

行サブクエリは、単一行 (1行複数列) を返すサブクエリである。

ROW() コンストラクタと組み合わせることで、複数列の値を同時に比較できる。

 # ROW()コンストラクタを使用した行サブクエリ
 SELECT * FROM employees
    WHERE ROW(department_id, salary) = (SELECT department_id, salary FROM employees WHERE id = 100);
 
 # ROW()を省略した形式 (同じ意味)
 SELECT * FROM employees
    WHERE (department_id, salary) = (SELECT department_id, salary FROM employees WHERE id = 100);
 
 # 複数列の比較
 SELECT * FROM products
    WHERE (category_id, price) = (SELECT category_id, MAX(price) FROM products WHERE category_id = 5);
 
 # 不等号での比較
 SELECT * FROM employees
    WHERE (department_id, salary) > (SELECT department_id, salary FROM employees WHERE id = 50);

行サブクエリは、複数列を同時に比較する必要がある場合に有用である。

 # 以下の2つは同じ意味
 
 # 行サブクエリを使用
 WHERE (col1, col2) = (SELECT col1, col2 FROM t WHERE id = 1);
 
 # 個別に比較
 WHERE col1 = (SELECT col1 FROM t WHERE id = 1)
    AND col2 = (SELECT col2 FROM t WHERE id = 1);

※注意
行サブクエリが複数行を返すとエラーになる。
LIMIT 1を使用するか、単一行を保証する条件を指定する。

テーブルサブクエリ (派生テーブル)

FROM句での派生テーブル

FROM 句でサブクエリを使用すると、仮想的なテーブル (派生テーブル) を作成できる。

派生テーブルは、一時的な結果セットとして扱われ、外部クエリから列を参照できる。

 # 基本的な派生テーブル
 SELECT dept_name, avg_salary
    FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
    JOIN departments ON dept_avg.department_id = departments.id;
 
 # 派生テーブルを使用した集計
 SELECT category, MAX(total_sales) AS max_sales
    FROM (SELECT category_id AS category, SUM(amount) AS total_sales FROM orders GROUP BY category_id) AS sales
    GROUP BY category;
 
 # 複数の派生テーブルを結合
 SELECT u.name, o.order_count, p.product_count
    FROM users u
    JOIN (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS o ON u.id = o.user_id
    JOIN (SELECT user_id, COUNT(DISTINCT product_id) AS product_count FROM order_items GROUP BY user_id) AS p ON u.id = p.user_id;

エイリアスの必須性

派生テーブルには、必ずエイリアス (別名) を指定する必要がある。

エイリアスを指定しないとエラーになる。

 # エラーになる例 (エイリアスなし)
 SELECT * FROM (SELECT * FROM employees);
 
 # エラーメッセージ
 # ERROR 1248 (42000): Every derived table must have its own alias
 
 # 正しい形式 (エイリアス指定)
 SELECT * FROM (SELECT * FROM employees) AS emp;

エイリアスは AS キーワードで指定する。

 # ASキーワードを使用 (推奨)
 SELECT * FROM (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS dept_avg;
 
 # ASキーワードを省略 (可能だが非推奨)
 SELECT * FROM (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) dept_avg;

MySQL 8.0以降では、オプティマイザーが派生テーブルを外部クエリにマージする最適化を行う場合がある。

 # この派生テーブルはマージされる可能性がある
 SELECT * FROM (SELECT * FROM employees WHERE department_id = 10) AS emp
    WHERE salary > 50000;
 
 # 内部的には以下のように書き換えられる
 SELECT * FROM employees
    WHERE department_id = 10 AND salary > 50000;

WHERE句での副問い合わせ

IN / NOT IN

IN 演算子は、サブクエリの結果セットに値が含まれるかを判定する。
NOT IN 演算子は、サブクエリの結果セットに値が含まれないかを判定する。

 # INを使用した基本的な例
 SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # NOT INを使用した例
 SELECT product_name FROM products
    WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);
 
 # 複数の値をINで指定
 SELECT name FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');
 
 # サブクエリの結果が0行の場合、INはFALSE、NOT INはTRUEになる
 SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'NonExistent');
 # → 結果は0行
 
 SELECT name FROM employees
    WHERE department_id NOT IN (SELECT id FROM departments WHERE location = 'NonExistent');
 # → 全行が返される

※NULLの重要な注意点
NOT INでサブクエリがNULLを含む場合、結果は常に0行になる。

 # サブクエリがNULLを返す場合の問題
 SELECT name FROM employees
    WHERE department_id NOT IN (SELECT manager_id FROM departments);
 # manager_idにNULLが含まれる場合、結果は0行
 
 # 理由:
 # NOT IN (1, 2, NULL) は、全てと異なることが必要だが、NULLとの比較は常にUNKNOWN
 # department_id <> 1 AND department_id <> 2 AND department_id <> NULL
 # → 最後の条件がUNKNOWNのため、全体がUNKNOWN (FALSEとして扱われる)

この問題を回避するには、以下の方法を使用する。

 # 回避策 1 : WHERE IS NOT NULLを追加
 SELECT name FROM employees
    WHERE department_id NOT IN (SELECT manager_id FROM departments WHERE manager_id IS NOT NULL);
 
 # 回避策 2 : NOT EXISTSを使用 (NULLの影響を受けない)
 SELECT name FROM employees e
    WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.department_id);

EXISTS / NOT EXISTS

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

 # EXISTSを使用した基本的な例
 SELECT name FROM employees e
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
 
 # NOT EXISTSを使用した例
 SELECT name FROM employees e
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
 
 # EXISTSは相関サブクエリで使用されることが多い
 SELECT department_name FROM departments d
    WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > 100000);
 
 # 注文のないユーザを取得
 SELECT name FROM users u
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

EXISTS では、慣習として SELECT 1 を使用する。
SELECT * でも結果は同じだが、SELECT 1 の方が意図が明確である。

 # SELECT 1を使用 (推奨)
 WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
 
 # SELECT *を使用 (結果は同じ)
 WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);
 
 # 理由 :
 # EXISTSは行の存在のみをチェックするため、具体的な列は不要

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

 # NOT INとNOT EXISTSの比較
 
 # NOT IN (NULLがある場合に問題が発生)
 SELECT name FROM employees
    WHERE department_id NOT IN (SELECT manager_id FROM departments);
 
 # NOT EXISTS (NULLの影響を受けない)
 SELECT name FROM employees e
    WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.department_id);

ANY / SOME / ALL

ANY 演算子 (別名: SOME) は、サブクエリの結果のいずれか1つと比較が TRUE なら全体が TRUE になる。
ALL 演算子は、サブクエリの結果のすべてと比較が TRUE なら全体が TRUE になる。

基本構文は以下の通りである。

 # 基本構文
 expression comparison_operator ANY (subquery)
 expression comparison_operator SOME (subquery)
 expression comparison_operator ALL (subquery)
 
 # 使用可能な比較演算子: =, <>, <, >, <=, >=

ANY と SOME の使用例を以下に示す。

 # ANYを使用 (いずれか1つより大きい)
 SELECT name, salary FROM employees
    WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);
 # 部署10の給与のいずれか1つより大きければTRUE (最小値より大きい)
 
 # = ANYは、INと同じ意味
 SELECT name FROM employees
    WHERE department_id = ANY (SELECT id FROM departments WHERE location = 'Tokyo');
 # これは以下と同じ
 SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # < ANYを使用
 SELECT product_name FROM products
    WHERE price < ANY (SELECT price FROM products WHERE category_id = 5);
 # カテゴリ5の商品のいずれか1つより安い (最高値より安い)
 
 # SOMEはANYのエイリアス (同じ意味)
 SELECT name FROM employees
    WHERE salary > SOME (SELECT salary FROM employees WHERE department_id = 10);

ALL の使用例を以下に示す。

 # ALLを使用 (すべてより大きい)
 SELECT name, salary FROM employees
    WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);
 # 部署10の給与のすべてより大きければTRUE (最大値より大きい)
 
 # <> ALLは、NOT INと同じ意味
 SELECT name FROM employees
    WHERE department_id <> ALL (SELECT id FROM departments WHERE location = 'Tokyo');
 # これは以下と同じ
 SELECT name FROM employees
    WHERE department_id NOT IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # < ALLを使用
 SELECT product_name FROM products
    WHERE price < ALL (SELECT price FROM products WHERE category_id = 5);
 # カテゴリ5の商品のすべてより安い (最小値より安い)
 
 # >= ALLを使用 (最大値を取得)
 SELECT name, salary FROM employees
    WHERE salary >= ALL (SELECT salary FROM employees);
 # 全ての給与以上 = 最大給与

※重要な区別
NOT IN は <> ANY ではなく、<> ALL と等価である。

 # NOT INと等価な表現
 WHERE column NOT IN (subquery)
 # これは以下と同じ
 WHERE column <> ALL (subquery)
 
 # <> ANYは、「いずれか1つと異なる」= 「全てと同じではない」
 WHERE column <> ANY (subquery)
 # これは、NOT INとは異なる意味

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

サブクエリと比較演算子を組み合わせることで、柔軟な条件指定が可能である。

 # 平均より高い給与
 SELECT name, salary FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);
 
 # 最大値と等しい
 SELECT product_name, price FROM products
    WHERE price = (SELECT MAX(price) FROM products);
 
 # 最小値より大きい
 SELECT product_name, price FROM products
    WHERE price > (SELECT MIN(price) FROM products);
 
 # 中央値より低い (MySQL 8.0以降)
 SELECT name, salary FROM employees
    WHERE salary < (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees);
 
 # 特定の条件を満たす値と比較
 SELECT name FROM employees
    WHERE hire_date > (SELECT MAX(hire_date) FROM employees WHERE department_id = 10);

HAVING句での副問い合わせ

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

 # 平均注文数より多い注文を持つユーザ
 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 t);
 
 # 平均給与より高い部署の平均給与
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
 
 # 最も注文の多いユーザの注文数と同じユーザを取得
 SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM orders GROUP BY user_id) AS t);
 
 # 特定の条件を満たす集約結果
 SELECT category_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY category_id
    HAVING SUM(amount) > (SELECT SUM(amount) / 10 FROM order_items);

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 avg_dept);
 
 # SUMとの組み合わせ
 SELECT user_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > (SELECT AVG(total) FROM (SELECT SUM(amount) AS total FROM orders GROUP BY user_id) AS t);

INSERT / UPDATE / DELETE文での副問い合わせ

INSERT ... SELECT構文

INSERT ... SELECT 構文を使用すると、他のテーブルからデータをコピーして挿入できる。

 # 基本的なINSERT ... SELECT
 INSERT INTO archive_orders (order_id, user_id, ordered_at)
    SELECT id, user_id, ordered_at FROM orders WHERE ordered_at < '2024-01-01';
 
 # 全列をコピー
 INSERT INTO backup_employees
    SELECT * FROM employees WHERE department_id = 10;
 
 # 集約結果を挿入
 INSERT INTO department_stats (department_id, avg_salary, employee_count)
    SELECT department_id, AVG(salary), COUNT(*) FROM employees GROUP BY department_id;
 
 # 条件付きでコピー
 INSERT INTO high_value_customers (user_id, total_amount)
    SELECT user_id, SUM(amount) FROM orders
       GROUP BY user_id
       HAVING SUM(amount) > 100000;

INSERT ... SELECT では、列数と型が一致する必要がある。

 # 列の順序を指定
 INSERT INTO target_table (col1, col2, col3)
    SELECT source_col1, source_col2, source_col3 FROM source_table;
 
 # 一部の列のみを挿入
 INSERT INTO users (name, email)
    SELECT full_name, email_address FROM temp_users;

UPDATE文での副問い合わせ

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

 # SET句でサブクエリを使用
 UPDATE employees
    SET salary = (SELECT AVG(salary) FROM (SELECT salary FROM employees) AS t)
    WHERE department_id = 10;
 
 # WHERE句でサブクエリを使用
 UPDATE products
    SET status = 'discontinued'
    WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);
 
 # 複数列を更新
 UPDATE employees e
    SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id),
        updated_at = NOW()
    WHERE hire_date < '2020-01-01';
 
 # EXISTSを使用した更新
 UPDATE users
    SET status = 'active'
    WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.ordered_at > '2024-01-01');

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

DELETE文での副問い合わせ

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

 # INを使用した削除
 DELETE FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'cancelled');
 
 # NOT INを使用した削除
 DELETE FROM products
    WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items WHERE order_items.product_id IS NOT NULL);
 
 # EXISTSを使用した削除
 DELETE FROM logs
    WHERE EXISTS (SELECT 1 FROM archived_logs WHERE archived_logs.log_id = logs.id);
 
 # サブクエリで条件を指定
 DELETE FROM employees
    WHERE salary < (SELECT AVG(salary) FROM (SELECT salary FROM employees) AS t);

詳細は DELETE文 のページを参照されたい。

ラテラル派生テーブル (LATERAL)

基本構文

LATERAL キーワードは、MySQL 8.0.14以降で使用可能である。

LATERAL を使用すると、派生テーブルが前に定義されたテーブルの列を参照できるようになる。
これは、相関サブクエリ (相関副問い合わせを参照) の FROM 句版といえる。

 # LATERALの基本構文
 SELECT ... FROM t1, LATERAL (SELECT ... FROM t2 WHERE t2.col = t1.col) AS dt;
 
 # 使用例: 各部署の上位3名の従業員を取得
 SELECT d.department_name, e.name, e.salary
    FROM departments d,
    LATERAL (
       SELECT name, salary FROM employees
          WHERE employees.department_id = d.id
          ORDER BY salary DESC
          LIMIT 3
    ) AS e;
 
 # 各ユーザの最新3件の注文を取得
 SELECT u.name, o.order_id, o.ordered_at
    FROM users u,
    LATERAL (
       SELECT id AS order_id, ordered_at FROM orders
          WHERE orders.user_id = u.id
          ORDER BY ordered_at DESC
          LIMIT 3
    ) AS o;
 
 # 各カテゴリの最高価格商品を取得
 SELECT c.category_name, p.product_name, p.price
    FROM categories c,
    LATERAL (
       SELECT product_name, price FROM products
          WHERE products.category_id = c.id
          ORDER BY price DESC
          LIMIT 1
    ) AS p;

従来の派生テーブルとの比較

従来の派生テーブルは、前に定義されたテーブルの列を参照できない。

 # エラーになる例 (LATERALなし)
 SELECT d.department_name, e.name
    FROM departments d,
    (SELECT name FROM employees WHERE employees.department_id = d.id) AS e;
 # エラー: Unknown column 'd.id' in 'where clause'
 
 # LATERALを使用すると参照可能
 SELECT d.department_name, e.name
    FROM departments d,
    LATERAL (SELECT name FROM employees WHERE employees.department_id = d.id) AS e;

LATERAL は、外部クエリの各行に対して異なる結果セットを返すことができる。

 # 各ユーザに対して異なる注文セットを返す
 SELECT u.name, o.order_count, o.total_amount
    FROM users u,
    LATERAL (
       SELECT COUNT(*) AS order_count, SUM(amount) AS total_amount
          FROM orders
          WHERE orders.user_id = u.id
    ) AS o
    WHERE o.order_count > 0;
 
 # 各部署の平均給与を超える従業員を取得
 SELECT d.department_name, e.name, e.salary
    FROM departments d,
    LATERAL (
       SELECT name, salary FROM employees
          WHERE employees.department_id = d.id
             AND salary > (SELECT AVG(salary) FROM employees WHERE department_id = d.id)
    ) AS e;

LATERAL は、LEFT JOIN と組み合わせることもできる。

 # LEFT JOIN LATERALを使用
 SELECT u.name, o.last_order_date
    FROM users u
    LEFT JOIN LATERAL (
       SELECT MAX(ordered_at) AS last_order_date FROM orders WHERE orders.user_id = u.id
    ) AS o ON TRUE;
 # 注文のないユーザも含めて、最新注文日を取得

副問い合わせの制限事項と注意点

同一テーブルの変更と参照の制限

UPDATE や DELETE 文で、同じテーブルをサブクエリで参照しながら変更することはできない。

エラー: ERROR 1093 (HY000): You can't specify target table for update in FROM clause

 # エラーになる例
 DELETE FROM users
    WHERE id IN (SELECT id FROM users WHERE status = 'deleted');
 
 # UPDATE文でもエラー
 UPDATE employees
    SET salary = (SELECT AVG(salary) FROM employees)
    WHERE department_id = 10;

回避策として、派生テーブルで1段包む方法がある。

 # 派生テーブルで1段包む (回避策)
 DELETE FROM users
    WHERE id IN (
       SELECT id FROM (
          SELECT id FROM users WHERE status = 'deleted'
       ) AS tmp
    );
 
 # UPDATE文での回避策
 UPDATE employees
    SET salary = (SELECT avg_sal FROM (SELECT AVG(salary) AS avg_sal FROM employees) AS tmp)
    WHERE department_id = 10;

この回避策は、オプティマイザーがサブクエリを一時テーブルとして実体化することで機能する。

その他の制限事項

副問い合わせには、以下に示すいくつかの制限がある。

• サブクエリの外側のクエリからの列参照はサブクエリ内でのみ有効

  サブクエリ内で外部クエリの列を参照できるが、逆は不可。

• ORDER BYはサブクエリ内で無視される場合がある

  オプティマイザーが不要と判断した場合、ORDER BYが無視される。LIMIT句と組み合わせると有効。

• LIMIT句はサブクエリ内で使用可能

  サブクエリの結果行数を制限できる。

• サブクエリの結果が大きすぎるとメモリ不足になる可能性がある

  大量のデータを返すサブクエリは避けるべき。

 # ORDER BYが無視される例
 SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) AS e;
 # ORDER BYは無視される (LIMIT句がないため)
 
 # LIMIT句と組み合わせると有効
 SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC LIMIT 10) AS e;
 
 # サブクエリの外側から内側の列を直接参照はできない
 SELECT name,
    (SELECT department_name FROM departments WHERE id = department_id) AS dept
    FROM employees;
 # department_idはemployeesテーブルの列として解釈される

副問い合わせとJOINの比較

副問い合わせと JOIN は、多くの場合で同じ結果を得ることができる。

ただし、パフォーマンスや機能に違いがあるため、使い分けが重要である。

副問い合わせとJOINの比較

項目	副問い合わせ	JOIN
パフォーマンス	相関サブクエリは遅い場合がある	一般的に高速
可読性	直感的でわかりやすい場合がある	複雑になる場合がある
列の選択	サブクエリの列は直接使用できない	結合後の全列を選択可能
重複行	INやEXISTSは重複を気にしない	INNER JOINは重複を生成する場合がある
NULL処理	NOT INはNULLで問題が発生	JOINはNULLを適切に処理
最適化	MySQL 8.0で大幅に改善	長年最適化されている

等価な書き換え例を以下に示す。

 # INをINNER JOINに書き換え
 
 # サブクエリ
 SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # INNER JOINに書き換え
 SELECT DISTINCT e.name FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
    WHERE d.location = 'Tokyo';
 
 # NOT INをLEFT JOINに書き換え
 
 # サブクエリ
 SELECT name FROM users
    WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
 
 # LEFT JOINに書き換え
 SELECT u.name FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.user_id IS NULL;
 
 # EXISTSをINNER JOINに書き換え (一部ケース)

 # サブクエリ
 SELECT name FROM employees e
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
 
 # INNER JOINに書き換え
 SELECT DISTINCT e.name FROM employees e
    INNER JOIN orders o ON o.employee_id = e.id;

使い分けの指針を以下に示す。

• 可読性を優先する場合

  サブクエリの方がわかりやすい場合がある。

• パフォーマンスが重要な場合

  JOINの方が高速なことが多い。

• 結合テーブルの列が必要な場合

  JOINを使用する。

• 存在チェックのみが必要な場合

  EXISTSが適している。

• NULLを含む可能性がある場合

  NOT INを避け、NOT EXISTSまたはLEFT JOINを使用する。

パフォーマンス最適化

オプティマイザーの副問い合わせ最適化

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

主な最適化技術として、セミジョイン最適化とマテリアライゼーション (実体化) がある。

セミジョイン最適化

IN や EXISTS サブクエリを、内部的にセミジョインに変換する。

 # このサブクエリは
 SELECT * FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # 内部的にセミジョインに変換される
 # 実行戦略: Table Pullout, Duplicate Weedout, FirstMatch, LooseScan, Materialization

マテリアライゼーション (実体化)

サブクエリの結果をインデックス付き一時テーブルとして実体化する。

 # サブクエリの結果が実体化される
 SELECT * FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE budget > 100000);
 
 # 内部的には以下のように処理される
 # 1. サブクエリを実行してインデックス付き一時テーブルを作成
 # 2. 外部クエリが一時テーブルをルックアップ

マテリアライゼーションは、サブクエリを1回だけ実行するため、相関サブクエリの行ごと実行を回避できる。

オプティマイザーの動作は、optimizer_switch システム変数で制御できる。

 # 現在の設定を確認
 SELECT @@optimizer_switch;
 
 # materializationの有効/無効
 SET optimizer_switch='materialization=on';
 SET optimizer_switch='materialization=off';
 
 # semijoinの有効/無効
 SET optimizer_switch='semijoin=on';
 SET optimizer_switch='semijoin=off';

EXISTS vs INの使い分け

EXISTS と IN は、多くの場合で同じ結果を得られるが、パフォーマンス特性が異なる。

 # EXISTSを使用
 SELECT name FROM users u
    WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
 # INを使用
 SELECT name FROM users
    WHERE id IN (SELECT user_id FROM orders);

使い分けの指針を以下に示す。

• 外部テーブルが小さい場合

  IN が有効。サブクエリの結果を1回だけ実体化できる。

• 内部テーブルが小さい場合

  EXISTS が有効。各行のチェックが高速。

• NULLが含まれる可能性がある場合

  EXISTS を推奨。NOT IN はNULLで問題が発生する。

• インデックスの利用

  EXISTS は結合列にインデックスがあると高速。IN は実体化時にインデックスが自動作成される。

サブクエリの書き換え

パフォーマンス改善のため、サブクエリを JOIN に書き換えることが有効な場合がある。
特に相関サブクエリの書き換えについては、相関副問い合わせのページも参照すること。

 # 相関サブクエリ (遅い可能性)
 SELECT name,
    (SELECT department_name FROM departments d WHERE d.id = e.department_id) AS dept_name
    FROM employees e;
 
 # JOINに書き換え (高速)
 SELECT e.name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 
 # 相関サブクエリでの集計 (遅い可能性)
 SELECT name, salary FROM employees e1
    WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);
 
 # JOINに書き換え (高速)
 SELECT e.name, e.salary
    FROM employees e
    JOIN (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS dept_avg
       ON e.department_id = dept_avg.department_id
    WHERE e.salary > dept_avg.avg_sal;

実行計画の確認には、EXPLAIN 文を使用する。

 # 実行計画を確認
 EXPLAIN SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');
 
 # MySQL 8.0.18以降では、EXPLAIN ANALYZEで実際の実行時間も確認可能
 EXPLAIN ANALYZE SELECT name FROM employees
    WHERE department_id IN (SELECT id FROM departments WHERE location = 'Tokyo');

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

• SUBQUERY

  非相関サブクエリ

• DEPENDENT SUBQUERY

  相関サブクエリ

• DERIVED

  FROM句の派生テーブル

• MATERIALIZED

  実体化されたサブクエリ

 # select_typeを確認
 EXPLAIN SELECT * FROM (SELECT * FROM employees WHERE salary > 50000) AS e;
 # select_type: DERIVED
 
 EXPLAIN SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments);
 # select_type: SIMPLE (セミジョイン最適化により)
 
 EXPLAIN SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
 # select_type: SIMPLE (セミジョイン最適化により)