SELECTの実行順序とNULL — WHEREで別名が使えない

SELECTの実行順序とNULL。順に並ぶ処理カードと空白のデータカード、確認用の虫眼鏡。

WHEREで別名が使えないのは、実行順序が書いた順ではないからです。順序とNULLの扱いを確かめます。

前回: プロセスを止めても消えない場所 — MySQLとSQLの基本

これは【深掘り】です。本線の順路ではありません。 本線はMySQLとSQLの基本の回から先へ進んでいます。この記事はその回の続きで、SELECT だけを1本かけて掘り直すものです。飛ばしても本線は繋がります。詰まったときに戻ってきてください。

作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて、appdb というデータベースがある状態から始めます。

本線の回で、これを打ちました。

SELECT * FROM tasks;

表が出ました。それで「SQLが書けた」ことになっています。

でも、こう聞かれたら答えられませんでした。

  • なぜ、この順番で返ってきたのか(ORDER BY を書いていないのに、なぜ並んでいるように見えるのか)
  • WHERE done = NULL と書いたのに、1行も返ってこないのはなぜか(NULL の行はあるのに)
  • SELECT salary * 12 AS 年収 と名前を付けたのに、WHERE 年収 > ... と書くとエラーになるのはなぜか

3つとも「書き方を間違えた」ではありません。 3つとも、SQLがどういう順番で仕事をしているかを知らない、という同じ1つの原因から出ています。

この記事は、その順番を見にいきます。

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

目次

この記事でわかること

  • SQLは、書いた順には実行されないこと。FROM から始まって SELECT は後ろのほうだということ。だから WHERE では列の別名が使えず、ORDER BY では使える
  • NULL は「空っぽの値」ではなく「値がないという状態」だということ。だから = NULL はどう転んでも真にならない
  • NOT IN の中に NULL が1つ混ざると、結果が丸ごと0行になること。これは誰でも1回はやる
  • ORDER BY で同じ値が並んだとき、その中の順序は保証されていないこと。LIMIT と組むと、ページごとに結果が入れ替わる
  • LIMIT 20 OFFSET 100000 は「100000行目まで飛ぶ」のではなく「100020行作って100000行捨てる」こと

この記事でやること

  1. 練習用のテーブルを2つ作る(d_dept と d_emp、60行、NULL 入り)。以降の深掘りでも使い回す
  2. WHERE で列の別名を使って、わざとエラーを出す。 そのうえで ORDER BY では通ることを確かめる
  3. = NULL を打って0行を見る。 そのあと NOT IN に NULL を混ぜて、結果が消えるのを見る
  4. ORDER BY を複数列・混在・式で書き分ける。 同値のときに順序が保証されないことを確かめる
  5. LIMIT / OFFSET でページをめくり、行がずれる事故を再現して、キーセットページネーションに直す

先に知る言葉

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

言葉意味
句(クエリ節)SELECT FROM WHERE のような、SQL文を組み立てる部品ひとつ分。「WHERE句」のように呼ぶ
論理的な実行順序「SQLがどの句から順に意味を決めていくか」の決まり。 書いた順とは違う
別名(エイリアス)AS で列や表に付ける別の名前。salary * 12 AS annual の annual
NULL「値が入っていない」という印。 0でも空文字でもない。「不明」に近い
三値論理条件の答えが真・偽・不明の3つあるという考え方。SQLはこれで動いている
述語= IN LIKE のような、真か偽かを判定する記号や語
索引(インデックス)「この値ならこの行」の早見表。 本の巻末の索引と同じ
ページネーション一覧を1ページ分ずつ小分けにして返すこと
キーセットページネーション「前のページの最後の値より先」で次を取るページのめくり方。 OFFSET を使わない

① 練習用のデータを作る

ここでちゃんと作っておきます。 SELECT の話は、行が3行しかない表では何も見えません。 NULL も、同じ値の重複も、並べ替えも、ある程度の数がないと現象が起きないからです。

触るのは d_ で始まる表だけにする

本線で作った tasks には一切触りません。

深掘り用の練習テーブルは、すべて d_ で始めることにします(d は deep のつもりです)。こうしておくと、

  • SHOW TABLES; を打ったときに、練習用がひと目で分かる
  • 消したくなったときに、tasks を巻き込まない

名前の規則を先に決めておくのは、DROP の事故を防ぐためです。 本線の回で「WHERE を忘れた DELETE は戻せない」と書きましたが、DROP TABLE はもっと戻せません。

管理者で入る

練習用の表を作るので、appuser ではなく管理者で入ります。

bash
$ sudo mysql

appuser には CREATE TABLE の権限を渡していません(本線の回でわざと外しました)。ここで appuser を使うと、次の行で止まります。

ERROR 1142 (42000): CREATE command denied to user 'appuser'@'localhost'

これは正常です。 権限が効いている証拠なので、appuser に CREATE を足したりしないでください。 sudo mysql で作れば済みます。

USE appdb;

表を2つ作る

DROP TABLE IF EXISTS d_emp;
DROP TABLE IF EXISTS d_dept;

CREATE TABLE d_dept (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(40) NOT NULL,
  floor_no  INT
);

CREATE TABLE d_emp (
  emp_id     INT PRIMARY KEY,
  emp_name   VARCHAR(40) NOT NULL,
  dept_id    INT,
  hire_date  DATE NOT NULL,
  salary     INT NOT NULL,
  bonus      INT,
  email      VARCHAR(80),
  manager_id INT
);

NULL を許す列と、許さない列を分けてあります。 ここが今日の主役です。

列NULL を許すかなぜ
emp_name salary hire_date許さない(NOT NULL)名前も給与も入社日も、無い社員はいない
dept_id許すまだ配属が決まっていない人がいる
bonus許す「支給なし」ではなく「まだ決まっていない」を表したい
email許す持っていない人がいる
manager_id許す社長には上司がいない

bonus を「0」ではなく NULL にしているのは意図的です。 0円支給とまだ決まっていないは、別のことです。ここを両方0にしてしまうと、あとから区別できません。 表の設計として NULL をどう使うかはテーブル設計と正規化の回の話です。この記事は「入ってしまった NULL をどう読むか」だけを扱います。

DROP TABLE IF EXISTS を先に置いてあるのは、何度でもやり直せるようにするためです。順番に意味があります(d_emp が d_dept を指す設計にする回もあるので、子から先に消す癖を付けます)。

データを入れる

部署が8つ。

INSERT INTO d_dept (dept_id, dept_name, floor_no) VALUES
  (1, '営業部', 3),
  (2, '開発部', 5),
  (3, '人事部', 2),
  (4, '経理部', 2),
  (5, '情報システム部', 5),
  (6, '品質管理部', 4),
  (7, '広報部', NULL),
  (8, '新規事業室', NULL);

社員が60人です。長いですが、全部貼ってください。 途中で切ると、あとの節の件数が合いません。

INSERT INTO d_emp (emp_id, emp_name, dept_id, hire_date, salary, bonus, email, manager_id) VALUES
  (1, '高橋 誠', 1, '2012-04-01', 620000, 900000, 'takahashi@example.com', NULL),
  (2, '佐藤 健一', 2, '2011-04-01', 680000, 1100000, 'sato.k@example.com', NULL),
  (3, '田中 由紀', 3, '2013-04-01', 540000, 700000, 'tanaka.y@example.com', NULL),
  (4, '鈴木 大輔', 4, '2010-10-01', 600000, 800000, 'suzuki.d@example.com', NULL),
  (5, '渡辺 明', 5, '2014-04-01', 650000, 850000, 'watanabe@example.com', NULL),
  (6, '伊藤 香織', 6, '2015-04-01', 560000, 600000, 'ito.k@example.net', NULL),
  (7, '山本 拓也', 1, '2016-04-01', 430000, 300000, 'yamamoto.t@example.com', 1),
  (8, '中村 彩', 1, '2017-04-01', 410000, 280000, 'nakamura.a@example.com', 1),
  (9, '小林 翔', 1, '2018-04-01', 390000, NULL, 'kobayashi.s@example.com', 1),
  (10, '加藤 美咲', 1, '2019-04-01', 370000, 200000, NULL, 1),
  (11, '吉田 亮', 1, '2020-04-01', 350000, NULL, 'yoshida.r@example.net', 1),
  (12, '山田 優', 1, '2021-04-01', 330000, 150000, 'yamada.y@example.com', 1),
  (13, '佐々木 涼', 1, '2022-04-01', 310000, NULL, NULL, 1),
  (14, '松本 真', 1, '2023-04-01', 295000, 100000, 'matsumoto.m@example.com', 1),
  (15, '井上 直樹', 2, '2015-04-01', 520000, 500000, 'inoue.n@example.com', 2),
  (16, '木村 隆', 2, '2016-04-01', 500000, 480000, 'kimura.t@example.com', 2),
  (17, '林 さくら', 2, '2017-04-01', 470000, 420000, 'hayashi.s@example.net', 2),
  (18, '清水 剛', 2, '2018-04-01', 450000, NULL, 'shimizu.g@example.com', 2),
  (19, '山口 恵', 2, '2019-04-01', 430000, 380000, 'yamaguchi.m@example.com', 2),
  (20, '池田 陽子', 2, '2019-10-01', 430000, 360000, NULL, 2),
  (21, '阿部 諒', 2, '2020-04-01', 400000, NULL, 'abe.r@example.com', 2),
  (22, '森 大地', 2, '2021-04-01', 380000, 250000, 'mori.d@example.net', 2),
  (23, '橋本 千夏', 2, '2021-10-01', 380000, 240000, 'hashimoto.c@example.com', 2),
  (24, '石川 優斗', 2, '2022-04-01', 350000, NULL, 'ishikawa.y@example.com', 2),
  (25, '前田 蓮', 2, '2023-04-01', 320000, 120000, NULL, 2),
  (26, '藤田 結衣', 2, '2024-04-01', 300000, NULL, 'fujita.y@example.com', 2),
  (27, '岡田 修', 3, '2016-04-01', 460000, 400000, 'okada.o@example.com', 3),
  (28, '長谷川 舞', 3, '2018-04-01', 420000, 330000, 'hasegawa.m@example.net', 3),
  (29, '村上 健太', 3, '2020-04-01', 370000, NULL, 'murakami.k@example.com', 3),
  (30, '近藤 静', 3, '2022-04-01', 330000, 180000, NULL, 3),
  (31, '石井 裕', 4, '2014-04-01', 490000, 450000, 'ishii.y@example.com', 4),
  (32, '坂本 麻衣', 4, '2016-10-01', 450000, 390000, 'sakamoto.m@example.com', 4),
  (33, '遠藤 淳', 4, '2018-04-01', 410000, NULL, 'endo.j@example.net', 4),
  (34, '青木 千鶴', 4, '2020-04-01', 360000, 220000, 'aoki.c@example.com', 4),
  (35, '藤井 涼太', 4, '2023-04-01', 310000, NULL, NULL, 4),
  (36, '福田 智', 5, '2015-10-01', 530000, 520000, 'fukuda.s@example.com', 5),
  (37, '三浦 亜紀', 5, '2017-04-01', 480000, 440000, 'miura.a@example.com', 5),
  (38, '岡本 隼', 5, '2018-10-01', 460000, 410000, 'okamoto.h@example.net', 5),
  (39, '松田 望', 5, '2019-04-01', 440000, NULL, 'matsuda.n@example.com', 5),
  (40, '中川 圭', 5, '2020-10-01', 400000, 290000, NULL, 5),
  (41, '原田 綾', 5, '2021-04-01', 390000, 270000, 'harada.a@example.com', 5),
  (42, '小野 竜也', 5, '2022-04-01', 360000, NULL, 'ono.t@example.net', 5),
  (43, '田村 咲希', 5, '2023-10-01', 330000, 140000, 'tamura.s@example.com', 5),
  (44, '竹内 陸', 5, '2024-04-01', 305000, NULL, NULL, 5),
  (45, '金子 亮平', 6, '2016-04-01', 470000, 430000, 'kaneko.r@example.com', 6),
  (46, '和田 沙織', 6, '2018-04-01', 430000, 370000, 'wada.s@example.com', 6),
  (47, '中山 悠', 6, '2019-10-01', 400000, NULL, 'nakayama.y@example.net', 6),
  (48, '石田 奈々', 6, '2021-04-01', 370000, 230000, 'ishida.n@example.com', 6),
  (49, '上田 諒太', 6, '2022-10-01', 345000, NULL, NULL, 6),
  (50, '森田 楓', 6, '2024-04-01', 300000, 110000, 'morita.k@example.com', 6),
  (51, '原 拓海', 7, '2019-04-01', 420000, 340000, 'hara.t@example.com', 1),
  (52, '柴田 詩織', 7, '2021-04-01', 370000, NULL, 'shibata.s@example.net', 1),
  (53, '酒井 玲', 7, '2023-04-01', 320000, 130000, NULL, 1),
  (54, '工藤 誠司', 8, '2022-04-01', 480000, NULL, 'kudo.s@example.com', NULL),
  (55, '宮崎 志保', 8, '2023-04-01', 400000, 260000, 'miyazaki.s@example.com', 54),
  (56, '内田 光', 8, '2024-04-01', 340000, NULL, 'uchida.h@example.net', 54),
  (57, '新井 蒼', NULL, '2025-04-01', 290000, NULL, 'arai.a@example.com', NULL),
  (58, '平野 紗希', NULL, '2025-04-01', 290000, NULL, NULL, NULL),
  (59, '菅原 陽向', NULL, '2025-10-01', 285000, NULL, 'sugawara.h@example.com', NULL),
  (60, '大島 芽衣', NULL, '2025-10-01', 285000, NULL, NULL, NULL);

