SQLが扱っているのは、表ではなく集合です。リレーショナルモデルと集合演算から、書き方の理由を確かめます。
これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線のプロセスを止めても消えない場所 — MySQLとSQLの基本の回の続きです。あの回で
SELECTを打って表が出たところから、この深掘りシリーズが始まりました。この記事は、そのシリーズの締めくくりです。前回: GRANTと最小権限 — rootで繋ぐと全部持っていかれる
作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて
appdbがあれば、それ以外の準備は要りません。練習用のテーブルはこの記事の中で作ります。最後に、シリーズ全体で作ったd_付きのテーブルを全部消す手順まで書きます。
ここまで16本、SQLを打ってきました。
SELECT の実行順を見て、JOIN が表を繋いでいないことを知って、GROUP BY で行が減る理由を確かめて、索引が「張る」と「使われる」で別物だと分かって。打ったSQLの本数だけで言えば、もう数百行あります。
でも、まだ一度も名前を出していないものがあります。
そのSQLが立っている土台です。あなたが SELECT と書くたびに使っている考え方には、ちゃんと名前が付いています。
リレーショナルモデル(関係モデル)です。
1970年に、E.F.コッド(Edgar F. Codd)という人が論文1本で提案しました。MySQL より25年、PostgreSQL より26年、あなたが触っているどのソフトよりも先に、考え方のほうが存在していました。 ソフトはあとから、その考え方を実装したものです。
だから、こういうことが起きます。
- 「リレーショナル」の「リレーション」は、テーブル同士の関係のことではありません。 ほとんどの人が最初にこう誤解します(私もそうでした)
- リレーショナルモデルには「重複した行」が存在しません。 でもMySQLには、同じ行を3つ入れられます
- リレーショナルモデルの行に順序はありません。 でも
SELECT * FROM d_emp;は、毎回だいたい同じ順で返ってきます
つまり、SQLは理論の通りには作られていません。 ズレています。
そしてそのズレが、あなたがこのシリーズでハマってきたところと、ほぼ全部一致します。 NULL も、DISTINCT も、ORDER BY を書かないと順序が決まらないことも、全部そうです。
この記事では、そのズレをSQLで実際に見にいきます。 理屈だけ読んで終わりにはしません。打って、数を数えて、目で確かめます。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
- リレーショナルモデルの「関係」は表ではなく集合だということ。行にも列にも順序がなく、重複行は存在しないのが理論上の前提であること
- SQLはその前提を守っていないこと。だから重複行が入り、
ORDER BYが要り、UNIONとUNION ALLが別物になること UNIONがUNION ALLより遅いのは、重複を消すために、結果を一時テーブルに入れて突き合わせているからだということ。それがEXPLAINに出ること- 表を分ける理由は「異常を消すため」だけではないこと。1つの関係は1つの事実だけを表す、という考え方から見ると、正規化は結果でしかないこと
- SQLは「どう取るか」を書かないこと。手順を決めるのはオプティマイザで、それが分業として成立していること。そして「関係」を捨てると、その分業も一緒に失うこと
この記事でやること
SELECT VERSION();でMySQLのバージョンを確認する(この記事の後半は 8.0.31 以降でないと動きません)- 練習用のテーブルを作り、同じ行を3つ入れて、重複行が入ることを確かめる
- 同じテーブルに違うSQLを打って、返る順序が変わることを確かめる(順序は意味ではなく事情だと知る)
- 集合演算4つ(
UNION/UNION ALL/INTERSECT/EXCEPT)を打ち、古いMySQL向けの書き換えも用意する UNIONとUNION ALLをEXPLAINして、重複除去のコストを見る- シリーズ全体で作った
d_付きのテーブルを、確認してから全部消す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| リレーショナルモデル | データを「関係」の集まりとして扱う考え方。1970年提案。ソフトの名前ではない |
| 関係(リレーション) | 行の集合のこと。「テーブル同士の関連」ではない。1枚のテーブルが1つの関係にあたる |
| 集合 | 同じものが2つ入らない入れ物。数学の集合と同じ。{1, 2, 3} に 1 を足しても {1, 2, 3} のまま |
| 多重集合(バッグ) | 同じものが何個でも入る入れ物。SQLのテーブルはこちら。{1, 1, 2} は {1, 2} と別物 |
| 集合演算 | 2つの集合を足す・重なりを取る・引く、という操作。SQLでは UNION / INTERSECT / EXCEPT |
| 宣言的 | 「何が欲しいか」だけを書き、「どうやって取るか」は書かない書き方。SQLはこれ |
| オプティマイザ | 「どうやって取るか」をデータベースが自分で決める部分。実行計画を作る担当 |
| SQL標準 / 方言 | SQLには国際規格があり、各製品がそれを一部だけ実装している。規格外の独自機能を方言と呼ぶ |
「リレーショナル=表と表を関連づけられるDB」ではありません。 リレーショナル=データを「行の集合」として扱うDBです。関連づけ(
JOIN)はその結果できることの1つにすぎません。 ここは言葉の見た目に引っ張られて、ほぼ全員が一度間違えます。
まずバージョンを確かめる
この記事の後半で使う INTERSECT と EXCEPT は、MySQL 8.0.31 以降でないと動きません。 それより古いと構文エラーになります。先に確認します。
VMの中(Ubuntu Server)で、MySQLに入ります。
入ったら、MySQLのプロンプト(mysql>)で打ちます。
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.43 |
+-----------+
[スクリーンショット: 01_mysqlバージョン確認.png] SELECT VERSION(); を打って、バージョン番号が返っている画面を撮ってください。
読み方は3つの数字を左から比べるだけです。8.0.43 なら、8.0.31 より新しいので使えます。8.0.28 だったら使えません。その場合の書き換えも、あとで用意します。
Ubuntu 22.04 / 24.04 の
aptで入れた MySQL は 8.0 系です。その中の3つ目の数字は、apt upgradeをかけたタイミングで上がっていきます。 動かなかったら、まずここを疑ってください。構文を間違えたのではなく、そのMySQLがまだその構文を知らないだけです。
「表」ではなく「関係」
リレーショナルモデルが言う「関係」には、3つの前提があります。
- 行に順序はない
- 列に順序はない(列は名前で識別する)
- 同じ行は2つ存在しない(集合だから)
この3つを、順番にSQLで壊していきます。 壊れるのが正常です。SQLは理論をそのまま実装したものではないからです。
練習用のテーブルを作る
この記事で使う練習用テーブルは、すべて d_ で始めます。 シリーズ全体でそうしてきたのと同じで、最後に「d_ で始まるものだけ消す」ためのしるしです。
DROP TABLE IF EXISTS d_rel_dup;
CREATE TABLE d_rel_dup (
code VARCHAR(10),
name VARCHAR(20)
);
主キーも UNIQUE も付けていません。 わざとです。
1. 同じ行を3つ入れてみる
INSERT INTO d_rel_dup (code, name) VALUES
('A01', 'ねじ'),
('A01', 'ねじ'),
('A01', 'ねじ');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
エラーは出ません。 3行入りました。
SELECT COUNT(*) FROM d_rel_dup;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
リレーショナルモデルの上では、これは起こり得ません。 集合に同じ要素を3回足しても、要素は1つのままだからです。理論の上では、いまの d_rel_dup は「1行の関係」でなければいけません。
でもMySQLは3と答えました。
つまり、SQLのテーブルは集合ではなく多重集合(バッグ)です。 同じものが何個でも入る入れ物です。そして、その1点から出てくる結果が、これです。
SELECT DISTINCT code, name FROM d_rel_dup;
+------+------+
| code | name |
+------+------+
| A01 | ねじ |
+------+------+
DISTINCTという機能が存在すること自体が、「SQLのテーブルは集合ではない」という証拠です。 本当に集合なら、重複はそもそも入らないので、消す機能は要りません。
SELECT の回で「DISTINCT は行全体で見る」という話をしました。あれの理由がここです。 DISTINCT は「集合に戻す」操作で、集合の要素は行そのものだからです。
では、なぜ主キーを付けるのか。 ここまで来ると、答えが変わります。
- 今までの答え: 「1件を特定できるようにするため」
- ここでの答え: 「テーブルを、理論上の『関係』に近づけるため」
主キーや UNIQUE を付けた瞬間、そのテーブルは重複行を持てなくなり、集合に一歩近づきます。 制約の回でやった UNIQUE は、「間違いを防ぐ道具」であると同時に「関係であることを守る道具」でした。
2. 列に順序はない、はずだった
リレーショナルモデルでは、列は名前で識別します。順番は持ちません。 {name, code} と {code, name} は同じ関係です。
SQLはそうなっていません。
SELECT * FROM d_rel_dup LIMIT 1;
+------+------+
| code | name |
+------+------+
| A01 | ねじ |
+------+------+
CREATE TABLE で書いた順に出ました。 * が列の順番を知っているということは、テーブルが列の順番を持っているということです。
そして、それが事故になる形はこれです。
INSERT INTO d_rel_dup VALUES ('ボルト', 'B02');
列名を書かずに入れると、位置で入ります。 code に ボルト、name に B02 が入りました。エラーは出ません。 型も長さも通ってしまうからです。
SELECT * FROM d_rel_dup;
+--------+------+
| code | name |
+--------+------+
| A01 | ねじ |
| A01 | ねじ |
| A01 | ねじ |
| ボルト | B02 |
+--------+------+
入れ替わったまま入りました。 元に戻します。
DELETE FROM d_rel_dup WHERE code = 'ボルト';
INSERTで列名を省略してはいけない理由は、ここまで「読みにくいから」で説明されがちです。 本当の理由は、列の順番はデータの意味ではないのに、SQLはそれを意味として使えてしまうからです。ALTER TABLEで列を1本足した日に、そのINSERTは静かに壊れます。(DDLの回で見た通りです)
3. 行に順序はない、を確かめる
ここが一番おもしろいところです。同じテーブルに、違うSQLを打つと、違う順で返ってきます。
DROP TABLE IF EXISTS d_rel_row;
CREATE TABLE d_rel_row (
id INT PRIMARY KEY,
name VARCHAR(20),
KEY idx_name (name)
);
INSERT INTO d_rel_row (id, name) VALUES
(3, 'sato'),
(1, 'tanaka'),
(2, 'suzuki');
入れた順は 3 → 1 → 2 です。 では取り出します。
SELECT * FROM d_rel_row;
+----+--------+
| id | name |
+----+--------+
| 1 | tanaka |
| 2 | suzuki |
| 3 | sato |
+----+--------+
入れた順ではなく、id 順で返りました。 ORDER BY は書いていません。
次に、name だけを取ります。
SELECT name FROM d_rel_row;
+--------+
| name |
+--------+
| sato |
| suzuki |
| tanaka |
+--------+
同じテーブル、同じ3行なのに、順番が変わりました。 これも ORDER BY は書いていません。
[スクリーンショット: 02_順序が変わる.png] SELECT * FROM d_rel_row; と SELECT name FROM d_rel_row; を続けて打ち、返る順が違っている画面を撮ってください。
理由は、索引の回でやったことで説明できます。 EXPLAIN で見ます。
EXPLAIN SELECT name FROM d_rel_row;
key の欄に idx_name、Extra に Using index が出ます。 name だけなら索引を読むだけで足りるので、MySQLは表を読まずに索引を読みました。 そして索引は name の順に並んでいます。 だから name 順で返りました。
SELECT * のほうは、id の主キー順に並んだ本体(クラスタ化索引)をそのまま読んだので、id 順で返りました。
順序は、データの意味ではありません。読み方の事情がそのまま外に出ているだけです。 だから「たまたま合っている順序」に依存したコードは、索引を1本足した日に壊れます。
順序が欲しいなら、書くしかありません。
SELECT name FROM d_rel_row ORDER BY id;
「ORDER BY を書け」は作法ではありません。 SQLの返す行に順序という意味は最初から無いので、欲しいなら自分で足すしかない、という話です。
理論とSQLのズレを、1枚にする
| リレーショナルモデルの前提 | SQL(MySQL)では | 実際に困る形 |
|---|---|---|
| 関係は行の集合。同じ行は2つ存在しない | テーブルは多重集合。同じ行が何個でも入る | 集計が二重に数えられる。DISTINCT が必要になる |
| 行に順序はない | 順序は保証されないが、何かの順では返る | ORDER BY 無しの順に依存 → 索引追加やバージョン更新で壊れる |
| 列に順序はない。名前で識別する | SELECT * は定義順。INSERT は列名省略で位置で入る | 列を1本足した日に INSERT が静かに壊れる |
| 値は必ず1つ。「不明」は無い | NULL がある。真・偽・不明の三値論理になる | = NULL が真にならない。NOT IN に NULL が1つで全滅 |
| 関係の組み合わせは集合演算 | UNION は集合寄り(重複を消す)、UNION ALL は多重集合のまま | 遅いほうを既定で使ってしまう |
| すべての操作の結果もまた関係 | サブクエリの結果も表として扱える(ここは理論通り) | サブクエリの回でやった入れ子が成立する |
このシリーズでハマってきたところが、右の列にほぼ全部入っています。
「SQLは難しい」のではありません。「SQLは、理論をそのまま実装していない」だけです。どこがズレているかを1枚で持てば、初見の挙動でも当たりが付きます。
集合演算 — 表を「足す・重ねる・引く」
関係が集合なら、集合として足したり引いたりできるはずです。 SQLにはその演算があります。
練習用の2つの集合
DROP TABLE IF EXISTS d_set_a;
DROP TABLE IF EXISTS d_set_b;
CREATE TABLE d_set_a (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE d_set_b (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO d_set_a (id, name) VALUES (1,'tanaka'), (2,'suzuki'), (3,'sato');
INSERT INTO d_set_b (id, name) VALUES (2,'suzuki'), (3,'sato'), (4,'takahashi');
suzuki と sato が両方にいます。 tanaka はAだけ、takahashi はBだけです。
UNION — 足す(重複を消す)
SELECT id, name FROM d_set_a
UNION
SELECT id, name FROM d_set_b;
+----+-----------+
| id | name |
+----+-----------+
| 1 | tanaka |
| 2 | suzuki |
| 3 | sato |
| 4 | takahashi |
+----+-----------+
4 rows in set (0.00 sec)
3行 + 3行 = 4行です。重なった2行が1つにまとめられました。 これが集合の足し算です。
UNION ALL — 足す(重複を残す)
SELECT id, name FROM d_set_a
UNION ALL
SELECT id, name FROM d_set_b;
+----+-----------+
| id | name |
+----+-----------+
| 1 | tanaka |
| 2 | suzuki |
| 3 | sato |
| 2 | suzuki |
| 3 | sato |
| 4 | takahashi |
+----+-----------+
6 rows in set (0.00 sec)
6行です。 id が重複していますが、エラーにはなりません。 結果は表であって、主キーを持たないからです。
UNIONが集合、UNION ALLが多重集合です。SQLは両方持っている、という言い方が正確です。 そして「重複が無いと分かっているならUNION ALLを書く」のが正しい理由が、次で見えます。
なぜ UNION のほうが遅いのか — EXPLAIN で見る
重複を消すには、全部の行を見比べる必要があります。 見比べるには、並べるか、ハッシュを作るかしかありません。どちらも仕事です。
EXPLAIN SELECT id, name FROM d_set_a
UNION
SELECT id, name FROM d_set_b;
+----+--------------+------------+------+---------+-----------------+
| id | select_type | table | type | rows | Extra |
+----+--------------+------------+------+---------+-----------------+
| 1 | PRIMARY | d_set_a | ALL | 3 | NULL |
| 2 | UNION | d_set_b | ALL | 3 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | Using temporary |
+----+--------------+------------+------+---------+-----------------+
3行目に注目してください。 UNION RESULT という行があり、Extra が Using temporary です。
「一時表を作った」という意味です。重複を消すために、結果を一度どこかに置いて見比べています。
UNION ALL に変えます。
EXPLAIN SELECT id, name FROM d_set_a
UNION ALL
SELECT id, name FROM d_set_b;
+----+-------------+---------+------+------+-------+
| id | select_type | table | type | rows | Extra |
+----+-------------+---------+------+------+-------+
| 1 | PRIMARY | d_set_a | ALL | 3 | NULL |
| 2 | UNION | d_set_b | ALL | 3 | NULL |
+----+-------------+---------+------+------+-------+
UNION RESULT の行が消えました。 一時表が要りません。上から順に流すだけだからです。
[スクリーンショット: 03_union_explain比較.png] UNION と UNION ALL の EXPLAIN を続けて打ち、Using temporary の有無が違う画面を撮ってください。
表示はMySQLのバージョンで変わります。
Using temporaryが出ない環境もあります。 見るべきは「行が1本多いかどうか」です。多い=重複を消す仕事が1段増えている、と読みます。
3行同士なら差は出ません。 10万行同士なら、はっきり出ます。 索引の回で「Using filesort が消えると速くなる」のを見たのと、まったく同じ話です。
判断はこうです。
| 状況 | 書くもの |
|---|---|
| 重複が出ないと分かっている(別々の期間、別々の店舗など) | UNION ALL |
| 重複を消したい | UNION |
| 重複が出るか分からない | まず UNION ALL で件数を見て、重複の有無を確かめてから決める |
「とりあえず UNION」は、毎回「一時テーブルで重複を突き合わせる仕事」を1回買っていることになります。
INTERSECT と EXCEPT — 重なりと引き算
ここが 8.0.31 以降でないと動かないところです。
SELECT id, name FROM d_set_a
INTERSECT
SELECT id, name FROM d_set_b;
+----+--------+
| id | name |
+----+--------+
| 2 | suzuki |
| 3 | sato |
+----+--------+
両方にいる行だけが出ました。
SELECT id, name FROM d_set_a
EXCEPT
SELECT id, name FROM d_set_b;
+----+--------+
| id | name |
+----+--------+
| 1 | tanaka |
+----+--------+
Aにいて、Bにいない行です。引き算です。
向きがあります。 ひっくり返すと結果が変わります。
SELECT id, name FROM d_set_b
EXCEPT
SELECT id, name FROM d_set_a;
+----+-----------+
| id | name |
+----+-----------+
| 4 | takahashi |
+----+-----------+
UNIONとINTERSECTは順番を入れ替えても同じ結果、EXCEPTは変わります。 足し算と掛け算には順番が無く、引き算にはある。 それと同じです。
古いMySQLでの書き換え
SELECT VERSION(); が 8.0.31 より小さかった場合、こうなります。
ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'INTERSECT'
構文を間違えたのではありません。そのMySQLが INTERSECT を知らないだけです。書き換えます。
INTERSECT の代わり(EXISTS):
SELECT a.id, a.name
FROM d_set_a a
WHERE EXISTS (
SELECT 1 FROM d_set_b b
WHERE b.id = a.id AND b.name = a.name
);
EXCEPT の代わり(NOT EXISTS):
SELECT a.id, a.name
FROM d_set_a a
WHERE NOT EXISTS (
SELECT 1 FROM d_set_b b
WHERE b.id = a.id AND b.name = a.name
);
IN / NOT IN でも書けます。 比べる列が1本だけならこちらが短いです。
SELECT id, name FROM d_set_a WHERE id IN (SELECT id FROM d_set_b);
ただし NOT IN は使わないでください。
-- ⚠️ 副問い合わせ側に NULL が1つでもあると、結果が丸ごと0行になる
SELECT id, name FROM d_set_a WHERE id NOT IN (SELECT id FROM d_set_b);
SELECT の回でやった通りです。エラーは出ません。黙って0行になります。 NOT EXISTS は NULL があっても壊れません。
そして、書き換えた瞬間に分かることがあります。
INTERSECT | EXISTS での書き換え | |
|---|---|---|
| 行数 | 3行 | 6行 |
| 読んだとき分かること | 「重なりが欲しい」 | 「Aを1行ずつ見て、Bに同じものがあるか探している」 |
INTERSECT は「何が欲しいか」だけを書いています。EXISTS は「どう探すか」が少し混ざっています。
この差が、次の話です。
なぜ「表を分ける」のか — 正規化をもう一度
本線のテーブル設計の回で、正規化をやりました。あの回の説明はこうでした。
1つの表に全部入れると、更新のときに片方だけ直してしまう。だから分ける。
これは正しいです。 実務ではこの説明で足ります。でも、これは「症状の説明」です。
「関係」の側から見ると、順番が逆になります。
1つの関係は、1種類の事実だけを表す。 2種類の事実が1枚に混ざっているから、片方だけ直せてしまう。異常はその結果でしかない。
具体で見ます。 混ざっている表です。
DROP TABLE IF EXISTS d_rel_flat;
CREATE TABLE d_rel_flat (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(20),
dept_id INT,
dept_name VARCHAR(20)
);
INSERT INTO d_rel_flat VALUES
(1, 'tanaka', 10, '営業'),
(2, 'suzuki', 10, '営業'),
(3, 'sato', 20, '開発');
この表は、2つの事実を持っています。
- 「社員1は tanaka で、部署10に所属している」(社員という事実)
- 「部署10の名前は営業である」(部署という事実)
部署10の名前は、2行に書かれています。 dept_name は dept_id が決まれば決まるのに、社員の行数だけ複製されています。
確かめます。
SELECT dept_id, COUNT(DISTINCT dept_name) AS 名前の種類, COUNT(*) AS 書かれている回数
FROM d_rel_flat
GROUP BY dept_id;
+---------+------------+------------------+
| dept_id | 名前の種類 | 書かれている回数 |
+---------+------------+------------------+
| 10 | 1 | 2 |
| 20 | 1 | 1 |
+---------+------------+------------------+
「名前の種類は1なのに、2回書かれている」。 この1が2に化けた瞬間が、更新異常です。
UPDATE d_rel_flat SET dept_name = '営業部' WHERE emp_id = 1;
SELECT dept_id, COUNT(DISTINCT dept_name) AS 名前の種類 FROM d_rel_flat GROUP BY dept_id;
+---------+------------+
| dept_id | 名前の種類 |
+---------+------------+
| 10 | 2 |
| 20 | 1 |
+---------+------------+
部署10に、名前が2つできました。 どちらが正しいかは、データベースには判断できません。
さらに、この表では「まだ社員が1人もいない部署」を登録できません。 emp_id が主キーなので、社員の行なしに部署だけ書けないからです。部署という事実が、社員という事実に人質に取られています。
分けます。
DROP TABLE IF EXISTS d_rel_emp;
DROP TABLE IF EXISTS d_rel_dept;
CREATE TABLE d_rel_dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(20) NOT NULL
);
CREATE TABLE d_rel_emp (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL,
dept_id INT,
CONSTRAINT fk_rel_emp_dept FOREIGN KEY (dept_id) REFERENCES d_rel_dept(dept_id)
);
INSERT INTO d_rel_dept VALUES (10, '営業'), (20, '開発'), (30, '総務');
INSERT INTO d_rel_emp VALUES (1,'tanaka',10), (2,'suzuki',10), (3,'sato',20);
部署30には社員が1人もいませんが、登録できました。 事実が独立したからです。
そして、部署名はもう1箇所にしかありません。 直す場所が1つなら、片方だけ直すという操作が存在できません。
UPDATE d_rel_dept SET dept_name = '営業部' WHERE dept_id = 10;
SELECT e.emp_name, d.dept_name
FROM d_rel_emp e
JOIN d_rel_dept d ON d.dept_id = e.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| tanaka | 営業部 |
| suzuki | 営業部 |
| sato | 開発 |
+----------+-----------+
1行直したら、2人とも直りました。
正規化は「異常を防ぐテクニック」ではありません。 1つの関係に1種類の事実だけを置いた結果、異常が起こりようがなくなる、という順番です。 だから「どこまで正規化するか」で迷ったときの問いは、「第3正規形か」ではなく「この表は何種類の事実を書いているか」です。
そして JOIN の回で見た「JOIN は表を繋いでいない」も、ここに繋がります。 JOIN は分かれた関係から新しい関係を1つ作る演算です。繋いでいるのではなく、作っています。 作れるから、安心して分けられます。
SQLは「どう取るか」を書かない
もう1つ、ここまで一度も意識せずに使ってきたものがあります。
SELECT e.emp_name, d.dept_name
FROM d_rel_emp e
JOIN d_rel_dept d ON d.dept_id = e.dept_id
WHERE d.dept_name = '営業部';
このSQLに、手順は1つも書かれていません。
- どちらの表を先に読むか — 書いていない
- 索引を使うか、全部読むか — 書いていない
- どうやって突き合わせるか(1行ずつ探すのか、ハッシュを作るのか)— 書いていない
書いてあるのは「欲しいもの」だけです。これを宣言的と言います。
手順を決めているのは、オプティマイザです。 索引の回で見た EXPLAIN は、「その決定を見せてくれ」というお願いでした。
EXPLAIN SELECT e.emp_name, d.dept_name
FROM d_rel_emp e
JOIN d_rel_dept d ON d.dept_id = e.dept_id
WHERE d.dept_name = '営業部';
d_rel_dept が先、d_rel_emp が後、という順で計画が出ます。 SQLでは d_rel_emp を先に書いたのに、です。
FROMに書いた順は、読む順ではありません。SELECTの回で見た「SQLは書いた順に実行されない」は、論理的な順序の話でした。 ここで見ているのは、そのさらに下、物理的にどう読むかの話です。両方とも、書いた順ではありません。
この分業には、はっきりした利益があります。
| 分業 | 誰の仕事 | 変わったときどうなるか |
|---|---|---|
| 何が欲しいか | あなた | 要件が変わったらSQLを書き直す |
| どう取るか | オプティマイザ | データが増えても、索引が増えても、SQLは書き直さない |
3行の表と、300万行の表で、同じSQLが動きます。 同じ手順で動くのではありません。違う手順が選ばれて、同じ答えが返ります。
これは、APIの回で書いたJavaScriptとは根本的に違います。 JavaScriptで配列を絞るとき、あなたは filter を1回まわすという手順そのものを書いています。データが100万件になっても、その手順は変わりません。あなたが書き直すまでは。
代償もあります。
- オプティマイザの判断が外れることがある。 統計情報が古いとき、想定より偏ったデータのとき
- そのとき「なぜ遅いか」が自分の書いた行に無い。 だから
EXPLAINを読む必要が出る FORCE INDEXのように手順を指定する道具はあるが、使った瞬間に分業が壊れる(索引の回で書いた通りです)
「SQLはブラックボックスで気持ち悪い」という感想は、ここから来ます。 正しくは、意図的にブラックボックスにして、その中身は EXPLAIN で開けられるようにしてある、です。
「関係」を捨てると、何が手に入り、何を失うか
本線のNoSQLの回で、MongoDBを触りました。あのとき「スキーマは消えずにアプリへ移る」という結論を書きました。
いま言い直せます。 MongoDBが捨てたのは、スキーマではなく「関係」のほうです。
| リレーショナル | ドキュメント指向(関係を捨てた側) | |
|---|---|---|
| データの単位 | 行の集合。関係が集合演算で組み合わさる | ドキュメント1個。中に入れ子を持てる |
| 重複の扱い | 分けて JOIN で作る。事実は1箇所 | 埋め込んで複製する。読むのが速い |
| 正しさを守るのは | データベース(型・NOT NULL・UNIQUE・外部キー) | アプリ(入れる前に自分で検査する) |
| 問い合わせ | 宣言的。手順はオプティマイザが決める | 手順に近い。どのコレクションをどう辿るか自分で組む |
| 形の違うデータ | 入らない。ALTER TABLE が要る | そのまま入る |
| 複数の場所にまたがる更新 | トランザクションでまとめて成功か失敗(本線の回) | 標準構成では使えない。使わなくて済む形に寄せる |
| 横に増やす | 難しい。集合演算が全台にまたがるため | しやすい。ドキュメントが独立しているため |
手に入るもの: 形の自由と、横に増やしやすさと、読む速さ。 失うもの: 「事実は1箇所」という保証と、宣言的な問い合わせ。
そして重要なのは、失ったものが消えるわけではないことです。
「事実は1箇所」を守る仕事は、データベースからアプリへ移ります。 「どう取るか」を決める仕事も、オプティマイザからあなたへ移ります。 仕事は無くなりません。誰がやるかが変わるだけです。
データベースの選び方の回で「迷ったらリレーショナル」と書いた根拠が、ここです。個人でサービスを作る段階では、その仕事を引き取れる人手が自分1人しかいないからです。
SQLの標準と方言 — 書いたSQLはどこまで動くのか
このシリーズで打ったSQLは、MySQLで打ちました。 では、PostgreSQL で動くのでしょうか。
答えは「7〜8割は動く。残りは書き換えが要る」です。
SQLには国際規格(ISO/IEC 9075)があります。 ただし規格を完全に実装した製品は存在しません。 各製品が一部を実装し、そこに独自機能を足しています。その独自部分を方言と呼びます。
このシリーズで打ったものの中で、方言だったところを並べます。
| このシリーズで使ったもの | 標準か | PostgreSQL では |
|---|---|---|
SELECT / WHERE / JOIN / GROUP BY | 標準 | そのまま動く |
UNION / INTERSECT / EXCEPT | 標準 | そのまま動く |
ウィンドウ関数(OVER) | 標準 | そのまま動く |
WITH(CTE) | 標準 | そのまま動く |
LIMIT n OFFSET m | 標準は FETCH FIRST n ROWS ONLY | LIMIT も動く(両対応) |
バッククォート 列名 | 標準は二重引用符 "列名" | 動かない。"..." に直す |
SHOW TABLES / DESCRIBE | 方言 | 動かない。\dt / \d 表名 |
AUTO_INCREMENT | 方言 | 動かない。GENERATED ... AS IDENTITY |
IFNULL() | 標準は COALESCE() | 動かない。COALESCE() は両方で動く |
CONCAT() の NULL の扱い | 実装依存 | 挙動が違う(関数の回参照) |
ORDER BY での NULL の位置 | 実装依存 | MySQLと逆。NULLS FIRST / NULLS LAST で明示できる |
この表から出てくる指針は、1つです。
移植しやすいSQLを書くコツは、「標準だけ使う」ではありません。 どれが方言かを知っておくことです。知っていれば、移すときに探す場所が分かります。
実際に試す(次にやると一番効く1つ)
本線で PostgreSQL の回をやっているなら、環境はもう手元にあります。
このシリーズで一番長いSQL — たとえばウィンドウ関数の回やCTEの回で書いたもの — を、そのまま PostgreSQL に貼ってみてください。
やることは3つだけです。
- 貼る
- エラーが出た行だけ直す
- 直した理由が「方言だったから」か「自分が間違えたから」かを分ける
これをやると、「どこまでが SQL という言語で、どこからが MySQL という製品か」の線が、自分の中に引けます。 本を読んで覚えるより速いです。1本のSQLで十分効きます。
EXPLAIN の読み方だけは、まるごと別物です。PostgreSQL の EXPLAIN は形式が違います。そこは「同じSQLでも、中で何をしているかは製品ごとに違う」という、この記事の話そのものの実例になります。
つまずいたところ
1. 「リレーション=テーブル同士の関係」だと10年思っていた
外部キーで表と表が繋がるから「リレーショナル」だと思っていました。 完全に逆でした。リレーション(関係)は1枚のテーブルそのものです。外部キーは後から足された仕組みで、名前の由来ではありません。この誤解のせいで、「関係を捨てる」という言葉の意味が長くつかめませんでした。
2. UNION を既定にしていた
UNION ALL は「重複を許す雑なほう」だと思っていました。 逆です。UNION ALL が素直で、UNION が余計な仕事を1つしているほうです。EXPLAIN に UNION RESULT の行が出るのを見るまで、「重複を消す」がタダだと思っていました。
3. INTERSECT が構文エラーになって、書き方を疑った
最初、INTERSECT を打ったら ERROR 1064 が出ました。 括弧の位置やセミコロンを疑って15分溶かしました。原因は、そのMySQLが 8.0.28 だったことです。ERROR 1064 は「構文が間違っている」ではなく「私にはこの構文が読めない」という意味でした。新しい構文を試すときは、先に SELECT VERSION(); を打つ。 これだけで防げました。
4. 「順序が変わる」を再現できなくて焦った
SELECT * と SELECT name で順序が変わる例を作るのに、最初は失敗しました。 3行しか無いテーブルに索引を張らずに試したので、両方とも同じ順で返ったからです。KEY idx_name (name) を足して、EXPLAIN で Using index を確認して、やっと再現しました。 「順序は事情で決まる」なら、事情のほうを作らないと再現しない、という当たり前のことに気づくのに時間がかかりました。
5. 正規化を「型」として覚えようとして失敗した
第1正規形・第2正規形・第3正規形の定義を暗記しようとして、3回忘れました。 「この表は何種類の事実を書いているか」という問い1つに置き換えたら、忘れなくなりました。 定義は、その問いを厳密に言い直したものです。問いが先で、定義が後です。
ここまでの16本で、できるようになったこと
この記事の前に、16本ありました。 主なものを、「何ができるようになったか」だけで並べます。
| 扱ったこと | できるようになったこと |
|---|---|
SELECT の実行順と NULL | WHERE で別名が使えない理由を説明できる。 NOT IN に NULL が混ざる事故を避けられる |
| データ型 | 金額に FLOAT を使わない。 型を「入る/入らない」ではなく「何を保証するか」で選べる |
| DDL | ALTER TABLE を打つ前に、戻せるかを確認する |
| DML | UPDATE / DELETE を先に SELECT で確かめる手順が身についた |
| 制約 | アプリで弾く前に、データベース側で弾く。 どちらか一方にしない |
JOIN | JOIN が新しい表を作っていると分かる。 件数が増える理由を説明できる |
集約と GROUP BY | GROUP BY で行が減る理由と、WHERE と HAVING の使い分け |
| ウィンドウ関数 | 行を減らさずに集計を横に付けられる。 順位・累計が1本で書ける |
| サブクエリ | 相関サブクエリが行の数だけ実行されると分かる。 JOIN に書き換えられる |
| CTEと再帰 | 3段のサブクエリを WITH で上から読める形に直せる。 階層データを辿れる |
| 関数 | CONCAT に NULL が1つで全部消える類の罠を先に潰せる |
索引と EXPLAIN | 「張った」と「使われた」を分けて考えられる。 EXPLAIN を読める |
このほか、同じシリーズの中で、ビューとストアドプロシージャ、トランザクションの分離レベル、遅いクエリの直し方、権限とセキュリティも扱っています。 そしてこの記事で、打ってきたSQLが何の上に載っていたのかを言葉にしました。
16本を通して身についたのは、たぶん個々の構文ではありません。 これです。
打つ → 件数を数える → 説明できない数字が出たら止まる →
EXPLAINかNULLを疑う
この順番だけは、どのデータベースに移っても変わりません。
次に何をするか
「SQLを勉強する」を続けるのは、ここでいったん終わりでいいです。 次は、使う側に回ります。具体的に3つ書きます。
1. PostgreSQL で同じことをやる(半日)
前述の通りです。このシリーズで書いた一番長いSQLを PostgreSQL に貼って、直す。 書き換えが必要だった行を1枚のメモにする。 それが自分用の方言メモになります。
2. 自分の作ったアプリの、実際のSQLに EXPLAIN を打つ(1時間)
本線で作った ~/server のアプリには、もうSQLが何本か入っています。
出てきたSQLを、1本ずつ EXPLAIN にかけてください。 type: ALL が出るものが、いま全部読んでいるSQLです。行数が少ないうちは問題になりませんが、どれがそうなのかを知っているかどうかで、増えたときの対応が変わります。
3. 練習用ではなく、本物のデータで試す(1〜2時間)
行数の多いCSVを1つ持ってきて、LOAD DATA か INSERT で入れて、集計してみてください。 自治体の公開データでも、自分のサービスのログでも構いません。
練習用テーブルと決定的に違うのは、本物のデータには必ず「想定外」が入っていることです。空文字と NULL が混ざる。全角と半角が混ざる。日付の形式が途中で変わる。 文字コードの回と日付と時刻の回でやったことが、まとめて出てきます。そこで初めて、このシリーズが道具になります。
後片づけ
このシリーズを通して、練習用のテーブルを大量に作りました。 全部 d_ で始めてあります。ここでまとめて消します。
まず、何があるか数える
SHOW TABLES LIKE 'd\_%';
_ の前のバックスラッシュを忘れないでください。 LIKE の中では _ が「任意の1文字」を意味する記号なので、'd_%' と書くと data も demo も引っかかります。 d\_% と書いて初めて「文字としてのアンダースコア」になります。
+---------------------------+
| Tables_in_appdb (d\_%) |
+---------------------------+
| d_at |
| d_cte_emp |
| d_dept |
| d_emp |
...
件数も見ておきます。
SELECT COUNT(*) AS 消す数
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_%';
消さないテーブルが混ざっていないことを、必ず目で確かめてください。
SHOW TABLES;
tasks など、本線のアプリが使っているテーブルが d_ で始まっていないことを確認します。
消す文を、自分で書かずに作らせる
30本以上あります。手で書くと必ず打ち間違えます。 MySQLに書かせます。
SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') AS stmt
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_%';
DROP TABLE IF EXISTS d_at; のような行がずらっと出ます。 これを目で読んでから、コピーして貼ります。
ただし、そのまま貼ると失敗するものがあります。
ERROR 3730 (HY000): Cannot drop table 'd_rel_dept' referenced by a foreign key constraint
制約の回で張った外部キーが残っているからです。参照されている側は、参照している側より先に消せません。
順番を考えるより、一時的に外します。
SET FOREIGN_KEY_CHECKS = 0;
そして DROP TABLE を全部流し、必ず戻します。
SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 0;は、いま繋いでいるセッションの中だけで効きます。exitすれば自然に戻ります。 それでも、戻す1行を必ず打ってください。 同じセッションで続けて別の作業をしたとき、外部キーが効かないままINSERTが通ります。
消えたことを確かめる
SHOW TABLES LIKE 'd\_%';
Empty set (0.00 sec)
そして、本線のテーブルが無事なことを確かめます。
SHOW TABLES;
SELECT COUNT(*) FROM tasks;
DROP DATABASE appdb;は打たないでください。 本線のアプリのデータが全部消えます。 消すのはd_で始まる表だけです。
この記事のテーブルだけを消したい場合は、これです。
DROP TABLE IF EXISTS d_rel_emp;
DROP TABLE IF EXISTS d_rel_dept;
DROP TABLE IF EXISTS d_rel_flat;
DROP TABLE IF EXISTS d_rel_row;
DROP TABLE IF EXISTS d_rel_dup;
DROP TABLE IF EXISTS d_set_a;
DROP TABLE IF EXISTS d_set_b;
d_rel_emp を先に書いてあります。 外部キーで d_rel_dept を参照している側だからです。
まとめ
- リレーショナルモデルはソフトではなく考え方。 1970年に提案され、MySQLもPostgreSQLも、あとからそれを実装したもの
- 「リレーション(関係)」はテーブル同士の関連ではない。 1枚のテーブル、つまり行の集合そのものが関係
- 理論上の関係には、行の順序も、列の順序も、重複行も無い。 SQLのテーブルは3つとも守っていない
- 同じ行は3つ入る。 だから
DISTINCTという機能が要る。DISTINCTの存在が「テーブルは集合ではない」証拠 - 主キーと
UNIQUEは、テーブルを理論上の「関係」に近づける道具でもある SELECT *とSELECT 列名で、返る順が変わる。 どの索引を読んだかがそのまま外に出るため。順序は意味ではなく事情ORDER BYを書けというのは作法ではない。 順序という意味が最初から無いので、欲しいなら足すしかないUNIONは集合の足し算(重複を消す)、UNION ALLは多重集合のまま。EXPLAINにUNION RESULTの行が出るかどうかで見分ける- 重複が出ないと分かっているなら
UNION ALL。 「とりあえずUNION」は毎回「一時テーブルで重複を突き合わせる仕事」を1回買っている INTERSECT/EXCEPTは MySQL 8.0.31 以降。 古いとERROR 1064が出る。新しい構文の前にSELECT VERSION();- 古い環境では
EXISTS/NOT EXISTSに書き換える。NOT INは使わない(NULLが1つで0行になる) EXCEPTには向きがある。UNIONとINTERSECTには無い- 正規化は「異常を防ぐテクニック」ではなく、「1つの関係に1種類の事実だけを置いた結果」。 迷ったときの問いは「この表は何種類の事実を書いているか」
- SQLは宣言的。 「何が欲しいか」だけを書き、「どう取るか」はオプティマイザが決める。だからデータが増えてもSQLを書き直さなくていい
- 代償は「なぜ遅いかが自分の書いた行に無い」こと。 だから
EXPLAINを読む必要がある - 「関係」を捨てると、形の自由と横への広げやすさが手に入り、「事実は1箇所」と宣言的な問い合わせを失う。 仕事は消えず、アプリ側に移るだけ
- SQLには標準があるが、完全実装した製品は無い。
SHOW TABLES/AUTO_INCREMENT/ バッククォートは方言。移すときはそこを探す - 確かめ方の型は最後まで同じ:打つ → 件数を数える → 説明できない数字が出たら止まる
このシリーズは、本線の MySQLとSQLの基本の回で SELECT * FROM tasks; を打って、表が出たところから始まりました。 あのとき私は、その表が何なのか説明できませんでした。16本かけてやったのは、新しい構文を覚えることではなく、あの1行の下に何があるのかを掘ることでした。 いま言えるのは、あそこに出ていたのは表ではなく、「行の集合」という56年前の考え方を、MySQLというソフトが自分なりに実装したものだった、ということです。ズレも含めて、それがSQLでした。SQL深掘りシリーズは、ここまでです。 ここから先は、読む記事ではなく、自分のデータで打つSQLが続きを教えてくれます。本線に戻るときは、はじめにから順路をたどってください。
参考リンク
- roadmap.sh — SQL
- A Relational Model of Data for Large Shared Data Banks(E.F. Codd, 1970)
- MySQL 8.0 リファレンスマニュアル(日本語)
- MySQL — 集合演算(UNION / INTERSECT / EXCEPT)
- MySQL — UNION 句
- MySQL — INTERSECT 句
- MySQL — EXCEPT 句
- MySQL — EXISTS / NOT EXISTS を使ったサブクエリ
- MySQL — 文字列比較関数(LIKE とワイルドカードのエスケープ)
- MySQL — 外部キー制約
- MySQL — MySQL 標準SQLへの準拠
- PostgreSQL — 問い合わせの結合(UNION / INTERSECT / EXCEPT)
- PostgreSQL — SQL準拠
この段階の確認
【深掘り】SQL は、ここで終わりです。 次に進む前に、答えを見ずに試してください。
読み終えた直後は、たいてい「分かった気」になっています。 手が動くかどうかは別です。
説明できますか
WHEREで別名が使えないのはなぜか。SELECTの実行順序を言えますか- インデックスを張ったのに使われないのはどんなときか
- 同じ SELECT が2回目で変わるのはなぜか
手が動きますか
- 遅いクエリを特定し、書き換えて速くなったことを測る
- JOIN を3種類使い分け、行が減る・増える理由を説明しながら書く
- トランザクションの分離レベルを変え、見え方が変わることを確かめる
1つでも詰まったら、飛ばした回があります。 そこへ戻ってください。戻るのは後退ではありません。 戻り先の目安: SELECTの実行順序の回 / インデックスとEXPLAINの回 / 分離レベルの回
全部答えられたなら、次の段階へ進んでください。
次に読む
この記事は 【深掘り】SQL の 17 / 17 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 関係とは何か / 理論とSQLのズレ
- 集合演算(UNION / INTERSECT / EXCEPT)
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント