相関サブクエリとNOT IN — 行の数だけ実行される

相関サブクエリとNOT IN。入れ子の検索カードと虫眼鏡、空白のデータカード。

相関サブクエリは、行の数だけ実行されます。NOT IN の落とし穴と合わせて、書き換え方を確かめます。

これは【深掘り】です。本線の順路ではありません。 読まなくても先へは進めます。 本線のMySQLとSQLの基本の回の続きとして、サブクエリだけを最後まで潰します。

前回: SQLの組み込み関数 — CONCATにNULL1つで全部消える 作業場所はVMの中(Ubuntu Server)です。MySQL が動いていれば、それ以外の準備は要りません。この記事の中で練習用の表を作って、最後に消します。

前回までで、1枚の表を読み書きし(SELECT / UPDATE)、2枚の表を繋ぎ、集計しました。

次に必ずぶつかるのが、これです。

「全社の平均給与より高い社員を出したい」

平均給与は SELECT AVG(salary) FROM d_sq_emp; で出ます。でもその数字を、WHERE に手で書き写すわけにはいきません。 1人入社したら変わるからです。

「先に別の SELECT を実行して、その結果を使う」。それがサブクエリです。

ここまでは、たいていの入門記事に書いてあります。問題はその先です。

私が実際にぶつかったのは、この3つでした。

  • 同じ結果を返す2つの書き方があって、片方だけ30倍遅い(EXPLAIN を見るまで理由が分からない)
  • NOT IN で書いた検索が、ある日から0行しか返さなくなった(エラーは出ない。データが1行増えただけ)
  • FROM の中に SELECT を書いたら ERROR 1248 で怒られた(何が足りないのか分からない)

この3つは、全部「サブクエリを場所と形で分けて考えていない」ことが原因です。

この記事では、まず書ける場所4つと返る形3つを先に表にします。そのうえで、遅さと NULL の地雷を、画面に出して潰します。

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • サブクエリを書ける場所は4つ(SELECT の中 / FROM の中 / WHERE の中 / HAVING の中)で、場所ごとに要求される「返る形」が違うこと。形が合わないと ERROR 1242 で止まること
  • 相関サブクエリは、外側の1行ごとに実行されること。「遅い」の正体はそこで、EXPLAIN の DEPENDENT SUBQUERY という文字がそれを教えてくれること
  • NOT IN の中に NULL が1つ混ざると、結果が丸ごと0行になること。エラーも警告も出ないこと。そして NOT EXISTS はその影響を受けないこと
  • FROM の中に書いたサブクエリ(派生表)には別名が必須であること。それは文法上の要求で、省略できないこと
  • SELECT の中に書いたサブクエリも、行の数だけ実行されること。それを LEFT JOIN に書き換えると結果は同じで速いこと
  • 「JOIN とサブクエリ、どちらで書くか」に明確な基準があること

この記事でやること

  1. 練習用の表を3つ作る(社員・部署・売上)。NULL と、売上が1件も無い部署を、わざと入れる
  2. 書ける場所4つを、1例ずつ実際に打つ。 そのあと ERROR 1242 をわざと出す
  3. 相関サブクエリを EXPLAIN にかけて DEPENDENT SUBQUERY を見る。 20万行の表で JOIN 版と実行時間を並べる
  4. NOT IN に NULL を混ぜて0行にする。 NOT EXISTS と LEFT JOIN ... IS NULL で直し、3通りの EXPLAIN を比べる
  5. 派生表と、SELECT の中のサブクエリを書き換える
  6. 作った表を全部消す

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
サブクエリ / 副問い合わせ別の SELECT の中に書かれた SELECT。 先に内側が実行され、その結果を外側が使う
スカラサブクエリ1行1列=ただ1つの値を返すサブクエリ。5 や 470000 と書いたのと同じように扱える
相関サブクエリ内側が、外側の列を参照しているサブクエリ。外側の行が決まらないと内側を実行できないのが特徴
派生表FROM の中に書いたサブクエリ。 「その場で作った、名前のない表」。別名を付けるのが必須
半結合 / アンチ結合「居るか」だけを見る結合(EXISTS)と、「居ないか」だけを見る結合(NOT EXISTS)。行は増えない
NULL「値が無い」という状態。 0でも空文字でもない。比較すると「真」でも「偽」でもなく「不明」になる
実行計画 / EXPLAINデータベースがどう実行するつもりかを出させるコマンド。本線のパフォーマンスの回で扱ったもの

練習用の表を作る

この記事は他の回のデータに依存しません。 ここに書いてある CREATE と INSERT を打てば、そのまま最後まで進めます。

VMの中で MySQL に入ります。

bash
$ sudo mysql

sudo は「管理者としてこのコマンドを実行する」という意味です。 MySQL を入れた直後の Ubuntu では、sudo mysql でパスワードなしで管理者として入れる設定になっています。

データベースを選びます。

USE appdb;

ERROR 1049 (42000): Unknown database 'appdb' と出たら、まだ無いだけなので作ります。

CREATE DATABASE appdb CHARACTER SET utf8mb4;
USE appdb;

utf8mb4 は日本語を正しく入れるための文字コードの指定です。ここを間違えると Incorrect string value で弾かれます。理由は本線の文字コードの回にあります。

ここから作る表には、全部 d_sq_ を頭に付けます。 d は deep(深掘り)、sq は subquery のつもりです。既存の表や、他の深掘り回で作った d_emp などに触らないための、ただの自衛です。

同じ名前の表が残っていたら消します。まだ何も作っていなければ、何も起きません。

DROP TABLE IF EXISTS d_sq_sales;
DROP TABLE IF EXISTS d_sq_emp;
DROP TABLE IF EXISTS d_sq_dept;

部署の表です。

