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行捨てる」こと
この記事でやること
- 練習用のテーブルを2つ作る(
d_deptとd_emp、60行、NULL入り)。以降の深掘りでも使い回す WHEREで列の別名を使って、わざとエラーを出す。 そのうえでORDER BYでは通ることを確かめる= NULLを打って0行を見る。 そのあとNOT INにNULLを混ぜて、結果が消えるのを見るORDER BYを複数列・混在・式で書き分ける。 同値のときに順序が保証されないことを確かめる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 ではなく管理者で入ります。
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;
でも、データベースはこの順では読んでいません。 実際に意味を決めていく順番は、こうです。
| 順 | 句 | そこで何が決まるか |
|---|---|---|
| 1 | FROM | どの表を使うか。 ここで「対象の行の集まり」ができる |
| 2 | WHERE | 行を捨てる。 条件に合わない行がここで消える |
| 3 | GROUP BY | 残った行をグループにまとめる |
| 4 | HAVING | グループを捨てる(WHERE は行を、HAVING はグループを捨てる) |
| 5 | SELECT | どの列を出すか決める。 ここで初めて別名(AS)が付く |
| 6 | ORDER BY | 並べ替える |
| 7 | LIMIT | 上から何行かだけ残す |
書く順と、実行される順が違います。
書く順: 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 |
+----------+
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>|</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行目まで一気に飛ぶ」と読みたくなりますが、そうではありません。
データベースは、こうします。
- 並べ替える
- 先頭から 100020 行を作る
- そのうち最初の 100000 行を捨てる
- 残った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行返ります。
違うのは、目的です。
DISTINCT | GROUP 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 set | IS 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 DISTINCT | DISTINCT のとき、SELECT に無い列で ORDER BY できない |
GROUP BY したのに並んでいない | MySQL 8.0 は自動で並べない。 ORDER BY を書く |
ORDER BY の結果が実行のたびに違う | 同じ値の行がある。 ORDER BY ..., emp_id のように一意な列を最後に足す |
| ページを送るほど重い | OFFSET が大きい。 キーセットページネーションに変える |
LIKE '%語%' の検索が急に遅くなった | 索引が効かない書き方。 前方一致にするか、全文検索へ |
CREATE command denied | appuser で入っている。 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がまだ動いていないから。それをやるのがHAVINGNULLは「値が無い」ではなく「不明」。 だから= 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がいる
参考リンク
- roadmap.sh — SQL
- MySQL 8.0 リファレンスマニュアル(日本語)
- MySQL — SELECT 構文
- MySQL — NULL 値の扱い
- MySQL — 論理演算子と三値論理
- MySQL — ORDER BY の最適化
- MySQL — LIMIT の最適化
- MySQL — 比較関数と演算子(COALESCE / IFNULL)
- MySQL — 正規表現(REGEXP)
次に読む
この記事は 【深掘り】SQL の 1 / 17 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- SELECT / FROM / WHERE / ORDER BY と実行順
- NULL の扱いと LIMIT / DISTINCT
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント