SQLサブクエリの使い方｜相関サブクエリ・EXISTS・JOIN書き換え

サブクエリ（副問い合わせ）とは、SQL文の中にかっこで囲んで書く、もう1つのSELECT文です。 「平均より高い行だけ」「別の表に存在する行だけ」のように、一度調べた結果を使ってさらに絞り込みたいときに使います。書ける場所は WHERE 句・FROM 句・SELECT 句などで、返す形（1つの値／1列／表）によって使い方が決まります。

この記事では、同じサンプル表を使って5つのパターン（スカラー・IN・EXISTS・FROM句の派生表・相関サブクエリ）をすべて実行結果つきで並べます。さらに、相関サブクエリが1行ずつ評価される流れの図解、20万行の表でJOINやウィンドウ関数に書き換えたときの EXPLAIN ANALYZE の比較、「Subquery returns more than 1 row」などのエラーの再現と直し方までを扱います。検証は MySQL 8.0.46 と PostgreSQL 16.15 で行い、出力はそのまま載せています。

MySQL 8.0 は2026年4月に公式サポートが終了しました。この記事のSQLは標準的な構文だけを使っているので、MySQL 8.4／9.7でも同じ手順で使えます。版によって変わる点（オプティマイザの既定値など）は、該当箇所で明記します。

サブクエリの早見表

検証に使うサンプル表（コピペ用）

この記事のSQLは、すべて次の2つの表で動かします。MySQL と PostgreSQL のどちらにもそのまま貼れます。

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(20) NOT NULL
);
CREATE TABLE employees (
  emp_id   INT PRIMARY KEY,
  emp_name VARCHAR(20) NOT NULL,
  dept_id  INT,              -- NULL を許可（配属前の人を表す）
  salary   INT NOT NULL
);

INSERT INTO departments VALUES (10,'開発'),(20,'営業'),(30,'総務'),(40,'企画');
INSERT INTO employees VALUES
(1,'青木',10,420000),(2,'井上',10,360000),(3,'上田',10,300000),
(4,'遠藤',20,380000),(5,'小川',20,280000),
(6,'加藤',30,330000),(7,'木村',NULL,310000);

データのポイントは2つあります。「企画」には社員が1人もいないこと、そして木村さんの dept_id が NULLであることです。この2つが、EXISTS や NOT IN の動きを確かめるときに効いてきます。

パターン1：スカラーサブクエリで「全体の平均より高い行」を取り出す

スカラーサブクエリは、1行1列、つまり値を1つだけ返すサブクエリです。 数値や文字列を書ける場所なら、どこにでも置けます。

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY emp_id;

実行結果（MySQL 8.0.46。PostgreSQL 16.15 でも同じ3行）：

+----------+--------+
| emp_name | salary |
+----------+--------+
| 青木     | 420000 |
| 井上     | 360000 |
| 遠藤     | 380000 |
+----------+--------+

かっこの中の SELECT AVG(salary) FROM employees は 340000 を返します。データベースはまずこの値を求め、外側は WHERE salary > 340000 と同じ意味になります。サブクエリを単独で実行して、結果を目で確かめられるのが、このパターンの読みやすさです。

SELECT 句にも書けます。SELECT emp_name, salary - (SELECT AVG(salary) FROM employees) AS diff FROM employees とすると、青木 80000.0000、小川 -60000.0000 のように、全体平均との差が各行に並びました。

スカラーサブクエリが0行を返したときは NULL として扱われます。たとえば WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = '存在しない') は、エラーにならず0件を返しました（両DBで確認）。= NULL はどの行にも当てはまらないためです。

パターン2：INサブクエリで「別の表の条件に合う行」を取り出す

IN サブクエリは、1列を複数行返すサブクエリを「値のリスト」として使います。

SELECT emp_name, dept_id
FROM employees
WHERE dept_id IN (SELECT dept_id
                  FROM departments
                  WHERE dept_name IN ('開発','総務'))
ORDER BY emp_id;

+----------+---------+
| emp_name | dept_id |
+----------+---------+
| 青木     |      10 |
| 井上     |      10 |
| 上田     |      10 |
| 加藤     |      30 |
+----------+---------+

内側が (10, 30) を返し、外側は WHERE dept_id IN (10, 30) と同じ意味になります。