CREATE TABLE d_sq_dept (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

INSERT INTO d_sq_dept (dept_id, dept_name) VALUES
  (10, '営業部'),
  (20, '開発部'),
  (30, '総務部'),
  (40, '監査室'),
  (50, '広報部'),
  (60, '新規事業室');

社員の表です。

CREATE TABLE d_sq_emp (
  emp_id   INT PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  dept_id  INT NULL,
  salary   INT NOT NULL
);

INSERT INTO d_sq_emp (emp_id, emp_name, dept_id, salary) VALUES
  (1,  '佐藤 一郎', 10,   620000),
  (2,  '鈴木 花子', 10,   480000),
  (3,  '高橋 健',   20,   560000),
  (4,  '田中 実',   20,   430000),
  (5,  '伊藤 望',   NULL, 390000),
  (6,  '渡辺 悠',   30,   510000),
  (7,  '中村 葵',   30,   350000),
  (8,  '小林 蓮',   50,   400000),
  (9,  '加藤 凛',   50,   460000),
  (10, '山本 匠',   20,   500000);

売上の表です。社員に対して付きます(部署には直接付きません)。

CREATE TABLE d_sq_sales (
  sale_id INT PRIMARY KEY,
  emp_id  INT NOT NULL,
  amount  INT NOT NULL,
  sold_on DATE NOT NULL
);

INSERT INTO d_sq_sales (sale_id, emp_id, amount, sold_on) VALUES
  (1,  1,  120000, '2026-04-10'),
  (2,  1,   80000, '2026-05-12'),
  (3,  2,  150000, '2026-04-20'),
  (4,  3,   60000, '2026-05-02'),
  (5,  3,   90000, '2026-06-15'),
  (6,  4,   30000, '2026-06-20'),
  (7,  6,  200000, '2026-04-05'),
  (8, 10,   45000, '2026-07-01'),
  (9,  1,   50000, '2026-07-11'),
  (10, 2,   70000, '2026-07-22');

この3枚には、全部意図があります。

わざと入れたもの何のためか
dept_id が NULL の社員(伊藤 望)NOT IN の地雷。 この1行があるだけで、後半の検索結果が丸ごと消えます
社員が1人もいない部署(監査室・新規事業室)NOT EXISTS の答え。 「無いものを探す」の練習台
社員はいるが売上が0件の部署(広報部)「0」と「行が無い」の違い。 JOIN すると消え、サブクエリだと残ります
売上が1件も無い社員(伊藤・中村・小林・加藤)半結合とアンチ結合の練習台

入ったことを確認します。

SELECT COUNT(*) FROM d_sq_dept;
SELECT COUNT(*) FROM d_sq_emp;
SELECT COUNT(*) FROM d_sq_sales;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

6・10・10。 この数字を覚えておいてください。

サブクエリを書ける場所は4つ

先に地図を出します。 サブクエリで詰まる原因のほとんどは、「どこに書いているか」を意識していないことです。

書く場所何をするものか求められる形相関できるか
SELECT の中1行ごとに、値を1つ足す1行1列(1つの値)だけできる(そして遅くなる)
FROM の中(派生表)その場で表を作って、それを読む表(何行何列でもよい)原則できない
WHERE の中行を絞る1値(= >)/1列複数行(IN EXISTS)できる(そして遅くなる)
HAVING の中グループを絞る1値が基本できる

この4つを、1つずつ実際に打ちます。 ここは「動くこと」の確認です。遅さと NULL の話は、そのあとで山場としてやります。

1. WHERE の中 — 一番よく書く場所

「全社平均より給料が高い社員」です。

まず、内側だけを単体で打ちます。サブクエリは、必ず内側だけで打てるかを先に確かめてください。

SELECT AVG(salary) FROM d_sq_emp;
+-------------+
| AVG(salary) |
+-------------+
| 470000.0000 |
+-------------+

この 470000.0000 を、そのまま外側に埋め込みます。 ただし数字ではなく、SELECT 文ごと埋めます。

SELECT emp_id, emp_name, salary
FROM d_sq_emp
WHERE salary > (SELECT AVG(salary) FROM d_sq_emp);
+--------+------------+--------+
| emp_id | emp_name   | salary |
+--------+------------+--------+
|      1 | 佐藤 一郎  | 620000 |
|      2 | 鈴木 花子  | 480000 |
|      3 | 高橋 健    | 560000 |
|      6 | 渡辺 悠    | 510000 |
|     10 | 山本 匠    | 500000 |
+--------+------------+--------+
5 rows in set (0.00 sec)

5人。 ここで大事なのは、内側の SELECT は1回しか実行されていないことです。外側と内側に関係が無いので、データベースは先に1回計算して、あとは定数として使い回せます。

この形は速いです。 遅くなるのは、次の「相関」からです。

2. SELECT の中 — 値を1つ足す

「部署ごとの社員数」を、部署一覧の右に1列足します。

SELECT d.dept_id,
       d.dept_name,
       (SELECT COUNT(*) FROM d_sq_emp e WHERE e.dept_id = d.dept_id) AS emp_count
FROM d_sq_dept d
ORDER BY d.dept_id;
+---------+-----------------+-----------+
| dept_id | dept_name       | emp_count |
+---------+-----------------+-----------+
|      10 | 営業部          |         2 |
|      20 | 開発部          |         3 |
|      30 | 総務部          |         2 |
|      40 | 監査室          |         0 |
|      50 | 広報部          |         2 |
|      60 | 新規事業室      |         0 |
+---------+-----------------+-----------+

監査室と新規事業室が 0 で出ていることに注目してください。JOIN で書くと、この2行は消えます(INNER JOIN の場合)。ここはサブクエリが素直な場面です。

ただしこれは相関サブクエリです。 内側が d.dept_id(外側の列)を見ています。部署が6行なので6回実行されています。 部署が10万件だったらどうなるか、は山場①でやります。

3. FROM の中 — 派生表

「部署ごとの売上合計を出して、そのあとで絞る」です。

SELECT t.dept_id, t.total
FROM (
  SELECT e.dept_id, SUM(s.amount) AS total
  FROM d_sq_emp e
  JOIN d_sq_sales s ON s.emp_id = e.emp_id
  GROUP BY e.dept_id
) AS t
WHERE t.total >= 220000;
+---------+--------+
| dept_id | total  |
+---------+--------+
|      10 | 470000 |
|      20 | 225000 |
+---------+--------+

AS t を消すと動きません。 その話は後半で1節を割きます。

4. HAVING の中 — グループを絞る

「売上合計が、全社の1件あたり平均額を超える社員」です。

まず全社の1件あたり平均を見ます。

SELECT AVG(amount) FROM d_sq_sales;
+-------------+
| AVG(amount) |
+-------------+
|  89500.0000 |
+-------------+

これを HAVING に埋めます。

SELECT e.emp_id, e.emp_name, SUM(s.amount) AS total
FROM d_sq_emp e
JOIN d_sq_sales s ON s.emp_id = e.emp_id
GROUP BY e.emp_id, e.emp_name
HAVING SUM(s.amount) > (SELECT AVG(amount) FROM d_sq_sales)
ORDER BY total DESC;
+--------+------------+--------+
| emp_id | emp_name   | total  |
+--------+------------+--------+
|      1 | 佐藤 一郎  | 250000 |
|      2 | 鈴木 花子  | 220000 |
|      6 | 渡辺 悠    | 200000 |
|      3 | 高橋 健    | 150000 |
+--------+------------+--------+

WHERE ではなく HAVING なのは、SUM() の結果で絞っているからです。WHERE は集計する前、HAVING は集計した後に働きます。ここは集計の回で扱った話です。

4つとも打てました。 ここまでは「書ける」の確認です。

返る形は3種類しかない

場所と同じくらい大事なのが、「サブクエリが何を返すか」です。 返る形は3つしかありません。

形例使える演算子書ける場所
1行1列(1値)SELECT AVG(salary) FROM d_sq_emp= > < <> などSELECT WHERE HAVING
1列・複数行SELECT dept_id FROM d_sq_deptIN NOT IN ANY ALLWHERE HAVING
表(複数列・複数行)SELECT dept_id, SUM(...) ... GROUP BY ...なし(そのまま表として読む)FROM のみ

この3つを取り違えると、エラーになります。 わざと出します。

わざと ERROR 1242 を出す

「営業部の社員を出したい」つもりで、= を使ってしまった例です。

SELECT emp_name FROM d_sq_emp WHERE dept_id = (SELECT dept_id FROM d_sq_dept);
ERROR 1242 (21000): Subquery returns more than 1 row

メッセージがそのまま答えです。 「サブクエリが1行より多く返した」。= は1つの値としか比べられないのに、内側は6行返しています。

直し方は2つあります。

(A)内側を1行に絞る。

SELECT emp_name FROM d_sq_emp WHERE dept_id = (SELECT dept_id FROM d_sq_dept WHERE dept_name = '営業部');
+------------+
| emp_name   |
+------------+
| 佐藤 一郎  |
| 鈴木 花子  |
+------------+

(B)演算子を IN に変える(複数行を受け取れるようにする)。

SELECT emp_name FROM d_sq_emp WHERE dept_id IN (SELECT dept_id FROM d_sq_dept WHERE dept_id <= 20);
+------------+
| emp_name   |
+------------+
| 佐藤 一郎  |
| 鈴木 花子  |
| 高橋 健    |
| 田中 実    |
| 山本 匠    |
+------------+

(A)は危険な直し方でもあります。 「いまのデータでは1行だった」だけで、データが増えた日に同じ ERROR 1242 が本番で出ます。 部署名は重複しうるのか、を先に考えてください。重複させたくないなら、UNIQUE 制約で表側に保証させるのが本筋です(制約の回)。

0行返すとエラーにならない。NULL になる

これが ERROR 1242 よりタチが悪いところです。

監査室(dept_id = 40)には社員が1人もいません。その平均給与と比べてみます。

SELECT emp_name, salary FROM d_sq_emp
WHERE salary > (SELECT AVG(salary) FROM d_sq_emp WHERE dept_id = 40);
Empty set (0.00 sec)

エラーも警告も出ません。0行です。

内側だけ打つと理由が分かります。

SELECT AVG(salary) FROM d_sq_emp WHERE dept_id = 40;
+-------------+
| AVG(salary) |
+-------------+
|        NULL |
+-------------+

AVG() は対象が0行だと NULL を返します。 そして salary > NULL は真でも偽でもなく「不明」なので、1行も通りません。

「サブクエリが0行だったとき、外側はどうなるか」を必ず考えてください。 = (...) や > (...) の形は、内側が空だと静かに0行になります。IS NULL の話はSELECTの回で扱いました。 ここでも同じ規則が効いています。

相関サブクエリは、外側の行ごとに実行される

ここがこの記事の中心です。

相関しているとは、どういうことか

さっきの「全社平均より高い社員」は、内側が外側を見ていませんでした。

-- 相関していない:内側だけで実行できる
SELECT AVG(salary) FROM d_sq_emp;

内側だけをコピーして貼れば、そのまま動きます。 だから1回だけ実行して、結果を使い回せます。

では、こう変えます。「自分の部署の平均より高い社員」です。

SELECT e.emp_id, e.emp_name, e.dept_id, e.salary
FROM d_sq_emp e
WHERE e.salary > (SELECT AVG(e2.salary) FROM d_sq_emp e2 WHERE e2.dept_id = e.dept_id);
+--------+------------+---------+--------+
| emp_id | emp_name   | dept_id | salary |
+--------+------------+---------+--------+
|      1 | 佐藤 一郎  |      10 | 620000 |
|      3 | 高橋 健    |      20 | 560000 |
|      6 | 渡辺 悠    |      30 | 510000 |
|      9 | 加藤 凛    |      50 | 460000 |
|     10 | 山本 匠    |      20 | 500000 |
+--------+------------+---------+--------+

内側の WHERE e2.dept_id = e.dept_id の e が、外側の表です。

この1文字が入った瞬間に、性質が変わります。

  • 内側だけをコピーして貼っても動きません(e が無いと言われます)
  • e.dept_id が決まらないと、内側は計算できません
  • つまり e の行を1行取り出すたびに、内側を実行し直す必要があります

社員は10人なので、内側は10回実行されました。

「相関サブクエリ」という名前は、この「外側の列を参照している」状態を指しています。 難しい概念ではなく、内側に外側の別名が出てくるかどうか、それだけで見分けられます。

同じ「5人」でも、中身が違う

さっきの「全社平均より高い5人」と見比べてください。

全社平均(相関なし)自部署の平均(相関あり)
1 佐藤 一郎○○
2 鈴木 花子○×(営業部の平均550000に届かない)
3 高橋 健○○
6 渡辺 悠○○
9 加藤 凛×○(広報部の平均430000より上)
10 山本 匠○○

どちらも5行ですが、別人が入っています。 相関を付けるかどうかは、速さの問題である前に、意味の問題です。

ここでも NULL が1人消している

伊藤 望(dept_id が NULL)が、どちらの結果にもいません。

相関ありのほうで何が起きているか、追ってみます。

SELECT AVG(e2.salary) FROM d_sq_emp e2 WHERE e2.dept_id = NULL;
+----------------+
| AVG(e2.salary) |
+----------------+
|           NULL |
+----------------+

e2.dept_id = NULL はどう転んでも真になりません。 だから内側は0行 → AVG は NULL → 390000 > NULL は「不明」→ 通らない。

エラーは出ません。1人静かに消えるだけです。 「社員は10人いるのに、集計対象が9人になっていた」というのは、この形で起きます。

EXPLAIN で DEPENDENT SUBQUERY を見る

「行ごとに実行される」は、想像ではなく画面で確かめられます。

EXPLAIN SELECT e.emp_id, e.emp_name
FROM d_sq_emp e
WHERE e.salary > (SELECT AVG(e2.salary) FROM d_sq_emp e2 WHERE e2.dept_id = e.dept_id);
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | e     | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | e2    | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+

見るのは select_type の1列だけです。

表示意味
SUBQUERY1回だけ実行される。 外側と無関係なので使い回せる
DEPENDENT SUBQUERY外側の行ごとに実行される。 DEPENDENT(依存している)=相関している
DERIVEDFROM の中の派生表。先に作って1回だけ materialize される
MATERIALIZED内側の結果を一時表に落として使い回す最適化が効いた状態

DEPENDENT SUBQUERY を見つけたら、「外側の行数 × 内側のコスト」を暗算してください。 それがこのクエリの重さです。

[スクリーンショット: 01_explain_dependent_subquery.png] 上の EXPLAIN を実行し、select_type の列に PRIMARY と DEPENDENT SUBQUERY の2行が並んでいる画面を撮ってください。

10行の表では一瞬です。 これを20万行でやります。

20万行を用意する

ここから重くなります。 VMのディスクに数十MB書きます。

まず、大量の行を作るための設定を1つ変えます。

SET SESSION cte_max_recursion_depth = 1000000;

これは接続中だけの設定です。 MySQL を抜ければ既定値に戻ります。設定ファイルは触りません。 この設定が無いと、次の INSERT が ERROR 3636 で1001行目で止まります。

部署1200件。 うち200件は、社員が1人もいない部署になります。

DROP TABLE IF EXISTS d_sq_emp_big;
DROP TABLE IF EXISTS d_sq_dept_big;

CREATE TABLE d_sq_dept_big (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

INSERT INTO d_sq_dept_big (dept_id, dept_name)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1200
)
SELECT n, CONCAT('部署', n) FROM seq;

WITH RECURSIVE は「1から順に数を作る」ための書き方です。 ここでは行を大量に作る道具として使っているだけなので、意味が分からなくてもこの記事は進められます。

社員20万人。 部署は1〜1000番だけを使います(1001〜1200番の部署には誰もいない)。

CREATE TABLE d_sq_emp_big (
  emp_id   INT PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  dept_id  INT NULL,
  salary   INT NOT NULL,
  INDEX idx_big_dept (dept_id)
);

INSERT INTO d_sq_emp_big (emp_id, emp_name, dept_id, salary)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT n,
       CONCAT('社員', n),
       (n % 1000) + 1,
       300000 + (FLOOR(n / 1000) % 200) * 1000
FROM seq;

dept_id に索引を最初から張っています。 索引が無いと、このあとの相関サブクエリが現実的な時間で終わりません(20万回 × 20万行の全走査になります)。「索引があってなお遅い」を見せたいので、条件を揃えました。

入ったか確認します。

SELECT COUNT(*) FROM d_sq_emp_big;
SELECT COUNT(*) FROM d_sq_dept_big;
+----------+
| COUNT(*) |
+----------+
|   200000 |
+----------+

+----------+
| COUNT(*) |
+----------+
|     1200 |
+----------+

相関サブクエリと JOIN を、並べて測る

「自分の部署の平均給与より高い社員は何人か」を、2通りで書きます。答えはどちらも同じです。

(1)相関サブクエリ。

SELECT COUNT(*) FROM d_sq_emp_big e
WHERE e.salary > (SELECT AVG(e2.salary) FROM d_sq_emp_big e2 WHERE e2.dept_id = e.dept_id);
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (6.41 sec)

(2)派生表で先に平均を作って JOIN する。

SELECT COUNT(*) FROM d_sq_emp_big e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM d_sq_emp_big
  GROUP BY dept_id
) AS a ON a.dept_id = e.dept_id
WHERE e.salary > a.avg_sal;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.21 sec)

秒数は環境で変わります。 私のVM(メモリ4GB・仮想2コア)での実測です。見てほしいのは絶対値ではなく、30倍という比のほうです。

なぜこうなるか。EXPLAIN を両方に付けます。

EXPLAIN SELECT COUNT(*) FROM d_sq_emp_big e
WHERE e.salary > (SELECT AVG(e2.salary) FROM d_sq_emp_big e2 WHERE e2.dept_id = e.dept_id);
+----+--------------------+-------+-------+---------------+--------------+---------+---------------+--------+-------------+
| id | select_type        | table | type  | possible_keys | key          | key_len | ref           | rows   | Extra       |
+----+--------------------+-------+-------+---------------+--------------+---------+---------------+--------+-------------+
|  1 | PRIMARY            | e     | ALL   | NULL          | NULL         | NULL    | NULL          | 199502 | Using where |
|  2 | DEPENDENT SUBQUERY | e2    | ref   | idx_big_dept  | idx_big_dept | 5       | appdb.e.dept_id |    199 | NULL      |
+----+--------------------+-------+-------+---------------+--------------+---------+---------------+--------+-------------+

rows の掛け算を見てください。 199502 × 199 = 約4000万行を読むつもりだ、と言っています。索引が効いていて type: ref なのに、それでも4000万です。 回数が多すぎるからです。

EXPLAIN SELECT COUNT(*) FROM d_sq_emp_big e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM d_sq_emp_big
  GROUP BY dept_id
) AS a ON a.dept_id = e.dept_id
WHERE e.salary > a.avg_sal;
+----+-------------+--------------+-------+---------------+--------------+---------+------+--------+------------------------------------------+
| id | select_type | table        | type  | possible_keys | key          | key_len | ref  | rows   | Extra                                    |
+----+-------------+--------------+-------+---------------+--------------+---------+------+--------+------------------------------------------+
|  1 | PRIMARY     | <derived2>   | ALL   | NULL          | NULL         | NULL    | NULL |   1000 | NULL                                     |
|  1 | PRIMARY     | e            | ALL   | NULL          | NULL         | NULL    | NULL | 199502 | Using where; Using join buffer (hash join) |
|  2 | DERIVED     | d_sq_emp_big | index | idx_big_dept  | idx_big_dept | 5       | NULL | 199502 | NULL                                     |
+----+-------------+--------------+-------+---------------+--------------+---------+------+--------+------------------------------------------+

DEPENDENT が消えて DERIVED になりました。

  • id: 2 の DERIVED は、先に1回だけ実行されて1000行の一時表になる
  • id: 1 は、その1000行と20万行を1回のハッシュ結合で突き合わせる