入ったか確かめる

この記事は「打ったら件数を見る」を全部の節でやります。 ここが最初の1回目です。

SELECT COUNT(*) FROM d_emp;
SELECT COUNT(*) FROM d_dept;
+----------+
| COUNT(*) |
+----------+
|       60 |
+----------+

60 と 8 です。 ここが違っていたら、INSERT が途中で切れています。先に進まないで、DELETE FROM d_emp; してから貼り直してください。

NULL がどこにいくつあるかも、先に数えておきます。

SELECT
  COUNT(*)                                    AS 全行,
  SUM(bonus IS NULL)                          AS bonusがNULL,
  SUM(email IS NULL)                          AS emailがNULL,
  SUM(dept_id IS NULL)                        AS 部署未定,
  SUM(manager_id IS NULL)                     AS 上司なし
FROM d_emp;
+--------+-------------+-------------+----------+----------+
| 全行   | bonusがNULL | emailがNULL | 部署未定 | 上司なし |
+--------+-------------+-------------+----------+----------+
|     60 |          22 |          12 |        4 |       11 |
+--------+-------------+-------------+----------+----------+

この5つの数字を、手元に控えておいてください。 これから何度も突き合わせます。

SUM(bonus IS NULL) が数えられるのはなぜか。 MySQL では bonus IS NULL という条件式そのものが 1 か 0 の値になります。 だから合計すると「当てはまった行数」になります。 COUNT() ではなくて SUM() を使っているのがポイントです。COUNT() は「NULL でない値の個数」を数えるので、ここでは使えません。

[スクリーンショット: 01_練習データ投入.png] SELECT COUNT(*) FROM d_emp; が 60 を返している画面を撮ってください。その上に USE appdb; が写っていると、どのデータベースで作業しているかが読者に伝わります。

② SQLは、書いた順に実行されない

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

実際に順番を見る

私たちは、こう書きます。

SELECT   emp_name, salary
FROM     d_emp
WHERE    dept_id = 2
ORDER BY salary DESC
LIMIT    3;

でも、データベースはこの順では読んでいません。 実際に意味を決めていく順番は、こうです。

順句そこで何が決まるか
1FROMどの表を使うか。 ここで「対象の行の集まり」ができる
2WHERE行を捨てる。 条件に合わない行がここで消える
3GROUP BY残った行をグループにまとめる
4HAVINGグループを捨てる(WHERE は行を、HAVING はグループを捨てる)
5SELECTどの列を出すか決める。 ここで初めて別名(AS)が付く
6ORDER BY並べ替える
7LIMIT上から何行かだけ残す

書く順と、実行される順が違います。

書く順:   SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
実行の順: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

SELECT が真ん中より後ろにいる、というのが全部です。

なぜこの順なのか、理屈は単純です。 並べ替える前に、何を並べるかが決まっていないといけません。 だから WHERE が ORDER BY より先。 どの列を出すかを決める前に、どの行を出すかが決まっていないといけません。 だから WHERE が SELECT より先。 「後の工程は、前の工程の結果しか見られない」という、それだけの話です。

だから WHERE では別名が使えない

打って確かめます。

SELECT emp_name, salary * 12 AS annual_salary
FROM   d_emp
WHERE  annual_salary > 6000000;
ERROR 1054 (42S22): Unknown column 'annual_salary' in 'where clause'

「そんな列は無い」と言われました。 すぐ上の行で作ったばかりなのに、です。

理由は順番です。 WHERE が動く時点では、SELECT はまだ実行されていません。 annual_salary という名前は SELECT が付けるものなので、WHERE の世界にはまだ存在していません。

直し方は2つです。

-- ① 式をそのまま書き直す
SELECT emp_name, salary * 12 AS annual_salary
FROM   d_emp
WHERE  salary * 12 > 6000000
ORDER BY annual_salary DESC;
+-----------+---------------+
| emp_name  | annual_salary |
+-----------+---------------+
| 佐藤 健一 |       8160000 |
| 渡辺 明   |       7800000 |
| 高橋 誠   |       7440000 |
| 鈴木 大輔 |       7200000 |
| 伊藤 香織 |       6720000 |
| 田中 由紀 |       6480000 |
| 福田 智   |       6360000 |
| 井上 直樹 |       6240000 |
+-----------+---------------+
8 rows in set

8行返りました。

もう1つの直し方は、SELECT を先に終わらせてしまうことです。

-- ② いったん結果を作って、その外側で絞る
SELECT *
FROM (
  SELECT emp_name, salary * 12 AS annual_salary
  FROM   d_emp
) AS t
WHERE t.annual_salary > 6000000
ORDER BY t.annual_salary DESC;

同じ8行が返ります。

内側の SELECT が先に終わっているので、外側から見ると annual_salary は「もうある列」です。順番の問題なので、順番を作ってやれば解決します。

②のような、FROM の中に書く SELECT を「派生表(サブクエリ)」と呼びます。 今は「そういう逃げ道がある」だけ覚えておいてください。 ①のほうが速いことが多いので、まずは①です。

ORDER BY では別名が使える

同じ別名なのに、ORDER BY では通ります。

SELECT emp_name, salary * 12 AS annual_salary
FROM   d_emp
ORDER BY annual_salary DESC
LIMIT 3;
+-----------+---------------+
| emp_name  | annual_salary |
+-----------+---------------+
| 佐藤 健一 |       8160000 |
| 渡辺 明   |       7800000 |
| 高橋 誠   |       7440000 |
+-----------+---------------+
3 rows in set

エラーになりません。

理由も順番です。 ORDER BY が動く時点では、SELECT はもう終わっています。 だから annual_salary という名前がすでに存在しています。

「WHERE では使えないのに ORDER BY では使える」は、覚える知識ではありません。 実行順の表を1回見れば、二度と迷いません。 SELECT が WHERE の後ろで ORDER BY の前にいる。それだけです。

同じ理由で、こういうことも起きます。

書き方どうなるかなぜ
WHERE に別名エラーSELECT がまだ動いていない
WHERE に集計関数(WHERE COUNT(*) > 3)エラーGROUP BY がまだ動いていない
HAVING に集計関数通るGROUP BY の後ろだから
ORDER BY に別名通るSELECT の後ろだから
ORDER BY に、SELECT に出していない列通る行そのものはまだ手元にあるから

最後の1つを確かめておきます。