複数の列をまとめて比べる書き方（行値式）もあります。「部署ごとの最高額の人」を取り出す例で、MySQL・PostgreSQL の両方で動きました。

SELECT emp_name
FROM employees
WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary)
                            FROM employees
                            GROUP BY dept_id)
ORDER BY emp_id;

+----------+
| emp_name |
+----------+
| 青木     |
| 遠藤     |
| 加藤     |
+----------+

木村さん（dept_id が NULL）は結果に入りません。(NULL, 310000) 同士の比較は真にならないためです。

パターン3：EXISTSサブクエリで「関連する行があるか」を調べる

EXISTS は、サブクエリが1行でも返せば真、0行なら偽になります。 返す値そのものは使わないので、SELECT 1 と書くのが定番です。

-- 社員が1人以上いる部署
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)
ORDER BY d.dept_id;

+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      10 | 開発      |
|      20 | 営業      |
|      30 | 総務      |
+---------+-----------+

NOT EXISTS にすると、反対に「社員がいない部署」が取れます。

SELECT d.dept_id, d.dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)
ORDER BY d.dept_id;

+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      40 | 企画      |
+---------+-----------+

サブクエリの中で外側の d.dept_id を使っている点に注目してください。EXISTS はほとんどの場合、あとで説明する「相関サブクエリ」の形で書きます。 IN と EXISTS の細かな使い分けや件数別の実測は、当サイトの「SQLのEXISTSとINの違い」の記事で扱っているので、ここでは基本の形に絞ります。

パターン4：FROM句のサブクエリ（派生表）で集計結果を結合する

FROM 句に書いたサブクエリは、その場かぎりの表として扱えます。 これを派生表（derived table）またはインラインビューと呼びます。

SELECT d.dept_name, t.cnt, t.avg_salary
FROM (SELECT dept_id,
             COUNT(*)    AS cnt,
             AVG(salary) AS avg_salary
      FROM employees
      GROUP BY dept_id) AS t
JOIN departments d ON d.dept_id = t.dept_id
ORDER BY d.dept_id;

+-----------+-----+-------------+
| dept_name | cnt | avg_salary  |
+-----------+-----+-------------+
| 開発      |   3 | 360000.0000 |
| 営業      |   2 | 330000.0000 |
| 総務      |   1 | 330000.0000 |
+-----------+-----+-------------+

「先に集計してから結合する」という順番を、そのまま書けるのが派生表の良さです。先に結合してから集計すると行が増えて数え間違いやすい場面で、とくに役立ちます。

派生表には別名（ここでは AS t）を付けます。別名については DB で次の違いがありました。

どちらでも動くSQLにしたいときは、常に別名を付けると決めておくと迷いません。派生表が長くなってきたら、WITH 句（CTE）で名前を付けて上に出すと読みやすくなります。書き方は「SQL WITH句の使い方」の記事を参照してください。

パターン5：相関サブクエリで「グループごとの平均より高い行」を取り出す

相関サブクエリとは、内側のSELECTが外側の行の値を参照するサブクエリです。 外側の行が変わるたびに、内側の条件も変わります。

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary)
                  FROM employees e2
                  WHERE e2.dept_id = e.dept_id)   -- 外側の e を参照
ORDER BY e.emp_id;

+----------+---------+--------+
| emp_name | dept_id | salary |
+----------+---------+--------+
| 青木     |      10 | 420000 |
| 遠藤     |      20 | 380000 |
+----------+---------+--------+

パターン1は「全体の平均」との比較でしたが、こちらは「自分の部署の平均」との比較です。同じ表を内側と外側で使うので、別名（e と e2）で区別するのが書き方のコツです。

相関サブクエリが1行ずつ評価される流れ

相関サブクエリは、意味の上では「外側の1行を取り出す → その行の値で内側を実行する → 結果で判定する」を行数ぶん繰り返します。

flowchart TD
    A["外側 employees e から1行取り出す"] --> B["その行の dept_id を内側に渡す"]
    B --> C["内側を実行<br>SELECT AVG(salary) FROM employees e2<br>WHERE e2.dept_id = 渡された値"]
    C --> D{"e.salary > 内側の結果 ?"}
    D -- 真 --> E["結果に入れる"]
    D -- 偽または不明 --> F["結果に入れない"]
    E --> G{"次の行がある?"}
    F --> G
    G -- ある --> A
    G -- ない --> H["終了"]