足し算(20万 + 20万 + 1000)で済んでいます。 掛け算(20万 × 199)との差が、そのまま30倍です。

「サブクエリは遅い」ではありません。 遅いのは相関しているサブクエリで、しかも外側の行数が多いときだけです。相関していないサブクエリ(SUBQUERY / MATERIALIZED)は、JOIN に書き換えても普通は速くなりません。

なお MySQL は年々賢くなっていて、バージョンによっては相関サブクエリを自動で JOIN 相当に書き換えます。 だから EXPLAIN の結果があなたの画面と一致しないことがあります。 覚えるのは形ではなく、DEPENDENT SUBQUERY という単語を探すことです。

IN / EXISTS / NOT IN / NOT EXISTS

小さい表に戻ります。

SELECT COUNT(*) FROM d_sq_emp;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+

4つの使い分け

書き方聞いていること返る形の要求NULL に強いか主な使いどころ
INこの値は、リストに含まれるか1列の複数行含む方向は問題なし定数の短いリスト。IN (10, 20)
EXISTS条件に合う行が、1つでもあるか何でもよい(中身は見ない)強い相関させて「居るか」を聞く
NOT INこの値は、リストに含まれないか1列の複数行弱い。NULL が1つで全滅NULL が絶対に無いと保証できるときだけ
NOT EXISTS条件に合う行が、1つも無いか何でもよい強い「無いもの」を探すときの既定

EXISTS が「何でもよい」のは、行が返ったかどうかしか見ないからです。 だから中身は SELECT 1 でも SELECT * でも同じです。SELECT 1 と書く習慣は「中身を使っていない」ことを読み手に伝えるためで、速さの理由ではありません。

IN と EXISTS — まずは同じ結果

「売上が1件以上ある社員」です。

SELECT emp_id, emp_name FROM d_sq_emp
WHERE emp_id IN (SELECT emp_id FROM d_sq_sales);
+--------+------------+
| emp_id | emp_name   |
+--------+------------+
|      1 | 佐藤 一郎  |
|      2 | 鈴木 花子  |
|      3 | 高橋 健    |
|      4 | 田中 実    |
|      6 | 渡辺 悠    |
|     10 | 山本 匠    |
+--------+------------+

EXISTS で書き換えます。相関になります(内側が e.emp_id を見ます)。

SELECT e.emp_id, e.emp_name FROM d_sq_emp e
WHERE EXISTS (SELECT 1 FROM d_sq_sales s WHERE s.emp_id = e.emp_id);

同じ6行が返ります。

これを JOIN で書くと行数が変わります。 JOIN d_sq_sales にすると、佐藤は売上3件なので3回出ます。IN と EXISTS は行を増やしません。 「絞りたいだけ」のときにこれらを選ぶ理由は、速さではなく行数が保たれることです。この話はJOINの回で扱いました。

NOT IN に NULL が1つ混ざると、結果が0行になる

「社員が1人もいない部署」を探します。答えは監査室と新規事業室の2つのはずです。

SELECT dept_id, dept_name FROM d_sq_dept
WHERE dept_id NOT IN (SELECT dept_id FROM d_sq_emp);
Empty set (0.00 sec)

0行です。エラーも警告も出ません。

原因は、伊藤 望の dept_id が NULL であることです。 内側の結果を見ます。

SELECT DISTINCT dept_id FROM d_sq_emp;
+---------+
| dept_id |
+---------+
|    NULL |
|      10 |
|      20 |
|      30 |
|      50 |
+---------+

リストの中に NULL が入っています。

NOT IN は、内部ではこう展開されています。

40 NOT IN (NULL, 10, 20, 30, 50)
  ↓
NOT (40 = NULL OR 40 = 10 OR 40 = 20 OR 40 = 30 OR 40 = 50)
  ↓
NOT (不明 OR 偽 OR 偽 OR 偽 OR 偽)
  ↓
NOT (不明)
  ↓
不明   ← 真ではないので、この行は出ない

40 = NULL が「偽」ではなく「不明」になるのが全部の原因です。 そして「偽」を1つでも含む OR は他が真なら真になれますが、「不明」が混じった OR は、真になれない限り「不明」のままです。NOT 不明 は 不明。どの行も通りません。

この NULL の規則そのものはSELECTの回で扱いました。 そちらが「なぜ = NULL が効かないか」の回で、この記事は「じゃあどう書き直すか」の回です。ここからは対策を厚くやります。

気づけない理由も書いておきます。

  • エラーが出ない。 「該当なし」と区別が付かない
  • 昨日まで動いていた。 NULL が1行入った日から静かに壊れる
  • IN は無事。 IN のほうは NULL があっても、他に一致すれば真になるので気づかない

直し方は3通り。全部打って比べる

(A)内側で NULL を除く。

SELECT dept_id, dept_name FROM d_sq_dept
WHERE dept_id NOT IN (SELECT dept_id FROM d_sq_emp WHERE dept_id IS NOT NULL);
+---------+-----------------+
| dept_id | dept_name       |
+---------+-----------------+
|      40 | 監査室          |
|      60 | 新規事業室      |
+---------+-----------------+

出ました。 ただしこれは、書く人が毎回忘れずに IS NOT NULL を足すという運用に頼っています。忘れたら壊れます。

(B)NOT EXISTS に書き換える。

SELECT d.dept_id, d.dept_name FROM d_sq_dept d
WHERE NOT EXISTS (SELECT 1 FROM d_sq_emp e WHERE e.dept_id = d.dept_id);
+---------+-----------------+
| dept_id | dept_name       |
+---------+-----------------+
|      40 | 監査室          |
|      60 | 新規事業室      |
+---------+-----------------+

IS NOT NULL を書いていないのに正しいことに注目してください。

NOT EXISTS は値を比べていないからです。 聞いているのは「行が返ってきたか」だけ。伊藤 望の行では e.dept_id = d.dept_id が「不明」になって行が返らないので、そもそも候補に入りません。NULL は「一致しなかった」として素直に扱われます。

(C)LEFT JOIN ... IS NULL に書き換える。

SELECT d.dept_id, d.dept_name
FROM d_sq_dept d
LEFT JOIN d_sq_emp e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
+---------+-----------------+
| dept_id | dept_name       |
+---------+-----------------+
|      40 | 監査室          |
|      60 | 新規事業室      |
+---------+-----------------+

これも正しく2行です。 LEFT JOIN で相手がいなかった行は右側が全部 NULL になるので、右側の主キーが NULL かどうかで「いなかった」を判定できます。

WHERE e.emp_id IS NULL の列は、必ず「NULL になりえない列」を選んでください。 ここで e.dept_id IS NULL と書くと、元から NULL だった伊藤の行と区別が付かなくなります。主キーを使うのが安全です。

3通りを EXPLAIN で比べる

大きい表でやります。 1200部署のうち、社員がいない200部署を数えます。

SELECT COUNT(*) FROM d_sq_dept_big d
WHERE d.dept_id NOT IN (SELECT e.dept_id FROM d_sq_emp_big e WHERE e.dept_id IS NOT NULL);
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0.09 sec)
SELECT COUNT(*) FROM d_sq_dept_big d
WHERE NOT EXISTS (SELECT 1 FROM d_sq_emp_big e WHERE e.dept_id = d.dept_id);
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0.01 sec)
SELECT COUNT(*) FROM d_sq_dept_big d
LEFT JOIN d_sq_emp_big e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0.01 sec)

3つとも200。 実行計画を見ます。

EXPLAIN SELECT COUNT(*) FROM d_sq_dept_big d
WHERE NOT EXISTS (SELECT 1 FROM d_sq_emp_big e WHERE e.dept_id = d.dept_id);
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref             | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+------+--------------------------+
|  1 | SIMPLE      | d     | index | PRIMARY       | PRIMARY      | 4       | NULL            | 1200 | Using index              |
|  1 | SIMPLE      | e     | ref   | idx_big_dept  | idx_big_dept | 5       | appdb.d.dept_id |  199 | Using where; Not exists  |
+----+-------------+-------+-------+---------------+--------------+---------+-----------------+------+--------------------------+

select_type が SIMPLE になっています。サブクエリが消えました。

MySQL が NOT EXISTS を「アンチ結合」に書き換えたからです。Extra の Not exists がその印で、「1件見つかった時点で、その外側の行を捨てて次へ行く」という意味です。199行を全部読む必要すらありません。

LEFT JOIN ... IS NULL のほうも見ます。

EXPLAIN SELECT COUNT(*) FROM d_sq_dept_big d
LEFT JOIN d_sq_emp_big e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

NOT EXISTS とほぼ同じ計画(Not exists 付き)が出ます。この2つは、書き方が違うだけで同じものになります。

NOT IN 版は、環境によって形が変わります。

EXPLAIN SELECT COUNT(*) FROM d_sq_dept_big d
WHERE d.dept_id NOT IN (SELECT e.dept_id FROM d_sq_emp_big e WHERE e.dept_id IS NOT NULL);
+----+--------------------+-------+-------+---------------+--------------+---------+------+--------+--------------------------+
| id | select_type        | table | type  | possible_keys | key          | key_len | ref  | rows   | Extra                    |
+----+--------------------+-------+-------+---------------+--------------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | d     | index | PRIMARY       | PRIMARY      | 4       | NULL |   1200 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | e     | ref   | idx_big_dept  | idx_big_dept | 5       | func |    199 | Using where; Using index |
+----+--------------------+-------+-------+---------------+--------------+---------+------+--------+--------------------------+

DEPENDENT SUBQUERY が出ました。

書いた NOT IN は相関していないのに、DEPENDENT になっています。 MySQL が NOT IN を「外側の値ごとに探す」形に内部で変換したためです。NOT IN は書き手からは相関に見えなくても、実行時には相関になりうる、ということです。

バージョンによっては MATERIALIZED になり、この差は消えます。 そこは環境依存なので断定しません。断定できるのは、NOT EXISTS と LEFT JOIN ... IS NULL が安定してアンチ結合になり、NULL の影響も受けないことです。

結論はこうです。

「無いもの」を探すときは NOT EXISTS。 NOT IN は、中身に NULL が絶対に無いと言い切れるとき(NOT NULL 制約が付いた列、または定数リスト)だけにする。

派生表 — FROM の中に書く

別名が無いと動かない

さっきの派生表から AS t を外します。

SELECT dept_id, total
FROM (
  SELECT e.dept_id, SUM(s.amount) AS total
  FROM d_sq_emp e
  JOIN d_sq_sales s ON s.emp_id = e.emp_id
  GROUP BY e.dept_id
);
ERROR 1248 (42000): Every derived table must have its own alias

「すべての派生表は、それ自身の別名を持たなければならない」。 これも直し方がメッセージに書いてあります。

理由は単純で、名前が無いと参照できないからです。 FROM に置かれたものは表として扱われるので、t.dept_id のようにどの表の列かを書けなければ困ります。列名がぶつかったとき(両方に dept_id があるとき)に、区別する手段が無くなります。

AS は省略できますが、名前そのものは省略できません。

SELECT dept_id, total
FROM (
  SELECT e.dept_id, SUM(s.amount) AS total
  FROM d_sq_emp e
  JOIN d_sq_sales s ON s.emp_id = e.emp_id
  GROUP BY e.dept_id
) t;

これは動きます(AS 無しの t)。私は AS t と書く派です。消し忘れたのか意図的なのかが読み手に分かるからです。