SELECT emp_name
FROM   d_emp
WHERE  dept_id = 5
ORDER BY salary DESC
LIMIT 3;
+-----------+
| emp_name  |
+-----------+
| 渡辺 明   |
| 福田 智   |
| 三浦 亜紀 |
+-----------+
3 rows in set

salary を出していないのに、salary で並べられました。 出す列を選ぶことと、並べ替えに使える列は、別の話です。

[スクリーンショット: 02_別名エラー.png] WHERE annual_salary > 6000000 で ERROR 1054 が出ている画面と、ORDER BY annual_salary DESC が通っている画面を、続けて1枚に収めて撮ってください。同じ別名なのに片方だけ落ちるのが見えることが条件です。

③ NULL は値ではない

もう1つの山です。こちらのほうが事故につながります。

= NULL は、何も返さない

SELECT emp_id, emp_name, bonus FROM d_emp WHERE bonus = NULL;
Empty set (0.00 sec)

0行です。 さっき数えたとおり、bonus が NULL の行は22行あります。 それでも0行です。

「NULL と等しい行」は、絶対に存在しません。

NULL は「値が無い」ではなく「値が分からない」と読むと、腑に落ちます。 「分からないもの と 分からないもの は等しいか?」——分かりません。 SQLの答えは「真でも偽でもなく、不明(UNKNOWN)」です。そして WHERE は「真」の行しか返しません。 不明は落とされます。

これが三値論理です。 ふつうのプログラミング言語は真と偽の2つですが、SQLは3つあります。

式答え
1 = 1真
1 = 2偽
NULL = NULL不明
NULL <> NULL不明
NULL = 0不明
NULL IS NULL真

MySQL で直接見られます。

SELECT 1 = 1, 1 = 2, NULL = NULL, NULL <> NULL, NULL IS NULL;
+-------+-------+-------------+--------------+--------------+
| 1 = 1 | 1 = 2 | NULL = NULL | NULL <> NULL | NULL IS NULL |
+-------+-------+-------------+--------------+--------------+
|     1 |     0 |        NULL |         NULL |            1 |
+-------+-------+-------------+--------------+--------------+

NULL = NULL の答えが NULL になっています。「不明」が返ってきているわけです。

正しくは IS NULL

SELECT COUNT(*) FROM d_emp WHERE bonus IS NULL;
+----------+
| COUNT(*) |
+----------+
|       22 |
+----------+

22行。さっき数えた数字と一致します。

SELECT COUNT(*) FROM d_emp WHERE bonus IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
|       38 |
+----------+

22 + 38 = 60。 ここは合います。

ここからが罠です:条件を否定しても、足して全部にならない

「ボーナスが30万を超える人」と「超えない人」を数えてみます。

SELECT COUNT(*) FROM d_emp WHERE bonus > 300000;
SELECT COUNT(*) FROM d_emp WHERE bonus <= 300000;

21 と 17 です。合計38。 60ではありません。

消えた22行は、bonus が NULL の行です。 NULL > 300000 も NULL <= 300000 も、両方とも「不明」なので、どちらの結果にも入りません。

同じことが NOT でも起きます。

SELECT COUNT(*) FROM d_emp WHERE NOT (bonus > 300000);
+----------+
| COUNT(*) |
+----------+
|       17 |
+----------+

NOT を付けても39にはなりません。 「不明」を否定しても「不明」のままだからです。

「条件Aの件数」と「条件Aでないものの件数」を足したら全件になる、という感覚は、SQLでは成り立ちません。 NULL を含む列で件数が合わないときは、まずこれを疑ってください。

全件にしたいなら、NULL を明示的に拾います。

SELECT COUNT(*) FROM d_emp WHERE bonus <= 300000 OR bonus IS NULL;
+----------+
| COUNT(*) |
+----------+
|       39 |
+----------+

21 + 39 = 60。 これで合いました。

必ず1回はハマる:NOT IN に NULL が混ざると、全部消える

この記事で、いちばん覚えて帰ってほしいところです。

「誰の上司でもない人(=部下がいない人)を出したい」 とします。素直に書くと、こうなります。

SELECT COUNT(*)
FROM   d_emp
WHERE  emp_id NOT IN (SELECT manager_id FROM d_emp);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

0行です。 そんなはずはありません。60人のうち、部下がいる人は7人しかいません。

しかも、エラーは出ていません。 「該当者なし」というまともに見える嘘が返ってきます。これが一番たちが悪いところです。

なぜこうなるか。NOT IN は、内部ではこう展開されます。

emp_id <> 1 AND emp_id <> 2 AND ... AND emp_id <> NULL AND ...

manager_id には NULL が11個入っていました。 つまり emp_id <> NULL という項が混ざります。 これは「不明」です。

そして AND の規則はこうです。

式答え
真 AND 真真
真 AND 不明不明
偽 AND 不明偽

どの行も、最後に「不明」と AND を取ることになります。 真にはなれません。だから、どの行も返らない。

確かめます。

SELECT COUNT(*) FROM d_emp WHERE manager_id IS NULL;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+

11個の NULL が、60行全部を消していました。

直し方は、NULL を先に落とすことです。

SELECT COUNT(*)
FROM   d_emp
WHERE  emp_id NOT IN (SELECT manager_id FROM d_emp WHERE manager_id IS NOT NULL);
+----------+
| COUNT(*) |
+----------+
|       53 |
+----------+

53人。60 – 7 = 53 で、合っています。

NOT IN の中に副問い合わせを書いたら、WHERE ... IS NOT NULL を必ず付ける。 これは「気をつける」で防げません。手癖にしてください。

IN(否定なし)のほうは、この問題が起きません。 IN は OR で繋がるので、「不明」があっても、どれか1つが真なら真になるからです。危ないのは NOT IN だけです。

COALESCE と IFNULL — NULL を別の値に置き換える

表示のときは、NULL のままだと読みにくいことがあります。

SELECT emp_id, emp_name, bonus, COALESCE(bonus, 0) AS bonus表示
FROM   d_emp
WHERE  emp_id BETWEEN 9 AND 13;
+--------+------------+--------+-----------+
| emp_id | emp_name   | bonus  | bonus表示 |
+--------+------------+--------+-----------+
|      9 | 小林 翔    |   NULL |         0 |
|     10 | 加藤 美咲  | 200000 |    200000 |
|     11 | 吉田 亮    |   NULL |         0 |
|     12 | 山田 優    | 150000 |    150000 |
|     13 | 佐々木 涼  |   NULL |         0 |
+--------+------------+--------+-----------+
5 rows in set
関数意味
IFNULL(a, b)a が NULL なら b、そうでなければ a。 引数は2つだけ。MySQL 専用
COALESCE(a, b, c, ...)左から見て、最初に NULL でないものを返す。 いくつでも並べられる。標準SQL