内側の結果を SELECT 句に出すと、この流れを表として確かめられます。

SELECT e.emp_name, e.dept_id, e.salary,
       (SELECT AVG(e2.salary) FROM employees e2
        WHERE e2.dept_id = e.dept_id) AS dept_avg
FROM employees e
ORDER BY e.emp_id;

+----------+---------+--------+-------------+
| emp_name | dept_id | salary | dept_avg    |
+----------+---------+--------+-------------+
| 青木     |      10 | 420000 | 360000.0000 |
| 井上     |      10 | 360000 | 360000.0000 |
| 上田     |      10 | 300000 | 360000.0000 |
| 遠藤     |      20 | 380000 | 330000.0000 |
| 小川     |      20 | 280000 | 330000.0000 |
| 加藤     |      30 | 330000 | 330000.0000 |
| 木村     |    NULL | 310000 |        NULL |
+----------+---------+--------+-------------+

この出力を、1行ずつの評価として読むと次のようになります。

7行目がポイントです。e2.dept_id = NULL に当てはまる行はないので、内側は0行を集計して NULL を返し、310000 > NULL は不明（UNKNOWN）になります。WHERE は真の行だけを通すので、木村さんは結果に入りません。

実行計画でも「外側の行に依存する」ことが表示されます。MySQL 8.0.46 の EXPLAIN では、内側が DEPENDENT SUBQUERY と表示されました。

+----+--------------------+-------+------+------+----------+-------------+
| id | select_type        | table | type | rows | filtered | Extra       |
+----+--------------------+-------+------+------+----------+-------------+
|  1 | PRIMARY            | e     | ALL  |    7 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | e2    | ALL  |    7 |    14.29 | Using where |
+----+--------------------+-------+------+------+----------+-------------+

（表示幅の都合で partitions・possible_keys・key・key_len・ref の列を省いています。いずれも NULL でした。）

PostgreSQL 16.15 では SubPlan と表示され、フィルター条件に外側の列 e.dept_id が現れます。

 Seq Scan on employees e  (cost=0.00..16952.38 rows=277 width=58)
   Filter: ((salary)::numeric > (SubPlan 1))
   SubPlan 1
     ->  Aggregate  (cost=20.39..20.40 rows=1 width=32)
           ->  Seq Scan on employees e2  (cost=0.00..20.38 rows=4 width=4)
                 Filter: (dept_id = e.dept_id)

SELECT句に書く相関サブクエリ

相関サブクエリは SELECT 句にも書けます。「部署ごとの人数」を、社員がいない部署も含めて出す例です。

SELECT d.dept_name,
       (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) AS cnt
FROM departments d
ORDER BY d.dept_id;

+-----------+------+
| dept_name | cnt  |
+-----------+------+
| 開発      |    3 |
| 営業      |    2 |
| 総務      |    1 |
| 企画      |    0 |
+-----------+------+

COUNT(*) は0行でも 0 を返すので、企画が 0 として残ります。名前を引くだけの用途（(SELECT dept_name FROM departments d WHERE d.dept_id = e.dept_id)）にも使え、該当がない木村さんの行は NULL になりました。

グループごとの最大の行を取り出す

「部署ごとに最高額の人」も、相関サブクエリの定番です。

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(e2.salary)
                  FROM employees e2
                  WHERE e2.dept_id = e.dept_id)
ORDER BY e.dept_id;

+----------+---------+--------+
| emp_name | dept_id | salary |
+----------+---------+--------+
| 青木     |      10 | 420000 |
| 遠藤     |      20 | 380000 |
| 加藤     |      30 | 330000 |
+----------+---------+--------+

同じ額の人が複数いれば、全員が返ります。「必ず1人だけ」「上位2人」としたいときは、後半で紹介する ROW_NUMBER() や LATERAL が向いています。

NOT INにNULLが混ざると0件になる（再現）

「社員がいない部署」を NOT IN で書くと、このサンプルでは意外な結果になります。

SELECT dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);

PostgreSQL 16.15 の結果（MySQL 8.0.46 でも同じく0件）：

 dept_name
-----------
(0 rows)