派生表の典型 — 「集計した結果を、さらに集計する」

HAVING では書けない例を出します。

「部署ごとの売上合計を出して、その合計たちの平均を取る」。

SELECT AVG(t.total) AS avg_of_dept_totals
FROM (
  SELECT e.dept_id, SUM(s.amount) AS total
  FROM d_sq_emp e
  JOIN d_sq_sales s ON s.emp_id = e.emp_id
  GROUP BY e.dept_id
) AS t;
+--------------------+
| avg_of_dept_totals |
+--------------------+
|      298333.333333 |
+--------------------+

AVG(SUM(amount)) とは書けません。 集計関数を入れ子にはできないからです。「1回集計してから、もう1回集計する」には、途中の結果を表にする必要があります。 それが派生表の一番の使いどころです。

そしてこの値を HAVING に埋めれば、「平均以上に売っている部署」が出ます。

SELECT e.dept_id, SUM(s.amount) AS total
FROM d_sq_emp e
JOIN d_sq_sales s ON s.emp_id = e.emp_id
GROUP BY e.dept_id
HAVING SUM(s.amount) > (
  SELECT AVG(t.total) FROM (
    SELECT e2.dept_id, SUM(s2.amount) AS total
    FROM d_sq_emp e2
    JOIN d_sq_sales s2 ON s2.emp_id = e2.emp_id
    GROUP BY e2.dept_id
  ) AS t
);
+---------+--------+
| dept_id | total  |
+---------+--------+
|      10 | 470000 |
+---------+--------+

営業部だけです。

同じ内容を2回書いていて、読みにくいと感じたはずです。 その感覚は正しく、これを1回で書く仕組みが MySQL にもあります(WITH を使う書き方)。この記事では派生表だけに絞ります。

SELECT の中に書く — スカラサブクエリ

最初に打った「部署ごとの社員数」を、もっと欲張ります。 社員数と売上合計を2列足します。

SELECT d.dept_name,
       (SELECT COUNT(*) FROM d_sq_emp e WHERE e.dept_id = d.dept_id) AS emp_count,
       (SELECT COALESCE(SUM(s.amount), 0)
          FROM d_sq_sales s
          JOIN d_sq_emp e2 ON e2.emp_id = s.emp_id
         WHERE e2.dept_id = d.dept_id) AS total_sales
FROM d_sq_dept d
ORDER BY d.dept_id;
+-----------------+-----------+-------------+
| dept_name       | emp_count | total_sales |
+-----------------+-----------+-------------+
| 営業部          |         2 |      470000 |
| 開発部          |         3 |      225000 |
| 総務部          |         2 |      200000 |
| 監査室          |         0 |           0 |
| 広報部          |         2 |           0 |
| 新規事業室      |         0 |           0 |
+-----------------+-----------+-------------+

読みやすいです。 部署一覧に列を足しているだけ、と一目で分かります。

COALESCE(SUM(s.amount), 0) は「SUM が NULL なら 0 にする」という意味です。 対象が0件のとき SUM は 0 ではなく NULL を返すので、これを挟まないと広報部が NULL と表示されます。

ただし、これは相関サブクエリを2つ抱えています。

  • 部署が6行なので、内側は 6×2 = 12回実行されている
  • 部署が10万件なら20万回

列を1つ足すたびに、実行回数が「行数ぶん」増えていくのがこの形の性質です。

LEFT JOIN に書き換える

SELECT d.dept_name,
       COUNT(DISTINCT e.emp_id) AS emp_count,
       COALESCE(SUM(s.amount), 0) AS total_sales
FROM d_sq_dept d
LEFT JOIN d_sq_emp e   ON e.dept_id = d.dept_id
LEFT JOIN d_sq_sales s ON s.emp_id  = e.emp_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;
+-----------------+-----------+-------------+
| dept_name       | emp_count | total_sales |
+-----------------+-----------+-------------+
| 営業部          |         2 |      470000 |
| 開発部          |         3 |      225000 |
| 総務部          |         2 |      200000 |
| 監査室          |         0 |           0 |
| 広報部          |         2 |           0 |
| 新規事業室      |         0 |           0 |
+-----------------+-----------+-------------+

同じ結果です。 そして表を読むのは1回ずつです。

ただし、書き換えには代償があります。

スカラサブクエリ版LEFT JOIN 版
読みやすさ高い。 列を足しているだけ低い。GROUP BY の意味を考える必要がある
実行回数行数 × 列数表ごとに1回
COUNT の罠無いDISTINCT を忘れると社員数が水増しされる
部署が0件のとき0 が出るLEFT にすれば 0 が出る。INNER だと行ごと消える

COUNT(DISTINCT e.emp_id) の DISTINCT は必須です。 外すと営業部が「5」になります。売上5件ぶんに行が増えているからです。JOIN した結果を数えるときの定番の事故で、JOINの回でも同じことを書きました。

6行の表なら、スカラサブクエリ版のままで構いません。 書き換えるのは、遅いと分かってからです。先に測る、は本線のパフォーマンスの回と同じ順序です。

どちらで書くか — 私の判断基準

「サブクエリと JOIN、どちらが正しいか」に答えはありません。 ただ、選ぶ順番は決められます。

やりたいこと選ぶもの理由
相手の列を、結果に表示したいJOINサブクエリでは相手の列を複数持ってこられない
「居るか」だけで絞りたいEXISTS行が増えない。 JOIN は件数が膨らんで COUNT を壊す
「居ないもの」を探したいNOT EXISTSNULL の影響を受けない。 NOT IN は静かに0行になる
1つの値と比べたい(全社平均など)相関しないサブクエリ1回しか実行されない。速い
集計してから、さらに絞りたい/集計したい派生表HAVING では2段の集計が書けない
定数の短いリストで絞りたいININ (10, 20) は読みやすく、NULL も混ざらない
相関していて、外側が数万行以上JOIN か派生表に書き換えるDEPENDENT SUBQUERY は掛け算になる

そして、これは書いておきたいことです。

読みやすさを優先してよい場面は、確実にあります。

行数が数百までの表で、月に1回しか実行しない集計なら、スカラサブクエリを並べたほうが半年後の自分に優しいです。JOIN と GROUP BY と DISTINCT が絡んだクエリは、読み間違えると静かに数字が狂います。 遅さは EXPLAIN で見つかりますが、読み間違えた集計は誰も気づきません。