COALESCE を覚えておけば足ります。 名前は長いですが、PostgreSQL でも SQLite でも同じ名前で動きます(PostgreSQL の回で移し替えるときに効きます)。

計算に使うと、はっきり効きます。

SELECT emp_name, salary * 12 + bonus              AS 間違い,
                 salary * 12 + COALESCE(bonus, 0) AS 正しい
FROM   d_emp
WHERE  emp_id IN (9, 10);
+-----------+---------+---------+
| emp_name  | 間違い  | 正しい  |
+-----------+---------+---------+
| 小林 翔   |    NULL | 4680000 |
| 加藤 美咲 | 4640000 | 4640000 |
+-----------+---------+---------+

NULL を含む足し算の答えは NULL です。 0として足されるのではありません。 年収の合計を出したら1人だけ NULL になっていた、というのは、この形です。

ただし、COALESCE を WHERE に書くと索引が効かなくなる

-- 索引が効かない書き方
SELECT COUNT(*) FROM d_emp WHERE COALESCE(bonus, 0) <= 300000;
-- 索引が効く書き方
SELECT COUNT(*) FROM d_emp WHERE bonus <= 300000 OR bonus IS NULL;

どちらも39行返ります。 ただし、上は bonus に索引を張っても使われません。

理由は、列を関数で包んでしまうと「その列の値」ではなくなるからです。索引は「列の値」で作られた早見表なので、加工した結果は載っていません。

この話はパフォーマンス改善とEXPLAINの回の内容です。 ここでは「WHERE の左側で列を加工したら負け」とだけ覚えてください。

ORDER BY で NULL はどこに来るか

MySQL には、はっきりした決まりがあります。

SELECT emp_id, emp_name, bonus FROM d_emp ORDER BY bonus ASC LIMIT 5;
+--------+------------+-------+
| emp_id | emp_name   | bonus |
+--------+------------+-------+
|      9 | 小林 翔    |  NULL |
|     11 | 吉田 亮    |  NULL |
|     13 | 佐々木 涼  |  NULL |
|     18 | 清水 剛    |  NULL |
|     21 | 阿部 諒    |  NULL |
+--------+------------+-------+
SELECT emp_id, emp_name, bonus FROM d_emp ORDER BY bonus DESC LIMIT 5;
+--------+-----------+---------+
| emp_id | emp_name  | bonus   |
+--------+-----------+---------+
|      2 | 佐藤 健一 | 1100000 |
|      1 | 高橋 誠   |  900000 |
|      5 | 渡辺 明   |  850000 |
|      4 | 鈴木 大輔 |  800000 |
|      3 | 田中 由紀 |  700000 |
+--------+-----------+---------+
並べ方NULL はどこ
ORDER BY bonus ASC先頭(NULL は最小として扱われる)
ORDER BY bonus DESC末尾

これは MySQL の決まりで、標準SQLではありません。 PostgreSQL は逆で、ASC のとき NULL が末尾に来ます。 移植したら並び順が変わる、という形で刺さります。

位置を自分で決めたいときは、こう書きます。

SELECT emp_id, emp_name, bonus
FROM   d_emp
ORDER BY (bonus IS NULL) ASC, bonus ASC
LIMIT 5;
+--------+-----------+--------+
| emp_id | emp_name  | bonus  |
+--------+-----------+--------+
|     14 | 松本 真   | 100000 |
|     50 | 森田 楓   | 110000 |
|     25 | 前田 蓮   | 120000 |
|     53 | 酒井 玲   | 130000 |
|     43 | 田村 咲希 | 140000 |
+--------+-----------+--------+

bonus IS NULL は 0 か 1 になるので、ASC で並べると 0(=NULL でない行)が先に来ます。 これで NULL を最後に押しやれます。

MySQL には NULLS LAST という書き方がありません。 この2段構えが定番です。

[スクリーンショット: 03_NOT_INの罠.png] NOT IN (SELECT manager_id FROM d_emp) が 0 を返している画面と、IS NOT NULL を足して 53 になった画面を1枚に収めて撮ってください。エラーが出ずに0件になるのが伝わることが条件です。

④ WHERE の演算子を並べる

ここは道具の紹介です。全部、件数を確かめながら進みます。

BETWEEN — 両端を含む

SELECT COUNT(*) FROM d_emp WHERE salary BETWEEN 350000 AND 450000;
+----------+
| COUNT(*) |
+----------+
|       27 |
+----------+

BETWEEN a AND b は a <= x AND x <= b と同じです。両端を含みます。

日付で使うときだけ、罠があります。 hire_date BETWEEN '2020-01-01' AND '2021-12-31' は問題ありませんが、DATETIME の列に対して同じことをすると、12月31日の 00:00:01 以降が落ちます。 日時は >= 開始 AND < 翌日 で書くのが安全です。これは日付と時刻の回の話です。

SELECT COUNT(*) FROM d_emp WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+

IN — 「どれか」

SELECT COUNT(*) FROM d_emp WHERE dept_id IN (1, 2, 5);
+----------+
| COUNT(*) |
+----------+
|       32 |
+----------+

IN (1, 2, 5) は dept_id = 1 OR dept_id = 2 OR dept_id = 5 と同じです。読みやすいので、OR を3つ並べるより IN を使います。

否定も打っておきます。

SELECT COUNT(*) FROM d_emp WHERE dept_id NOT IN (1, 2, 5);
+----------+
| COUNT(*) |
+----------+
|       24 |
+----------+

60 – 32 = 28 のはずなのに、24 です。 4行足りません。

さっきの話がここでも出ています。 dept_id が NULL の4人(57〜60番)が、IN にも NOT IN にも入っていません。

SELECT COUNT(*) FROM d_emp WHERE dept_id NOT IN (1, 2, 5) OR dept_id IS NULL;
+----------+
| COUNT(*) |
+----------+
|       28 |
+----------+

28。合いました。

LIKE — 部分一致

記号意味
%0文字以上の任意の文字列
_ちょうど1文字
SELECT COUNT(*) FROM d_emp WHERE emp_name LIKE '%田%';
+----------+
| COUNT(*) |
+----------+
|       17 |
+----------+
SELECT COUNT(*) FROM d_emp WHERE email LIKE '%@example.net';
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+

email が NULL の12人は、LIKE でも落ちています。 NULL LIKE '%...' も「不明」です。もう驚かないはずです。

LIKE の大文字小文字は、照合順序(コレーション)で決まります。 本線の回で utf8mb4_unicode_ci を指定しました。末尾の ci は case insensitive(大文字小文字を区別しない)という意味です。だから LIKE '%EXAMPLE%' でも当たります。 区別させたいなら、列や比較の照合順序を変える必要があります。 詳しくは文字コードの回を見てください。

LIKE '%x%' は索引が効かない

LIKE には、必ず知っておくべき制限があります。

書き方索引
LIKE 'abc%'(前方一致)効く
LIKE '%abc'(後方一致)効かない
LIKE '%abc%'(中間一致)効かない

理由は、索引が「前から順に並んだ早見表」だからです。

電話帳を想像してください。 「た から始まる人」は探せます。「た を含む人」は、1ページ目から全部読むしかありません。

索引もこれと同じ構造です。 先頭の文字が分からないと、どこから探せばいいかが決まりません。

「じゃあ、名前の部分一致検索はどうするのか」——それが全文検索の回の話です。 LIKE '%キーワード%' で検索機能を作ると、データが増えた瞬間に止まります。 60行なら一瞬ですが、100万行だと全部読みます。 どのくらい遅くなるかを実際に測るのはパフォーマンス改善の回です。

REGEXP — 正規表現

LIKE で足りないときに使います。

SELECT COUNT(*) FROM d_emp WHERE emp_name REGEXP '^(山|田)';
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+

「山」か「田」で始まる人が5人です(山本・山田・山口・田中・田村)。

SELECT COUNT(*) FROM d_emp WHERE email REGEXP 'example[.]net$';
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
記号意味
^先頭
$末尾
<code>&#124;</code>または
[...]この中のどれか1文字

REGEXP は、LIKE よりさらに索引が効きません。 例外なく全行を読みます。 「LIKE で書けることは LIKE で書く」が原則です。REGEXP は、管理者が手で調べるときの道具だと思っておくのが安全です。 なお、正規表現そのものの中身は正規表現エンジンを自作する回で分解しています。

⑤ ORDER BY を掘る

複数の列で並べる

SELECT emp_id, dept_id, emp_name, salary
FROM   d_emp
WHERE  dept_id IN (3, 4)
ORDER BY dept_id ASC, salary DESC;
+--------+---------+------------+--------+
| emp_id | dept_id | emp_name   | salary |
+--------+---------+------------+--------+
|      3 |       3 | 田中 由紀  | 540000 |
|     27 |       3 | 岡田 修    | 460000 |
|     28 |       3 | 長谷川 舞  | 420000 |
|     29 |       3 | 村上 健太  | 370000 |
|     30 |       3 | 近藤 静    | 330000 |
|      4 |       4 | 鈴木 大輔  | 600000 |
|     31 |       4 | 石井 裕    | 490000 |
|     32 |       4 | 坂本 麻衣  | 450000 |
|     33 |       4 | 遠藤 淳    | 410000 |
|     34 |       4 | 青木 千鶴  | 360000 |
|     35 |       4 | 藤井 涼太  | 310000 |
+--------+---------+------------+--------+
11 rows in set

部署の昇順で、その中では給与の降順。 ASC と DESC は列ごとに指定します。

ORDER BY dept_id, salary DESC と書くと、DESC は salary にしか掛かりません。 dept_id は ASC(省略時の既定)です。「まとめて DESC」にはなりません。 両方降順にしたいなら ORDER BY dept_id DESC, salary DESC と両方書きます。

式で並べる

列そのものではなく、計算した結果で並べられます。

SELECT emp_name, salary, bonus,
       salary * 12 + COALESCE(bonus, 0) AS 年収
FROM   d_emp
ORDER BY 年収 DESC
LIMIT 5;
+-----------+--------+---------+---------+
| emp_name  | salary | bonus   | 年収    |
+-----------+--------+---------+---------+
| 佐藤 健一 | 680000 | 1100000 | 9260000 |
| 渡辺 明   | 650000 |  850000 | 8650000 |
| 高橋 誠   | 620000 |  900000 | 8340000 |
| 鈴木 大輔 | 600000 |  800000 | 8000000 |
| 伊藤 香織 | 560000 |  600000 | 7320000 |
+-----------+--------+---------+---------+

COALESCE を入れてあるのが効いています。 入れないと、ボーナス未定の22人が全員 NULL になり、DESC では末尾に固まります。

ORDER BY 1 のように「何番目の列か」で書くこともできます。 やめてください。 SELECT の列を1つ足しただけで、黙って並び順が変わります。 名前か式で書きます。

同じ値のとき、順序は保証されていない

ここが盲点です。

SELECT emp_id, emp_name, salary FROM d_emp WHERE salary = 430000;
+--------+-----------+--------+
| emp_id | emp_name  | salary |
+--------+-----------+--------+
|      7 | 山本 拓也 | 430000 |
|     19 | 山口 恵   | 430000 |
|     20 | 池田 陽子 | 430000 |
|     46 | 和田 沙織 | 430000 |
+--------+-----------+--------+
4 rows in set

給与が同じ人が4人います。 ここで、

SELECT emp_id, emp_name FROM d_emp ORDER BY salary DESC LIMIT 10;

と打つと、この4人が「どういう順で」出てくるかは決まっていません。

手元で試すと、たいてい同じ順に見えます。 それでも保証はありません。 変わる条件は、

  • 索引を1本張っただけで変わる
  • データを足しただけで変わる
  • MySQL のバージョンを上げただけで変わる

「同じ値のときの順序」を、実行するたびに同じにしたいなら、必ず一意な列を最後に足してください。

SELECT emp_id, emp_name, salary
FROM   d_emp
ORDER BY salary DESC, emp_id ASC
LIMIT 10;

emp_id は主キーなので、重複しません。 これを足せば、並び順が完全に1つに決まります。

これを「決定的な(deterministic)並び順」と言います。 そして、次の節の LIMIT と組み合わせたときに、これが必須になります。

⑥ LIMIT と OFFSET

基本

SELECT emp_id, emp_name FROM d_emp ORDER BY emp_id LIMIT 5;
SELECT emp_id, emp_name FROM d_emp ORDER BY emp_id LIMIT 5 OFFSET 5;
(1ページ目)
+--------+-----------+
| emp_id | emp_name  |
+--------+-----------+
|      1 | 高橋 誠   |
|      2 | 佐藤 健一 |
|      3 | 田中 由紀 |
|      4 | 鈴木 大輔 |
|      5 | 渡辺 明   |
+--------+-----------+

(2ページ目)
+--------+-----------+
| emp_id | emp_name  |
+--------+-----------+
|      6 | 伊藤 香織 |
|      7 | 山本 拓也 |
|      8 | 中村 彩   |
|      9 | 小林 翔   |
|     10 | 加藤 美咲 |
+--------+-----------+

OFFSET n は「最初の n 行を捨てる」です。LIMIT 5 OFFSET 10 と LIMIT 10, 5 は同じ意味ですが、後者は数字の順が逆で紛らわしいので、OFFSET と書くほうを勧めます。

ORDER BY の無い LIMIT に意味はありません。 並び順が決まっていないのに「上から5行」と言っても、どの5行かが決まりません。 LIMIT を書くときは ORDER BY もセットです。