企画（40）が返るはずが、0件です。サブクエリの結果は (10, 10, 10, 20, 20, 30, NULL) で、木村さんの NULL が混ざっています。40 NOT IN (10, 20, 30, NULL) は 40 <> 10 AND 40 <> 20 AND 40 <> 30 AND 40 <> NULL と同じ意味で、最後の 40 <> NULL が不明になるため、全体も真になりません。

直し方は2通りで、どちらも企画の1行が返りました。

-- 直し方A：NOT EXISTS に書き換える（NULL の影響を受けない）
SELECT dept_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

-- 直し方B：サブクエリ側で NULL を除く
SELECT dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

+-----------+
| dept_name |
+-----------+
| 企画      |
+-----------+

「〜でない行」を探すときは NOT EXISTS を第一候補にすると覚えておくと安心です。なぜそうなるかの詳しい説明と実行計画の違いは「SQLのEXISTSとINの違い」「SQLのNULLの扱い」の記事にまとめています。

「Subquery returns more than 1 row」などのエラーを再現して直す

サブクエリで出会いやすいエラーを、実際に出して確かめます。

「=」の右側が2行以上返した

SELECT emp_name
FROM employees
WHERE dept_id = (SELECT dept_id
                 FROM departments
                 WHERE dept_name IN ('開発','総務'));

-- MySQL 8.0.46
ERROR 1242 (21000) at line 43: Subquery returns more than 1 row

-- PostgreSQL 16.15
ERROR:  more than one row returned by a subquery used as an expression

= は値1つとしか比べられないのに、サブクエリが 10 と 30 の2行を返したためです。このエラーは、構文ではなくデータによって起きます。 テスト用の少ないデータでは1行しか返らずに通り、データが増えてから出ることがあるので、書く時点で「必ず1行になるか」を考えておくのがコツです。

直し方は、意図によって3通りあります。

3つとも両DBで実行でき、IN 版は青木・井上・上田・加藤の4行、MIN 版と LIMIT 1 版は開発の3行（青木・井上・上田）を返しました。そもそも1行に決まるはずの条件なら、主キーや UNIQUE 制約の付いた列で絞ると、データが増えても1行であることが保証されます。

サブクエリが2列以上返した

SELECT emp_name FROM employees
WHERE dept_id = (SELECT dept_id, dept_name FROM departments WHERE dept_name = '開発');

-- MySQL 8.0.46
ERROR 1241 (21000) at line 44: Operand should contain 1 column(s)

-- PostgreSQL 16.15
ERROR:  subquery must return only one column

IN の右側で2列返した場合も、MySQL は同じ ERROR 1241、PostgreSQL は ERROR: subquery has too many columns でした。比べる相手が1列なら、サブクエリの SELECT も1列にします。SELECT * をサブクエリに書いたときに起きやすいエラーです。

列名の書き間違いが、エラーにならず相関サブクエリになる

次のSQLは、departments に存在しない emp_id を内側で SELECT しています。エラーになりそうですが、両DBとも実行できました。

SELECT emp_name
FROM employees
WHERE emp_id IN (SELECT emp_id FROM departments);

+----------+
| emp_name |
+----------+
| 青木     |
| 井上     |
| 上田     |
| 遠藤     |
| 小川     |
| 加藤     |
| 木村     |
+----------+

全7行が返っています。 内側の表に emp_id がないため、データベースは外側の employees.emp_id を探しに行き、相関サブクエリとして解釈します。すると条件は「自分の emp_id が自分の emp_id に等しいか」になり、常に真です。これが DELETE や UPDATE の WHERE に入ると、対象が全行になります。

防ぎ方は1つで、サブクエリの中の列には必ず表の別名を付けることです。

-- 別名を付けると、書き間違いがエラーとして見つかる
SELECT e.emp_name
FROM employees e
WHERE e.emp_id IN (SELECT d.emp_id FROM departments d);
-- MySQL:      ERROR 1054 (42S22): Unknown column 'd.emp_id' in 'field list'
-- PostgreSQL: ERROR:  column d.emp_id does not exist

MySQLで更新対象の表をサブクエリで参照した

DELETE FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees);

-- MySQL 8.0.46
ERROR 1093 (HY000) at line 10: You can't specify target table 'employees' for update in FROM clause