私の順番はこうです。

  1. まず、意味が正しく読める書き方で書く
  2. 遅いと感じたら、EXPLAIN を取って DEPENDENT SUBQUERY を探す
  3. 見つかったら、そこだけ JOIN か派生表に書き換えて、時間を並べる

「サブクエリは遅いから使わない」から入ると、意味を取り違えたクエリを書いて、そちらのほうが高くつきます。

つまずいたところ

遠回りした順に書きます。

1. NOT IN が0行になり、データが消えたと思ってバックアップを探した

これが一番焦りました。昨日まで2件返っていた検索が、0件になりました。 データを消したのだと思って SELECT COUNT(*) を何度も打ちました。行は増えてすらいました。

増えた1行の dept_id が NULL だっただけでした。エラーが出ないので、SQLを疑うのが最後になりました。 順番が逆でした。

「NOT IN で0行」を見たら、まず内側に NULL が無いかを見る、と決めました。

2. EXISTS の中に SELECT * と書いてあるのを見て、「全列取ってきて遅いのでは」と思った

SELECT 1 に書き換えて EXPLAIN を取りましたが、まったく同じ計画でした。 EXISTS は行が返るかしか見ないので、中身は評価されません。「速くなるはず」と思って書き換えたことが間違いで、測らずに直そうとしていました。

3. 相関サブクエリを JOIN に書き換えて、結果が偶然合っていた

「自分の部署の平均より高い社員」を JOIN に書き換えたとき、件数は合いましたが、派生表の中身が想定と違いました。 GROUP BY dept_id した派生表に dept_id が NULL のグループが1行できていたのです。JOIN の ON では NULL = NULL が一致しないので結果は同じになりましたが、「一致しないから偶然合っている」だけだと気づくのに時間がかかりました。

書き換えたら、必ず COUNT(*) を両方で取って突き合わせるようにしました。

4. 派生表の ERROR 1248 で、括弧の対応を数え直した

「派生表は自身の別名を持たねばならない」を読んで、なぜか括弧が閉じていないのだと思い込みました。 3回数え直して、そのあとメッセージをもう一度読んで AS t を足しました。エラーは下から読む、が身に付いていませんでした(本線のエラーの読み方の回)。

5. 20万行を入れようとして止まった

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

直し方がメッセージに書いてあります。 SET SESSION cte_max_recursion_depth = 1000000; を先に打てば通ります。

6. 索引を張っても相関サブクエリが速くならなかった

「遅いなら索引だろう」で、dept_id に索引を張りました。 type は ALL から ref になりましたが、体感は変わりませんでした。

1回あたりは速くなっていました。回数が20万回のままだっただけです。索引は「1回のコスト」を下げるもので、「実行回数」は減らしません。 回数を減らすには、クエリの形を変えるしかありませんでした。

7. EXPLAIN の出力が、記事や本と違う

MySQL のバージョンによって、同じSQLでも MATERIALIZED になったり DEPENDENT SUBQUERY になったりします。最初は「自分の環境が壊れている」と思いました。 そうではなく、最適化が年々変わっているだけでした。覚えるべきは出力の形ではなく、DEPENDENT という単語を探すことだと分かりました。

後片づけ

練習用の表を全部消します。 d_sq_ で始まるものだけです。

DROP TABLE IF EXISTS d_sq_sales;
DROP TABLE IF EXISTS d_sq_emp;
DROP TABLE IF EXISTS d_sq_dept;
DROP TABLE IF EXISTS d_sq_emp_big;
DROP TABLE IF EXISTS d_sq_dept_big;

消えたことと、他の表が残っていることを確認します。

SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| knex_migrations |
| tasks           |
+-----------------+

d_sq_ で始まる表が1つも無ければ成功です。 表示される中身は、あなたがここまでに作ったものによって変わります。d_sq_ が消えていることだけ見てください。

MySQL を抜けます。

EXIT;

cte_max_recursion_depth は元に戻ります。 SET SESSION は接続中だけの設定なので、抜けた時点で既定値です。設定ファイルは触っていません。

もし途中で appdb 自体をこの記事のために新規作成したなら、それも消して構いません。本線で使っている場合は消さないでください。

-- 本線で appdb を使っているなら、これは打たないこと
DROP DATABASE appdb;

まとめ

この記事で理解できたことを、6行にします。

  1. サブクエリを書ける場所は4つ(SELECT / FROM / WHERE / HAVING)で、場所ごとに求められる形が違う。形が合わなければ ERROR 1242、FROM に別名が無ければ ERROR 1248。どちらもメッセージが直し方を書いている
  2. 内側が0行を返すとエラーではなく NULL になる。 そして > NULL は「不明」なので、外側が静かに0行になる。エラーが出ないほうが怖い
  3. 相関サブクエリは、外側の1行ごとに実行される。 それが EXPLAIN の DEPENDENT SUBQUERY。rows を掛け算した値がそのクエリの重さで、20万行では30倍の差になった
  4. 索引は1回のコストを下げるが、実行回数は減らさない。 回数を減らすのはクエリの形を変えること(派生表 + JOIN)だけ
  5. NOT IN に NULL が1つ混ざると、結果が丸ごと0行になる。 NOT EXISTS と LEFT JOIN ... IS NULL は影響を受けず、同じアンチ結合の計画になる。 「無いものを探す」は NOT EXISTS を既定にする
  6. 選ぶ順番は「相手の列を出したいなら JOIN、居るかだけなら EXISTS、集計の集計なら派生表」。 そしてまず読める書き方で書き、遅いと分かってから測って直す

全部、1つのことの言い換えです。

サブクエリは「先に実行された別の SELECT」。 それが1回なのか、行の数だけなのか。 見るべきはそこだけ。

EXPLAIN を1回打てば、どちらかは必ず分かります。

参考リンク


次に読む

この記事は 【深掘り】SQL の 9 / 17 本目です。

記事
← 前の記事SQLの組み込み関数 — CONCATにNULL1つで全部消える
→ 次の記事ウィンドウ関数 — GROUP BYだと名前が消える

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Subqueries
  • サブクエリを書ける4つの場所と返る形
  • 相関サブクエリ / IN・EXISTS・NOT IN の使い分け

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次