罠①:途中でデータが増えると、行がずれる

1ページ目を表示したあとで、誰かが新しい行を追加したとします。

INSERT INTO d_emp (emp_id, emp_name, dept_id, hire_date, salary, bonus, email, manager_id)
VALUES (0, '見学 太郎', 1, '2026-01-05', 300000, NULL, NULL, 1);

この状態で、2ページ目を取ります。

SELECT emp_id, emp_name FROM d_emp ORDER BY emp_id LIMIT 5 OFFSET 5;
+--------+-----------+
| emp_id | emp_name  |
+--------+-----------+
|      5 | 渡辺 明   |
|      6 | 伊藤 香織 |
|      7 | 山本 拓也 |
|      8 | 中村 彩   |
|      9 | 小林 翔   |
+--------+-----------+

「渡辺 明」が、1ページ目にも2ページ目にも出ています。

全体が1行ぶん後ろにずれたからです。 逆に、削除されたときは、1行が「どのページにも出ない」ことになります。読者からは、データが消えたように見えます。

行を戻しておきます。

DELETE FROM d_emp WHERE emp_id = 0;
SELECT COUNT(*) FROM d_emp;

60に戻ることを確かめてください。

罠②:OFFSET が大きいと遅くなる

これは60行では体感できません。 理屈だけ先に押さえます。

SELECT * FROM 大きな表 ORDER BY id LIMIT 20 OFFSET 100000;

「100000行目まで一気に飛ぶ」と読みたくなりますが、そうではありません。

データベースは、こうします。

  1. 並べ替える
  2. 先頭から 100020 行を作る
  3. そのうち最初の 100000 行を捨てる
  4. 残った20行を返す

捨てるためだけに、10万行を作っています。

ページが後ろへ行くほど、確実に遅くなります。 1ページ目は速く、1000ページ目は止まる、という形で現れます。

これが「ページ送りをどんどん押していくと、だんだん重くなる」正体です。 EXPLAIN で実際に何行読んでいるかを見る方法はパフォーマンス改善の回にあります。

直し方:キーセットページネーション

OFFSET を使うのをやめて、「前のページの最後の値」を条件にします。

-- 1ページ目
SELECT emp_id, emp_name FROM d_emp ORDER BY emp_id LIMIT 5;
-- 2ページ目(1ページ目の最後が emp_id = 5 だった)
SELECT emp_id, emp_name FROM d_emp WHERE emp_id > 5 ORDER BY emp_id LIMIT 5;
+--------+-----------+
| emp_id | emp_name  |
+--------+-----------+
|      6 | 伊藤 香織 |
|      7 | 山本 拓也 |
|      8 | 中村 彩   |
|      9 | 小林 翔   |
|     10 | 加藤 美咲 |
+--------+-----------+

さっきの「見学 太郎」をもう一度入れてからこれを打っても、返る5行は変わりません。 前のページの最後の値を覚えているので、行が増えても減ってもずれません(確かめたら、また DELETE FROM d_emp WHERE emp_id = 0; で戻してください)。

速さも変わります。 emp_id > 5 は索引で一発で場所が決まります(emp_id は主キー)。捨てる行が0行なので、1000ページ目でも1ページ目と同じ速さです。

OFFSETキーセット
深いページ遅い(捨てる行が増える)速い(常に同じ)
途中で追加・削除ずれるずれない
「5ページ目に飛ぶ」できるできない(次へ・前へだけ)
並び順何でもいい一意な列を含む必要がある

万能ではありません。 「3ページ目に飛ぶ」ができないので、ページ番号のリンクを並べる画面には使えません。 「もっと見る」「次へ」だけの画面なら、こちらが正解です。

並び順が salary DESC のような、重複する列のときはどうするか。 一意な列を足して、2段で比べます。

SELECT emp_id, emp_name, salary
FROM   d_emp
WHERE  (salary, emp_id) < (430000, 20)
ORDER BY salary DESC, emp_id DESC
LIMIT 5;

(a, b) < (x, y) は「行の組で比べる」書き方です。前のページの最後の行の (salary, emp_id) をそのまま渡します。 前の節で「同値のときは一意な列を足す」と言ったのは、ここで効いてきます。

⑦ DISTINCT を掘る

DISTINCT は行全体で見る

列1つなら、直感どおりです。

SELECT DISTINCT dept_id FROM d_emp ORDER BY dept_id;
+---------+
| dept_id |
+---------+
|    NULL |
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
+---------+
9 rows in set

9行です。部署は8つなのに9行なのは、NULL が1つの値として数えられているからです。

DISTINCT の中では、NULL 同士は「同じもの」として扱われます。 WHERE では NULL = NULL が「不明」だったのに、です。場所によって扱いが違うので、ここは丸暗記するしかありません。

列を2つにすると、話が変わります。

SELECT DISTINCT dept_id, salary FROM d_emp;
56 rows in set

60行のうち56行が残りました。 「部署と給与の組み合わせ」が重複しているのが4組だけだった、ということです。

DISTINCT は「最初の列だけを見て重複を消す」のではありません。 SELECT に並べた列全部を1つの組として見ます。 「DISTINCT を付けたのに重複が消えない」は、ほぼ100%これです。 出す列を1つ足したせいで、組み合わせとしては重複しなくなっています。

SELECT DISTINCT は、SELECT のところに書きます。 実行順の表を思い出してください。SELECT の直後(ORDER BY の前)に効きます。 だから、

-- これは通らない
SELECT DISTINCT dept_id FROM d_emp ORDER BY salary;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'appdb.d_emp.salary' which is not in SELECT list; this is incompatible with DISTINCT

DISTINCT で行をまとめてしまうと、salary がどれのことか決まらなくなるので、拒否されます。DISTINCT が無ければ通るのに、です。これも順番の話です。

GROUP BY との違い

「重複を消す」という結果だけ見ると、この2つは同じに見えます。

SELECT DISTINCT dept_id FROM d_emp;
SELECT dept_id FROM d_emp GROUP BY dept_id;

どちらも9行返ります。

違うのは、目的です。

DISTINCTGROUP BY
やること重複した行を消す行をグループに分ける
実行される場所SELECT のときWHERE の直後
集計関数使えない使える(COUNT SUM AVG)
グループを絞るできないHAVING で絞れる

GROUP BY にしかできないのは、こちらです。

SELECT dept_id, COUNT(*) AS 人数, AVG(salary) AS 平均給与
FROM   d_emp
GROUP BY dept_id
ORDER BY dept_id;

部署ごとに数えられます。DISTINCT では書けません。

「重複を消したいだけなら DISTINCT、数えたいなら GROUP BY」で使い分けます。 どちらでも書けるときは DISTINCT のほうが意図が伝わります。 「重複を消したい」としか言っていないからです。