MySQL は、UPDATE／DELETE の対象と同じ表をサブクエリで直接読むことを受け付けません。PostgreSQL 16.15 では同じ形の文がそのまま実行できました。MySQL での書き方（派生表でもう一段包む、JOIN を使う）は「SQLで別テーブルの値を使ってUPDATEする方法」の記事で扱っています。別の表を参照するだけなら、MySQL でも次のように問題なく書けます。

UPDATE employees
SET salary = salary + 10000
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = '総務');
-- 加藤さんの salary が 330000 → 340000 になった（両DBで確認）

サブクエリをJOINに書き換える

サブクエリの多くは JOIN でも書けます。ただし結果の行数が変わる場合があるので、対応を表で押さえておきます。

EXISTSをJOINにすると行が増える

「社員がいる部署」を単純に JOIN で書くと、社員の人数ぶん行が出ます。

SELECT d.dept_name
FROM departments d
JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

+-----------+
| dept_name |
+-----------+
| 開発      |
| 開発      |
| 開発      |
| 営業      |
| 営業      |
| 総務      |
+-----------+

EXISTS 版は3行でしたが、JOIN 版は6行です。SELECT DISTINCT d.dept_name にすると3行に戻ります。「あるかどうか」だけ知りたいなら EXISTS のまま、相手の表の列も表示したいなら JOIN、と使い分けると自然です。

NOT EXISTSをLEFT JOINにする

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

+-----------+
| dept_name |
+-----------+
| 企画      |
+-----------+

結合相手が見つからなかった行は、e 側の列がすべて NULL になります。そこを e.emp_id IS NULL（主キー）で拾う書き方です。

相関サブクエリを「集計した派生表とのJOIN」にする

パターン5の「部署平均より高い人」は、部署ごとの平均を先に1回だけ求めて結合する形に書き換えられます。

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS dept_avg
      FROM employees
      GROUP BY dept_id) a ON a.dept_id = e.dept_id
WHERE e.salary > a.dept_avg
ORDER BY e.emp_id;

+----------+---------+--------+
| emp_name | dept_id | salary |
+----------+---------+--------+
| 青木     |      10 | 420000 |
| 遠藤     |      20 | 380000 |
+----------+---------+--------+

相関サブクエリ版と同じ2行です。平均の計算が「行ごと」から「部署ごとに1回」に変わるので、データが多いほど差が出ます（次の章で実測します）。

サブクエリをウィンドウ関数に書き換える

ウィンドウ関数を使うと、同じ表を2回書かずに「グループの平均」を各行に並べられます。

SELECT emp_name, dept_id, salary
FROM (SELECT emp_id, emp_name, dept_id, salary,
             AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
      FROM employees) t
WHERE salary > dept_avg
  AND dept_id IS NOT NULL
ORDER BY emp_id;

+----------+---------+--------+
| emp_name | dept_id | salary |
+----------+---------+--------+
| 青木     |      10 | 420000 |
| 遠藤     |      20 | 380000 |
+----------+---------+--------+

ウィンドウ関数の結果は WHERE で直接は絞れないので、派生表で一段包んでから外側で絞ります。ここで1つ違いがあります。PARTITION BY dept_id は NULL 同士を1つのグループにまとめるので、木村さんの行は「NULL グループの平均 310000」と比べられます。今回は 310000 > 310000 が偽なのでどちらでも結果は同じですが、NULL グループに複数人いると結果に入る人が出てきます。相関サブクエリと同じ結果にそろえるために、dept_id IS NOT NULL を足しています。

「部署ごとの1位」は ROW_NUMBER() で書けます。

SELECT emp_name, dept_id, salary
FROM (SELECT emp_name, dept_id, salary,
             ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
      FROM employees
      WHERE dept_id IS NOT NULL) t
WHERE rn = 1
ORDER BY dept_id;

+----------+---------+--------+
| emp_name | dept_id | salary |
+----------+---------+--------+
| 青木     |      10 | 420000 |
| 遠藤     |      20 | 380000 |
| 加藤     |      30 | 330000 |
+----------+---------+--------+

rn <= 2 にすれば上位2人になります。累計や前の行との比較など、ウィンドウ関数そのものの使い方は「SQLウィンドウ関数の使い方」の記事で詳しく扱っています。

LATERALで「グループごとの上位N件」を書く

FROM 句のサブクエリは、通常は外側の表を参照できません。LATERAL を付けると参照できるようになり、「FROM 句に書く相関サブクエリ」として使えます。MySQL は 8.0.14 以降、PostgreSQL は 9.3 以降で使えます。

SELECT d.dept_name, t.emp_name, t.salary
FROM departments d
JOIN LATERAL (SELECT e.emp_name, e.salary
              FROM employees e
              WHERE e.dept_id = d.dept_id
              ORDER BY e.salary DESC
              LIMIT 2) t ON TRUE
ORDER BY d.dept_id, t.salary DESC;

+-----------+----------+--------+
| dept_name | emp_name | salary |
+-----------+----------+--------+
| 開発      | 青木     | 420000 |
| 開発      | 井上     | 360000 |
| 営業      | 遠藤     | 380000 |
| 営業      | 小川     | 280000 |
| 総務      | 加藤     | 330000 |
+-----------+----------+--------+

スカラーサブクエリでは1つの値しか返せませんが、LATERAL なら複数行・複数列をグループごとに取り出せます。

EXPLAIN ANALYZEで比べる：相関サブクエリ・JOIN・ウィンドウ関数

書き換えで速さがどう変わるかを、20万行（1,000部署×各200人）の表で測りました。

-- PostgreSQL
CREATE TABLE emp_big (emp_id INT PRIMARY KEY, dept_id INT NOT NULL, salary INT NOT NULL);
INSERT INTO emp_big
SELECT n, n % 1000 + 1, 200000 + (n::bigint * 7919) % 300000
FROM generate_series(1,200000) n;

-- MySQL
SET SESSION cte_max_recursion_depth = 300000;
CREATE TABLE emp_big (emp_id INT PRIMARY KEY, dept_id INT NOT NULL, salary INT NOT NULL);
INSERT INTO emp_big
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 200000)
SELECT n, n % 1000 + 1, 200000 + (n * 7919) % 300000 FROM s;

比べたのは「部署平均より高い人の数」を数える3つの書き方です。どれも結果は 99,836 件で一致しました。

-- (1) 相関サブクエリ
SELECT COUNT(*) FROM emp_big e
WHERE e.salary > (SELECT AVG(e2.salary) FROM emp_big e2 WHERE e2.dept_id = e.dept_id);

-- (2) 集計した派生表と JOIN
SELECT COUNT(*) FROM emp_big e
JOIN (SELECT dept_id, AVG(salary) AS dept_avg FROM emp_big GROUP BY dept_id) a
  ON a.dept_id = e.dept_id
WHERE e.salary > a.dept_avg;

-- (3) ウィンドウ関数
SELECT COUNT(*) FROM (SELECT salary, AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
                      FROM emp_big) t
WHERE salary > dept_avg;

実測結果の比較表

インデックスは CREATE INDEX idx_emp_big_dept ON emp_big (dept_id, salary); を作成しています。時間は EXPLAIN ANALYZE が表示した値で、1回の計測です。環境によって変わるので、桁の違いを見てください。

インデックスがない状態でも試しました。外側を5部署・1,000行に絞った WHERE e.dept_id <= 5 AND e.salary > (相関サブクエリ) で、MySQL は約99.7秒、PostgreSQL は約13.4秒でした。内側が1,000回、そのたびに20万行を全件読むためです。相関サブクエリを使うときは、内側の結合列（ここでは dept_id）にインデックスがあるかを最初に確かめるとよいことが分かります。

MySQL 8.0 の実行計画

相関サブクエリ（インデックスあり）では、dependent と loops=200000 が表示されました。