MySQL 8.0 は、GROUP BY しても勝手には並べてくれません。 昔の MySQL は GROUP BY した列で自動的に並べ替えていましたが、8.0 でその挙動は無くなりました。 並べたいなら ORDER BY を必ず書いてください。 「前は並んでいたのに」は、これが原因です。

確かめ方の型

この記事で、全部の節で同じことをやりました。

SELECT を1本打ったら、返った行数を見る。そして、その数字が説明できるか自分に聞く。

説明できない数字が出たら、そこに NULL がいます。

この型は、ふだんの開発でもそのまま使えます。

-- ① まず全体を数える
SELECT COUNT(*) FROM d_emp;
-- ② 条件で数える
SELECT COUNT(*) FROM d_emp WHERE 条件;
-- ③ 条件の否定でも数える
SELECT COUNT(*) FROM d_emp WHERE NOT (条件);
-- ④ ②+③ が①になるか確かめる。ならなければ NULL がいる

本線の回で「UPDATE の前に同じ WHERE で SELECT する」と書きました。 同じ型です。 壊す操作の前に、壊さない操作で件数を確かめる。

つまずいたところ

症状原因と直し方
ERROR 1054 Unknown column '...' in 'where clause'WHERE で SELECT の別名を使っている。 式を書き直すか、派生表で包む
WHERE bonus = NULL で Empty setIS NULL を使う。 = NULL は永遠に真にならない
NOT IN (副問い合わせ) が 0 行副問い合わせの結果に NULL が混ざっている。 WHERE ... IS NOT NULL を足す
条件と、その否定を足しても全件にならないNULL の行がどちらにも入っていない。 OR 列 IS NULL を足す
合計を出したら NULL になったNULL を含む足し算は NULL。 COALESCE(列, 0) で包む
DISTINCT を付けたのに重複が残るSELECT の列全部を1組として見ている。 出す列を減らす
ERROR 3065 ... incompatible with DISTINCTDISTINCT のとき、SELECT に無い列で ORDER BY できない
GROUP BY したのに並んでいないMySQL 8.0 は自動で並べない。 ORDER BY を書く
ORDER BY の結果が実行のたびに違う同じ値の行がある。 ORDER BY ..., emp_id のように一意な列を最後に足す
ページを送るほど重いOFFSET が大きい。 キーセットページネーションに変える
LIKE '%語%' の検索が急に遅くなった索引が効かない書き方。 前方一致にするか、全文検索へ
CREATE command deniedappuser で入っている。 sudo mysql で入り直す
件数が記事と合わないINSERT が途中で切れている。 DELETE FROM d_emp; してから全部貼り直す

私が一番長く詰まったのは、NOT IN の0件です。

エラーが出ないので、SQLではなくデータのほうを疑いました。 「部下がいない人が本当にいないのか」と、d_emp を上から目で追いました。30分ほど無駄にしました。

気づいたきっかけは、NOT IN を IN に変えたら普通に返ってきたことです。「否定にすると全部消える」という形が見えて、そこで初めて NULL を疑いました。

「エラーではなく、もっともらしい嘘が返る」ときは、たいてい NULL です。 これを知っているかどうかだけの差で、30分が3秒になります。

後片づけ

d_emp と d_dept は、消さないでください。

このSQL深掘りシリーズの、以降の回でも同じ表を使います。 毎回60行を貼り直すのは、読むほうも打つほうも無駄です。

置いたままにしても、実害はありません。

  • appdb の中にありますが、tasks とは別の表なので、本線のアプリには何の影響もありません
  • d_ で始まっているので、SHOW TABLES; を打てばすぐ見分けられます
SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| d_dept          |
| d_emp           |
| tasks           |
+-----------------+

それでも消したい場合は、これです。

DROP TABLE IF EXISTS d_emp;
DROP TABLE IF EXISTS d_dept;

tasks を巻き込んでいないことを、必ず確かめてください。

SHOW TABLES;
SELECT COUNT(*) FROM tasks;

DROP DATABASE appdb; は打たないでください。 本線のアプリのデータが全部消えます。 消すのは表2つだけです。

この記事を作り直したくなったときは、DROP TABLE からやり直せば、何度でも同じ状態に戻せます。 そのために DROP TABLE IF EXISTS を先頭に置いてありました。

まとめ

  • SQLは書いた順に実行されない。 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
  • SELECT は真ん中より後ろにいる。 だから WHERE では別名が使えず、ORDER BY では使える。覚えるのではなく、順番の表を見れば分かる
  • WHERE に集計関数は書けない。 GROUP BY がまだ動いていないから。それをやるのが HAVING
  • NULL は「値が無い」ではなく「不明」。 だから = NULL はどう転んでも真にならない。 IS NULL を使う
  • SQLは三値論理。 真・偽のほかに不明がある。WHERE は真の行しか返さないので、不明は黙って落ちる
  • 条件と、その否定を足しても全件にならない。 数が合わないときは、まず NULL を疑う
  • NOT IN の中に NULL が1つでもあると、結果が丸ごと0行になる。 エラーは出ない。副問い合わせには必ず IS NOT NULL を足す
  • NULL を含む計算の答えは NULL。 COALESCE(列, 0) で包む。ただし WHERE の左側で包むと索引が効かなくなる
  • MySQL の ORDER BY は、ASC で NULL が先頭、DESC で末尾。 PostgreSQL とは逆。位置を決めたいなら ORDER BY (列 IS NULL), 列
  • LIKE '%x%' は索引が効かない。 前方一致だけが効く。電話帳で「たを含む人」が探せないのと同じ
  • ORDER BY で同じ値が並んだとき、その中の順序は保証されていない。 一意な列を最後に足すと決まる
  • LIMIT 20 OFFSET 100000 は10万行を作って捨てている。 深いページほど遅い。途中で追加・削除されると行がずれる
  • キーセットページネーションは「前のページの最後の値より先」で取る。 ずれず、常に同じ速さ。ただしページ番号で飛べない
  • DISTINCT は行全体で見る。 SELECT に並べた列全部を1組として比べる
  • DISTINCT は重複を消すもの、GROUP BY はグループに分けるもの。 数えたいなら GROUP BY
  • 確かめ方の型:SELECT を打ったら件数を見る。説明できない数字が出たら、そこに NULL がいる

参考リンク


次に読む

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

記事
← 前の記事nanoとVimでサーバーのファイルを直す — Vimから出られないのは仕様
→ 次の記事MySQLのデータ型選び — FLOATで金額は1円ずれる

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

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

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

Backend›SQL — Basic Syntax
  • SELECT / FROM / WHERE / ORDER BY と実行順
  • NULL の扱いと LIMIT / DISTINCT

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

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

コメント

コメントする

目次