-> Aggregate: count(0)  (cost=40143 rows=1) (actual time=10849..10849 rows=1 loops=1)
    -> Filter: (e.salary > (select #2))  (cost=20124 rows=200196) (actual time=6.04..10841 rows=99836 loops=1)
        -> Covering index scan on e using idx_emp_big_dept  (cost=20124 rows=200196) (actual time=0.813..54.9 rows=200000 loops=1)
        -> Select #2 (subquery in condition; dependent)
            -> Aggregate: avg(e2.salary)  (cost=43.5 rows=1) (actual time=0.0528..0.0529 rows=1 loops=200000)
                -> Covering index lookup on e2 using idx_emp_big_dept (dept_id=e.dept_id)  (cost=22.4 rows=211) (actual time=0.022..0.0369 rows=200 loops=200000)

1回あたりは約0.05ミリ秒と十分に速いのですが、それが20万回積み重なって約10秒になっています。JOIN 版では、平均の集計が1回（Materialize ... loops=1）にまとまります。

-> Aggregate: count(0)  (cost=327708 rows=1) (actual time=107..107 rows=1 loops=1)
    -> Nested loop inner join  (cost=321035 rows=66725) (actual time=45..102 rows=99836 loops=1)
        -> Table scan on a  (cost=40238..40253 rows=950) (actual time=45..45.1 rows=1000 loops=1)
            -> Materialize  (cost=40238..40238 rows=950) (actual time=45..45 rows=1000 loops=1)
                -> Group aggregate: avg(emp_big.salary)  (cost=40143 rows=950) (actual time=0.916..44.7 rows=1000 loops=1)
                    -> Covering index scan on emp_big using idx_emp_big_dept  (cost=20124 rows=200196) (actual time=0.885..29.8 rows=200000 loops=1)
        -> Filter: (e.salary > a.dept_avg)  (cost=1.3 rows=70.2) (actual time=0.033..0.0504 rows=99.8 loops=1000)
            -> Covering index lookup on e using idx_emp_big_dept (dept_id=a.dept_id)  (cost=1.3 rows=211) (actual time=0.0186..0.0315 rows=200 loops=1000)

MySQL には、相関スカラーサブクエリを派生表に自動で変換するオプティマイザスイッチ subquery_to_derived があります。8.0.46 では既定で off でした。SET optimizer_switch='subquery_to_derived=on'; を実行してから同じ相関サブクエリを流すと、SQL を書き換えなくても、平均の集計が派生表（derived_1_2）として1回だけ実行される計画に変わり、約0.15秒で終わりました。

このスイッチの既定値は版によって変わる可能性があるため、8.4／9.7 で使うときは SELECT @@optimizer_switch; で現在の値を確かめてください（本記事では 8.0.46 のみ実行して確認しています）。

PostgreSQL 16 の実行計画

相関サブクエリは SubPlan 1 が loops=200000 で実行されました。

 Aggregate  (actual time=10870.924..10870.925 rows=1 loops=1)
   ->  Seq Scan on emp_big e  (actual time=75.392..10859.024 rows=99836 loops=1)
         Filter: ((salary)::numeric > (SubPlan 1))
         Rows Removed by Filter: 100164
         SubPlan 1
           ->  Aggregate  (actual time=0.053..0.053 rows=1 loops=200000)
                 ->  Index Only Scan using idx_emp_big_dept on emp_big e2  (actual time=0.009..0.034 rows=200 loops=200000)
                       Index Cond: (dept_id = e.dept_id)
                       Heap Fetches: 0
 Execution Time: 10871.568 ms

（見やすさのため cost の表示を省いています。）JOIN 版は Hash Join になり、表を2回読むだけで終わります。

 Aggregate  (actual time=165.715..165.793 rows=1 loops=1)
   ->  Hash Join  (actual time=77.965..161.004 rows=99836 loops=1)
         Hash Cond: (e.dept_id = emp_big.dept_id)
         Join Filter: ((e.salary)::numeric > (avg(emp_big.salary)))
         Rows Removed by Join Filter: 100164
         ->  Seq Scan on emp_big e  (actual time=0.003..16.882 rows=200000 loops=1)
         ->  Hash  (actual time=77.947..78.023 rows=1000 loops=1)
               ->  Finalize HashAggregate  (actual time=77.599..77.922 rows=1000 loops=1)
                     Group Key: emp_big.dept_id
                     ...
 Execution Time: 165.852 ms

ウィンドウ関数版は WindowAgg の下に Index Only Scan using idx_emp_big_dept が1回（loops=1）あるだけの計画で、インデックスを部署順に1回読んで終わりました（Execution Time: 169.122 ms）。

INやEXISTSは、データベースが自動でJOINに変換する

一方、IN や EXISTS のサブクエリは、書き換えなくても内部で結合（セミジョイン）として処理されます。サンプル表での計画です。

-- MySQL 8.0.46：WHERE dept_id IN (SELECT dept_id FROM departments WHERE ...)
-> Inner hash join (employees.dept_id = departments.dept_id)  (cost=2.3 rows=2)
    -> Table scan on employees  (cost=0.175 rows=7)
    -> Hash
        -> Filter: (departments.dept_name in ('開発','総務'))  (cost=0.65 rows=2)
            -> Table scan on departments  (cost=0.65 rows=4)

PostgreSQL 16.15 でも同じSQLの計画は Hash Join（Hash Cond: (employees.dept_id = departments.dept_id)）でした。どちらもサブクエリの形は消え、結合になっています。つまり、「サブクエリは遅い」と一律に考える必要はありません。繰り返しが起きやすいのは、今回測った「集約を含む相関スカラーサブクエリ」のように、自動変換の対象になりにくい形です。EXPLAIN に DEPENDENT SUBQUERY（MySQL）や SubPlan（PostgreSQL）と大きな loops が出ていたら、書き換えを検討する合図になります。実行計画の読み方そのものは「実行計画の見方」の記事を参照してください。

どの書き方を選ぶかの判断表

書く前のチェックリスト

• [ ] =・<・> の右側のサブクエリは、必ず1行1列になるか（主キー・UNIQUE・集約関数・LIMIT 1 のどれかで保証する）
• [ ] NOT IN のサブクエリが返す列に、NULL が入る可能性はないか
• [ ] サブクエリの中の列に、表の別名を付けたか
• [ ] FROM 句のサブクエリに別名を付けたか
• [ ] 相関サブクエリの内側で使う結合列に、インデックスがあるか
• [ ] JOIN に書き換えたとき、行数が増えていないか（件数を COUNT(*) で見比べる）

よくある質問

Q. サブクエリと副問い合わせは違うものですか？ A. 同じものです。英語の subquery をカタカナにしたのが「サブクエリ」、日本語に訳したのが「副問い合わせ（副問合せ）」です。外側のSELECT文は「主問い合わせ」「メインクエリ」と呼ばれます。

Q. サブクエリとJOINはどちらが速いですか？ A. 書き方だけでは決まりません。IN や EXISTS は、MySQL 8.0 でも PostgreSQL 16 でも内部で結合に変換され、JOIN と同じような計画になりました。差が出たのは集約を含む相関サブクエリで、20万行の表では JOIN 版が約0.1〜0.2秒、相関サブクエリ版が約11秒でした。迷ったら EXPLAIN ANALYZE で両方を測るのが確実です。

Q. 相関サブクエリは使わないほうがよいですか？ A. 小さな表や、EXISTS／NOT EXISTS の形であれば、読みやすく十分に速い書き方です。行数の多い表で、集約を含む相関サブクエリを行ごとに実行する形になったときに、JOIN やウィンドウ関数への書き換えを検討するとよいでしょう。

Q. サブクエリの中で ORDER BY は使えますか？ A. 書けますが、LIMIT と組み合わせない ORDER BY は外側の並び順を保証しません。並べたいときは、いちばん外側のSELECTに ORDER BY を書きます。「先頭の1件」を取り出す目的なら、ORDER BY ... LIMIT 1 をサブクエリに書く形が両DBで動きました。

まとめ

• サブクエリは「返す形」で5パターンに分かれる：値1つ（スカラー）、1列（IN）、有無（EXISTS）、表（FROM句の派生表）、外側の行ごと（相関）。
• 相関サブクエリは、外側の1行ごとに内側を評価する。EXPLAIN では MySQL が DEPENDENT SUBQUERY、PostgreSQL が SubPlan と表示する。
• = の右側が2行以上返すと「Subquery returns more than 1 row」。IN に変えるか、集約関数・LIMIT 1・主キーで1行にする。
• NOT IN はサブクエリに NULL が混ざると0件になる。「〜でない行」は NOT EXISTS を第一候補にする。
• サブクエリの中の列には別名を付ける。列名の書き間違いが、意図しない相関サブクエリになるのを防げる。
• 大きな表では、相関サブクエリを派生表との JOIN やウィンドウ関数に書き換えると集計が1回で済む。20万行の実測で約11秒が約0.1〜0.5秒になった。
• IN や EXISTS はデータベースが自動で結合に変換する。書き換えるかどうかは EXPLAIN ANALYZE の loops を見て決める。

株式会社RJCでは、SQLのサブクエリの使い方や実行計画の読み方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。