本線では SELECT / INSERT / UPDATE / DELETE の4つを打ちました。そのとき、表は1枚しかありませんでした。
これは【深掘り】です。本線の順路ではありません。 読まなくても先へは進めます。 本線のMySQLとSQLの基本の回の続きとして、
JOINだけを最後まで潰します。前回: DML(INSERT・UPDATE・DELETE) — WHERE無しのUPDATEは戻せない 作業場所はVMの中(Ubuntu Server)です。MySQL が動いていれば、それ以外の準備は要りません。この記事の中で練習用の表を作って、最後に消します。
表が2枚になった瞬間に、詰まります。
私が最初に覚えた説明はこれでした。
「
JOINは、2つの表を横に繋げる命令です」
この説明は、半分は合っていて、致命的に間違っています。 この説明のままだと、次の3つが一生説明できません。
LEFT JOINを書いたのに、結果がINNER JOINと同じになる(しかもエラーは出ない)WHEREに条件を1つ足しただけで、5行あった結果が2行になる- 表を3枚繋いだら、4行だったものが10行になった
「横に繋ぐ」というイメージだと、この3つは全部「バグ」に見えます。 実際にはどれも仕様どおりです。
正しい言い方はこうです。
JOINは、まず「行の組み合わせ」を全部作り、そのあと条件で絞っている。
この記事では、その「全部作る」ところを実際に画面に出します。 そこさえ見てしまえば、残りは全部その言い換えです。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
JOINは表を横に繋ぐ命令ではなく、行の組み合わせを作って条件で絞る命令だということ。CROSS JOINが素の姿で、INNER JOINはその絞り込みだということLEFT JOINはWHEREの書き方ひとつでINNER JOINに化けること。エラーは出ないので、自分で気づくしかないこと- 同じ条件でも
ONに書くかWHEREに書くかで結果が変わること。そしてそれが気まぐれではなく、実行される順番の違いだということ RIGHT JOINは覚えなくていいこと。MySQL にはFULL OUTER JOINが無いこと- 同じ表を2回使えること(自己結合)。そのときエイリアスが「あったほうがいい」ではなく必須である理由
JOINが遅いときに見るのは、たいてい結合列の索引1つだということ
この記事でやること
- 練習用の表を2つ作る。 部署が未設定の社員と、社員がいない部署を、わざと入れる
CROSS JOINを打って、5×4=20行が出るのを見る。 そこからINNER JOINを作るLEFT JOINで「いない側」を出す。WHEREに書いた場合とONに書いた場合を、両方実行して並べる- 自己結合で上司と部下を出す。 表を3枚に増やして、行数が増えるのを見る
- 20万行の表を作って
EXPLAINを読み、索引を張って読み直す - 作った表を全部消す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 結合 / JOIN | 複数の表から行を取り出して、1つの結果表にまとめること。 「繋ぐ」より「組み合わせて絞る」が実態に近い |
| 直積 / CROSS JOIN | 片方の全行 × もう片方の全行。5行と4行なら20行。条件をひとつも付けない結合 |
| 内部結合 / INNER JOIN | 直積のうち、条件に合った組み合わせだけ残したもの。片方にしか無い行は消える |
| 外部結合 / OUTER JOIN | 条件に合わなかった行も片側は残す結合。足りないほうは NULL で埋まる |
| NULL | 「値が無い」を表す特別な状態。 0でも空文字でもない。比較すると常に「不明」になるのがポイント |
| エイリアス | 表や列に付ける別名。FROM d_emp AS e の e。同じ表を2回使うときは、これが無いとどちらを指すか書けない |
| 索引 / インデックス | 表の中を探しやすくするための別データ。 本の索引と同じ。本線のパフォーマンスの回で扱ったもの |
練習用の表を作る
この記事は他の回のデータに依存しません。 ここに書いてある CREATE と INSERT を打てば、そのまま最後まで進めます。
VMの中で MySQL に入ります。
sudoは「管理者としてこのコマンドを実行する」という意味です。 MySQL を入れた直後の Ubuntu では、sudo mysqlでパスワードなしで管理者として入れる設定になっています。 本線の回で作ったアプリ用ユーザーで入りたい場合はmysql -u appuser -p appdbでも構いません。
入ったら、データベースを選びます。本線の回で作った appdb を使います。
USE appdb;
ERROR 1049 (42000): Unknown database 'appdb' と出たら、まだ無いだけなので作ります。
CREATE DATABASE appdb CHARACTER SET utf8mb4;
USE appdb;
utf8mb4は日本語を正しく入れるための文字コードの指定です。ここを間違えるとIncorrect string valueで弾かれます。理由は本線の文字コードの回にあります。
ここから作る表には、全部 d_ を頭に付けます。 d は deep(深掘り)のつもりです。既存の tasks などに触らないための、ただの自衛です。
まず、同じ名前の表が残っていたら消します。まだ何も作っていなければ、何も起きません。
DROP TABLE IF EXISTS d_proj;
DROP TABLE IF EXISTS d_emp;
DROP TABLE IF EXISTS d_dept;
部署の表です。
CREATE TABLE d_dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
INSERT INTO d_dept (dept_id, dept_name) VALUES
(10, '営業部'),
(20, '開発部'),
(30, '総務部'),
(40, '監査室');
社員の表です。
CREATE TABLE d_emp (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
dept_id INT NULL,
boss_id INT NULL
);
INSERT INTO d_emp (emp_id, emp_name, dept_id, boss_id) VALUES
(1, '佐藤 一郎', 10, NULL),
(2, '鈴木 花子', 10, 1),
(3, '高橋 健', 20, 1),
(4, '田中 実', 20, 3),
(5, '伊藤 望', NULL, 1);
この5行と4行には、意図があります。
| わざと入れたもの | 何のためか |
|---|---|
dept_id が NULL の社員(伊藤 望) | どの部署にも属していない人。 INNER JOIN で黙って消えるのを見るため |
| 社員が1人もいない部署(総務部・監査室) | 「いない側」を出すため。LEFT JOIN の向きの練習台 |
boss_id が NULL の社員(佐藤 一郎) | 上司がいない人=社長。 自己結合で社長が消えるのを見るため |
本来なら
dept_idには外部キーを付けます。 「存在しない部署番号を入れさせない」ための仕組みで、本線のテーブル設計と正規化の回で扱っています。 この記事では、あえて付けません。 外部キーを付けると MySQL が索引を自動で作ってしまい、後半の「索引が無いと遅い」を実演できなくなるからです。練習用の割り切りで、本番でやることではありません。
入ったことを確認します。
SELECT COUNT(*) FROM d_emp;
SELECT COUNT(*) FROM d_dept;
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
5行と4行。 この数字を覚えておいてください。次にこれを掛け算します。
まず全組み合わせを見る
INNER JOIN から入らないでください。 ここから入ると一生「横に繋ぐもの」のままです。
最初に打つのは、条件を1つも付けない結合です。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
CROSS JOIN d_dept AS d
ORDER BY e.emp_id, d.dept_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | 営業部 |
| 佐藤 一郎 | 開発部 |
| 佐藤 一郎 | 総務部 |
| 佐藤 一郎 | 監査室 |
| 鈴木 花子 | 営業部 |
| 鈴木 花子 | 開発部 |
| 鈴木 花子 | 総務部 |
| 鈴木 花子 | 監査室 |
| 高橋 健 | 営業部 |
| 高橋 健 | 開発部 |
| 高橋 健 | 総務部 |
| 高橋 健 | 監査室 |
| 田中 実 | 営業部 |
| 田中 実 | 開発部 |
| 田中 実 | 総務部 |
| 田中 実 | 監査室 |
| 伊藤 望 | 営業部 |
| 伊藤 望 | 開発部 |
| 伊藤 望 | 総務部 |
| 伊藤 望 | 監査室 |
+-----------+-----------+
20 rows in set (0.00 sec)
[スクリーンショット: 01_cross_join.png] 上の CROSS JOIN を実行して、20 rows in set まで写るように撮ってください。
5 × 4 = 20行。 佐藤さんが監査室に属している、という行まで出ています。これは事実として間違っています。 それでいいのです。
CROSS JOINは「ありえる組み合わせを全部並べただけ」で、正しさを一切見ていません。
そして、ここが全部の起点です。
INNER JOIN は、この20行を絞ったもの
いま出た20行に、条件を1つ足します。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
CROSS JOIN d_dept AS d
WHERE e.dept_id = d.dept_id
ORDER BY e.emp_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | 営業部 |
| 鈴木 花子 | 営業部 |
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
+-----------+-----------+
4 rows in set (0.00 sec)
20行が4行になりました。
そして、これとまったく同じ結果を返すのが、教科書に最初に出てくるこれです。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
INNER JOIN d_dept AS d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | 営業部 |
| 鈴木 花子 | 営業部 |
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
+-----------+-----------+
4 rows in set (0.00 sec)
INNER JOIN ... ON 条件は、CROSS JOINしてWHERE 条件で絞ったものと同じです。
これが分かると、残りは全部おまけになります。 以降の話は「20行のどれを残すか」の違いでしかありません。
もう1つ、大事なことがここで見えています。
伊藤 望が消えました。 dept_id が NULL だからです。NULL = 10 も NULL = 20 も真になりません(「等しくない」ですらなく「不明」です)。だから20行のうち伊藤さんの4行は、1つも条件を通りません。
エラーは出ません。黙って4行になります。 「社員一覧を出したのに、なぜか1人足りない」の正体は、たいていこれです。
実務では、この直積が本当に作られているわけではありません。 データベースは索引を使って、必要な組み合わせだけを取りに行きます(後半の
EXPLAINでそれを見ます)。 ただし「結果として何が出るか」は、直積を絞ったものと必ず一致します。 頭の中のモデルとしては、直積で考えて構いません。
INNER JOIN の書き方 — ON と USING
ON は両側の条件を自分で書く書き方です。列名が違っても書けます。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
INNER JOIN d_dept AS d ON e.dept_id = d.dept_id;
両側の列名がまったく同じときに限り、USING で短く書けます。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
INNER JOIN d_dept AS d USING (dept_id);
結果の行は同じですが、違いが1つあります。 SELECT * にすると分かります。
SELECT * FROM d_emp AS e INNER JOIN d_dept AS d ON e.dept_id = d.dept_id;
SELECT * FROM d_emp AS e INNER JOIN d_dept AS d USING (dept_id);
ONのほう …dept_idが2回出てきます(社員側と部署側)USINGのほう …dept_idは1回だけ。しかも先頭の列に来ます
USING は「同じ意味の列を1本にまとめる」ところまでやります。 そのぶん、e.dept_id のようにどちら側かを指定して書けなくなります(USING した列は表名を付けずに dept_id と書きます)。
私は ON に統一しています。 列名が同じであることに依存する書き方は、片方の表の列名を変えた瞬間に壊れるからです。
WHERE だけで書く「古い書き方」
こういう書き方も動きます。古いコードで必ず見ます。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e, d_dept AS d
WHERE e.dept_id = d.dept_id;
FROM にカンマ区切りで並べると CROSS JOIN になり、WHERE で絞ります。結果は INNER JOIN と同じ4行です。
それでも新しく書くときは JOIN を使ってください。 理由は2つです。
WHEREを書き忘れると、黙って直積が出ます。 表が3枚あったら、事故の規模が桁で変わります- 「繋ぐ条件」と「絞り込む条件」が
WHEREに混ざります。 どれが結合でどれが検索条件か、後から読めません
-- 読める: 繋ぐ条件は ON、探す条件は WHERE
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
INNER JOIN d_dept AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '開発部';
そしてこの書き分けが、次の山場でそのまま効いてきます。
LEFT JOIN で「いない側」を出す
INNER JOIN は両側にある組み合わせしか出しません。 業務で欲しいのは、たいてい逆です。
- どの部署にも属していない社員は誰か
- 社員が1人もいない部署はどこか
LEFT JOIN は、左の表の行を必ず全部残します。 相手が見つからなければ、右側の列は NULL で埋まります。
社員を全員出す(部署が無くても)
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | 営業部 |
| 鈴木 花子 | 営業部 |
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
| 伊藤 望 | NULL |
+-----------+-----------+
5 rows in set (0.00 sec)
5行になりました。 伊藤 望が NULL 付きで戻ってきています。
「属していない人だけ」が欲しいなら、NULL で絞ります。
SELECT e.emp_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
+-----------+
| emp_name |
+-----------+
| 伊藤 望 |
+-----------+
1 row in set (0.00 sec)
= NULLとは書けません。必ずIS NULLです。NULLとの比較は真でも偽でもなく「不明」になり、WHEREは不明を通さないので、WHERE d.dept_id = NULLは必ず0行になります。エラーも出ません。
社員がいない部署を出す(向きを変える)
左右を入れ替えるだけです。
SELECT d.dept_name, e.emp_name
FROM d_dept AS d
LEFT JOIN d_emp AS e ON d.dept_id = e.dept_id
ORDER BY d.dept_id;
+-----------+-----------+
| dept_name | emp_name |
+-----------+-----------+
| 営業部 | 佐藤 一郎 |
| 営業部 | 鈴木 花子 |
| 開発部 | 高橋 健 |
| 開発部 | 田中 実 |
| 総務部 | NULL |
| 監査室 | NULL |
+-----------+-----------+
6 rows in set (0.00 sec)
6行です。 営業部と開発部が2行ずつに増え、総務部と監査室が NULL 付きで出ています。
ここも「横に繋ぐ」では説明できません。 部署は4行しかないのに結果は6行です。組み合わせを作っているから増えます。
空の部署だけ出すなら、同じく IS NULL です。
SELECT d.dept_name
FROM d_dept AS d
LEFT JOIN d_emp AS e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
+-----------+
| dept_name |
+-----------+
| 総務部 |
| 監査室 |
+-----------+
2 rows in set (0.00 sec)
IS NULLで調べる列は、NOT NULLの列を選んでください。 ここではe.emp_id(主キー)にしています。e.boss_idのようにもともとNULLが入りうる列で判定すると、「相手が見つからなかった」のか「見つかったが値がNULL」なのか区別できず、佐藤さんの部署まで空扱いになります。私はこれで一度おかしな集計を出しました。
必ずやる間違い — LEFT JOIN が INNER JOIN に化ける
これは、ほぼ全員が一度やります。 私もやりました。
「部署が入っている人だけ見たいな」と思って、さっきのクエリに条件を足します。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NOT NULL;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | 営業部 |
| 鈴木 花子 | 営業部 |
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
+-----------+-----------+
4 rows in set (0.00 sec)
4行。INNER JOIN と完全に同じです。
LEFT JOIN と書いてあるのに、LEFT JOIN として働いていません。 そしてエラーも警告も出ません。
理由は、順番です。
LEFT JOINが5行を作る(伊藤 望は右側がNULL)- そのあと
WHERE d.dept_id IS NOT NULLが、そのNULLの行を捨てる
LEFT JOINがわざわざ残した行を、WHEREが後から始末しています。
LEFT JOIN の右側の列を WHERE で判定した瞬間、それは INNER JOIN です。 例外は IS NULL(=「いなかった行だけ」を狙って残す)で、これだけは目的に合っています。
気づき方は1つです。 LEFT JOIN を書いたら、WHERE に右側の表の列が出ていないかを毎回見てください。出ていたら、それは意図か事故かを判断する場所です。
ON に書くか WHERE に書くか — 両方実行して並べる
同じ条件を、置く場所だけ変えます。 「開発部の人には部署名を出す」というクエリです。
まず WHERE に書いた場合。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '開発部'
ORDER BY e.emp_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
+-----------+-----------+
2 rows in set (0.00 sec)
次に ON に書いた場合。 変えたのは条件の置き場所だけです。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
LEFT JOIN d_dept AS d
ON e.dept_id = d.dept_id AND d.dept_name = '開発部'
ORDER BY e.emp_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | NULL |
| 鈴木 花子 | NULL |
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
| 伊藤 望 | NULL |
+-----------+-----------+
5 rows in set (0.00 sec)
[スクリーンショット: 02_on_と_where.png] 上の2つを続けて実行し、2行と5行が並んで写るように撮ってください。
2行と5行。 同じ条件、同じ表、同じ LEFT JOIN です。
| どこに書くか | 意味 | 結果 |
|---|---|---|
ON | 「相手として認める条件」。 合わなければその社員は残り、右側が NULL になる | 5行(全社員が出る) |
WHERE | 「最後に結果から捨てる条件」。 合わない行そのものが消える | 2行(開発部だけ) |
覚え方はこれで足ります。
ONは「誰と組ませるか」。WHEREは「できあがった結果から何を捨てるか」。
INNER JOIN では、この2つの結果は一致します。 どうせ相手がいない行は消えるからです。だから INNER JOIN で覚えた感覚のまま LEFT JOIN を書くと、必ず事故ります。
日本語での言い分けも決めておくと楽です。
- 「全社員を出して、開発部の人には部署名を付ける」 →
ON - 「開発部の人だけ出す」 →
WHERE
「全部出して、あるものには〜を付ける」と言えたら ON です。
RIGHT JOIN と、MySQL に無い FULL OUTER JOIN
RIGHT JOIN は LEFT JOIN の鏡です。右の表の行を必ず残します。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
RIGHT JOIN d_dept AS d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
結果は6行で、さっきの「部署を左にした LEFT JOIN」とまったく同じです。
なので、RIGHT JOIN は使わなくて構いません。
LEFT JOINで書けないものは1つもありません。 表の順番を入れ替えるだけですFROMに書いた順に読めなくなります。RIGHT JOINがあると「上から順に足していく」読み方ができず、右から巻き戻して読むことになります- 3表以上で混ざると、ほぼ読めません。
LEFTとRIGHTが交互に出てくるクエリは、書いた本人でも追えなくなります
「LEFT に統一する」で決めてしまうのが一番安全です。
FULL OUTER JOIN は MySQL に無い
「両側の余り物を、両方とも出したい」 ことがあります。部署なしの社員も、社員なしの部署も、1つの結果に。
これは FULL OUTER JOIN と言い、PostgreSQL などにはあります。MySQL にはありません。
-- MySQL では動きません
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
FULL OUTER JOIN d_dept AS d ON e.dept_id = d.dept_id;
ERROR 1064 (42000): You have an error in your SQL syntax; ...near 'FULL OUTER JOIN d_dept AS d ON e.dept_id = d.dept_id' at line 3
UNION で自作します。 LEFT と RIGHT を縦に足すだけです。
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM d_emp AS e
RIGHT JOIN d_dept AS d ON e.dept_id = d.dept_id;
+-----------+-----------+
| emp_name | dept_name |
+-----------+-----------+
| 佐藤 一郎 | 営業部 |
| 鈴木 花子 | 営業部 |
| 高橋 健 | 開発部 |
| 田中 実 | 開発部 |
| 伊藤 望 | NULL |
| NULL | 総務部 |
| NULL | 監査室 |
+-----------+-----------+
7 rows in set (0.00 sec)
7行。 4件の正常な組み合わせ+部署なしの社員1人+社員なしの部署2つです。これが「両側の全部」です。
ここで UNION の性質を1つ覚えてください。
UNIONは重複行を消します。 上と下に同じ4行が出ていますが、結果には1回しか出ていませんUNION ALLは消しません。 速いですが、この用途では4行が二重に出ます
重複を消す UNION は、内部で並べ替えをしています。 行が多いと重くなるので、FULL OUTER JOIN が本当に必要かは一度疑ってください。 実務では「余っている側だけ知りたい」ことのほうが多く、その場合は LEFT JOIN + IS NULL の1本で足ります。
自己結合 — 同じ表を2回使う
d_emp には boss_id があります。上司も社員なので、同じ d_emp の中にいます。
「社員名と、その上司の名前」を1行で出したい。繋ぐ相手が自分自身です。
まず、エイリアスなしで書いてみます。
SELECT emp_name, emp_name
FROM d_emp
JOIN d_emp ON boss_id = emp_id;
ERROR 1066 (42000): Not unique table/alias: 'd_emp'
エラーになります。 これは意地悪ではなく、書きようがないからです。
emp_name と書いたとき、それが部下の名前なのか上司の名前なのか、決める方法がありません。boss_id = emp_id も同様で、どちらの emp_id か決まりません。
だから、別名を付けて「2つの表」に見せます。
SELECT
e.emp_name AS 社員,
b.emp_name AS 上司
FROM d_emp AS e
LEFT JOIN d_emp AS b ON e.boss_id = b.emp_id
ORDER BY e.emp_id;
+-----------+-----------+
| 社員 | 上司 |
+-----------+-----------+
| 佐藤 一郎 | NULL |
| 鈴木 花子 | 佐藤 一郎 |
| 高橋 健 | 佐藤 一郎 |
| 田中 実 | 高橋 健 |
| 伊藤 望 | 佐藤 一郎 |
+-----------+-----------+
5 rows in set (0.00 sec)
e が部下側、b が上司側です。 同じ d_emp を2回読んでいますが、データベースにとっては「たまたま中身が同じ2つの表」でしかありません。自己結合に特別な文法はありません。エイリアスが必須になるだけです。
ここで LEFT JOIN を使った理由も、はっきりしています。
INNER JOIN に変えると佐藤 一郎が消えて4行になります。boss_id が NULL で、相手が見つからないからです。「社員一覧を出したら社長だけいない」という、いかにも気づきにくい欠落が起きます。
自己結合は
LEFT JOINから書き始めてください。 木構造(上司と部下、カテゴリと親カテゴリ、返信と元コメント)は、必ず「根っこ」だけ相手がいません。
自己結合は、同じ表の中で行どうしを比べたいとき全般に使えます。 例えば「同じ部署の2人組」を出すならこうです。
SELECT a.emp_name, b.emp_name
FROM d_emp AS a
JOIN d_emp AS b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;
+-----------+-----------+
| emp_name | emp_name |
+-----------+-----------+
| 佐藤 一郎 | 鈴木 花子 |
| 高橋 健 | 田中 実 |
+-----------+-----------+
2 rows in set (0.00 sec)
a.emp_id < b.emp_id が効いています。 これが無いと「佐藤と佐藤」(自分自身)と、「佐藤と鈴木」「鈴木と佐藤」(同じ組の裏返し)まで出ます。ON に書けるのは等号だけではありません。
3つ以上の JOIN — 順番と括弧と、行数の爆発
表を1枚足します。部署ごとのプロジェクトの表です。
CREATE TABLE d_proj (
proj_id INT PRIMARY KEY,
dept_id INT NOT NULL,
proj_name VARCHAR(50) NOT NULL
);
INSERT INTO d_proj (proj_id, dept_id, proj_name) VALUES
(101, 10, '新規開拓'),
(102, 10, '既存フォロー'),
(201, 20, 'サイト刷新'),
(202, 20, 'アプリ開発'),
(203, 20, '基盤更改');
営業部に2件、開発部に3件。 総務部と監査室には0件です。
社員・部署・プロジェクトを繋ぎます。
SELECT e.emp_name, d.dept_name, p.proj_name
FROM d_emp AS e
JOIN d_dept AS d ON e.dept_id = d.dept_id
JOIN d_proj AS p ON d.dept_id = p.dept_id
ORDER BY e.emp_id, p.proj_id;
+-----------+-----------+--------------+
| emp_name | dept_name | proj_name |
+-----------+-----------+--------------+
| 佐藤 一郎 | 営業部 | 新規開拓 |
| 佐藤 一郎 | 営業部 | 既存フォロー |
| 鈴木 花子 | 営業部 | 新規開拓 |
| 鈴木 花子 | 営業部 | 既存フォロー |
| 高橋 健 | 開発部 | サイト刷新 |
| 高橋 健 | 開発部 | アプリ開発 |
| 高橋 健 | 開発部 | 基盤更改 |
| 田中 実 | 開発部 | サイト刷新 |
| 田中 実 | 開発部 | アプリ開発 |
| 田中 実 | 開発部 | 基盤更改 |
+-----------+-----------+--------------+
10 rows in set (0.00 sec)
4行だったものが10行になりました。
社員は増えていません。 営業部の2人が2件ずつ、開発部の2人が3件ずつ。2×2 + 2×3 = 10 です。
これが実務で一番よく踏む地雷です。
SELECT COUNT(*) FROM d_emp;
-- 5
SELECT COUNT(*)
FROM d_emp AS e
JOIN d_dept AS d ON e.dept_id = d.dept_id
JOIN d_proj AS p ON d.dept_id = p.dept_id;
-- 10
「社員数を数えたら、社員数の2倍が出た」の正体です。表を1枚足すたびに、行は増えうると思ってください。
数えたいものが社員なら、COUNT(*) ではなく COUNT(DISTINCT ...) です。
SELECT COUNT(DISTINCT e.emp_id)
FROM d_emp AS e
JOIN d_dept AS d ON e.dept_id = d.dept_id
JOIN d_proj AS p ON d.dept_id = p.dept_id;
+--------------------------+
| COUNT(DISTINCT e.emp_id) |
+--------------------------+
| 4 |
+--------------------------+
合計金額なども同じです。 3表繋いだ結果で SUM(price) を取ると、組み合わせの数だけ同じ金額を足します。 金額が2倍3倍になっていたら、まずここを疑ってください。
JOIN は上から順に積まれる
JOIN は3つを同時に繋いでいません。 上から順に、こう積まれています。
d_empとd_deptを繋ぐ → 中間結果ができる- その中間結果と
d_projを繋ぐ
だから、順番が結果を変えます。 ここに LEFT JOIN を混ぜると、はっきり分かります。
SELECT e.emp_name, d.dept_name, p.proj_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
JOIN d_proj AS p ON d.dept_id = p.dept_id
ORDER BY e.emp_id;
結果は10行です。 LEFT JOIN と書いたのに、伊藤 望はいません。
理由は山場②とまったく同じ構造です。
LEFT JOINが伊藤 望をd.dept_id = NULLで残す- 次の
JOIN(内部結合)が、NULL = p.dept_idを通さず捨てる
LEFT JOINの後ろにINNER JOINを積むと、LEFTが打ち消されます。
直し方は2つあります。
(1) 以降も全部 LEFT JOIN にする
SELECT e.emp_name, d.dept_name, p.proj_name
FROM d_emp AS e
LEFT JOIN d_dept AS d ON e.dept_id = d.dept_id
LEFT JOIN d_proj AS p ON d.dept_id = p.dept_id
ORDER BY e.emp_id;
+-----------+-----------+--------------+
| emp_name | dept_name | proj_name |
+-----------+-----------+--------------+
| 佐藤 一郎 | 営業部 | 新規開拓 |
| 佐藤 一郎 | 営業部 | 既存フォロー |
| 鈴木 花子 | 営業部 | 新規開拓 |
| 鈴木 花子 | 営業部 | 既存フォロー |
| 高橋 健 | 開発部 | サイト刷新 |
| 高橋 健 | 開発部 | アプリ開発 |
| 高橋 健 | 開発部 | 基盤更改 |
| 田中 実 | 開発部 | サイト刷新 |
| 田中 実 | 開発部 | アプリ開発 |
| 田中 実 | 開発部 | 基盤更改 |
| 伊藤 望 | NULL | NULL |
+-----------+-----------+--------------+
11 rows in set (0.00 sec)
11行。伊藤 望が戻りました。
(2) 括弧で「先に繋ぐ組」を指定する
部署とプロジェクトを先に繋いでから、社員にぶら下げます。
SELECT e.emp_name, d.dept_name, p.proj_name
FROM d_emp AS e
LEFT JOIN (
d_dept AS d
JOIN d_proj AS p ON d.dept_id = p.dept_id
) ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
同じ11行になります。 括弧の中で「部署+プロジェクト」という1つのかたまりを先に作り、それに対して社員から LEFT JOIN しています。
普段は (1) で足ります。 括弧が要るのは、「かたまりの中では内部結合、外からは外部結合」を明示したいときです。
そして、LEFT JOIN を積むときの実務的なルールはこれです。
LEFT JOINを1本書いたら、それ以降は全部LEFT JOINにする。
途中で INNER JOIN に戻すと、何行になるかを読むのが人間には無理になります。
JOIN が遅いとき — EXPLAIN で見る
ここまでの表は5行と4行です。 どう書いても一瞬で返ります。遅さの話は、行が増えてから初めて意味を持ちます。
20万行の表を作ります。
MySQL の再帰CTEは既定で1000回までしか回らないので、上限を上げます。
SET SESSION cte_max_recursion_depth = 1000000;
部署を1000件。
CREATE TABLE d_dept_big (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
INSERT INTO d_dept_big (dept_id, dept_name)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT n, CONCAT('部署', n) FROM seq;
社員を20万件。dept_id には索引を張りません。
CREATE TABLE d_emp_big (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
dept_id INT NOT NULL
);
INSERT INTO d_emp_big (emp_id, emp_name, dept_id)
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 FROM seq;
数十秒かかります。 終わったら確認します。
SELECT COUNT(*) FROM d_emp_big;
+----------+
| COUNT(*) |
+----------+
| 200000 |
+----------+
「部署777の社員を出す」を打ちます。
SELECT e.emp_name
FROM d_emp_big AS e
JOIN d_dept_big AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '部署777';
...
200 rows in set (0.42 sec)
200行を出すのに0.4秒。 遅いかどうかは、中で何をしたかを見ないと判断できません。
先頭に EXPLAIN を付けます。
EXPLAIN SELECT e.emp_name
FROM d_emp_big AS e
JOIN d_dept_big AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '部署777';
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 10.00 | Using where |
| 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 199568 | 10.00 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
数字は環境で変わります。 見るのは、そこではありません。
| 列 | 見かた |
|---|---|
table | 上から実行される順。 ここでは部署 d を先に読み、その結果で社員 e を探している |
type | 一番大事。 ALL は全行読んでいる。ref / eq_ref / const なら索引で絞れている |
key | 実際に使った索引。 NULL なら索引を使っていない |
rows | 読むと見込んでいる行数。 あくまで見積もりで、実測値ではない |
Extra | Using where は絞り込みあり。Using join buffer は「索引が無いので、まとめてメモリ上で突き合わせている」 |
問題は2行目です。
社員側が
type: ALLでkey: NULL。20万行を全部読んでいます。
200行取り出すために20万行読んでいるということです。索引が無いので、それ以外にやりようがありません。
結合列に索引を張ります。
CREATE INDEX idx_d_emp_big_dept ON d_emp_big (dept_id);
もう一度 EXPLAIN を打ちます。
EXPLAIN SELECT e.emp_name
FROM d_emp_big AS e
JOIN d_dept_big AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '部署777';
+----+-------------+-------+------+--------------------+--------------------+---------+---------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+--------------------+--------------------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 10.00 | Using where |
| 1 | SIMPLE | e | ref | idx_d_emp_big_dept | idx_d_emp_big_dept | 4 | appdb.d.dept_id | 200 | 100.00 | NULL |
+----+-------------+-------+------+--------------------+--------------------+---------+---------------+------+----------+-------------+
ALL が ref に変わり、rows が 199568 から 200 になりました。
[スクリーンショット: 03_explain_索引の前後.png] 索引を張る前と後の EXPLAIN が、type の ALL と ref が見比べられる形で写るように撮ってください。
実測も見ます。
SELECT e.emp_name
FROM d_emp_big AS e
JOIN d_dept_big AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '部署777';
...
200 rows in set (0.00 sec)
0.42秒が0.00秒になりました。
JOIN が遅いときに見る順番は、これで足ります。
EXPLAINを付けるtypeがALLの行を探す- その表の、
ONに書いた列に索引があるか見る - 無ければ張って、もう一度
EXPLAINを打つ
大事なのは、4番のあとにもう一度測ることです。 索引は書き込みを遅くし、容量も食います。張って速くなったことを確認していないなら、それは「たぶん効くはず」でしかありません。 この型そのものは、本線のパフォーマンス改善とEXPLAINの回でやったものと同じです。測る → 1つだけ直す → もう一度測る。
外部キーを付けていれば、この索引は自動で作られていました。 MySQL は外部キーを作るとき、必要な索引を勝手に用意します。 「外部キーを付けたら速くなった」の正体はこれです。 詳しくは本線のテーブル設計と正規化の回にあります。
ONに書いた列には索引がある、が原則だと思ってください。
なお、rows は見積もりです。 統計が古いと実態とずれます。おかしいと思ったら更新します。
ANALYZE TABLE d_emp_big;
実際に何行読んだかを見たいときは、EXPLAIN ANALYZE があります(MySQL 8.0 系の新しめのものと、PostgreSQL で使えます)。本当にクエリを実行して、実測時間を返します。
EXPLAIN ANALYZE SELECT e.emp_name
FROM d_emp_big AS e
JOIN d_dept_big AS d ON e.dept_id = d.dept_id
WHERE d.dept_name = '部署777';
ERROR 1064 で弾かれたら、そのバージョンには無いだけです。 ふつうの EXPLAIN で十分読めます。
JOIN で書くか、サブクエリで書くか
同じことは、たいていサブクエリでも書けます。「プロジェクトを持つ部署の社員」は、JOIN でも IN でも書けます。現在の MySQL は、多くの場合どちらも同じような実行計画に書き換えます(EXPLAIN を両方に付けると、同じ形になることがよくあります)。なので「サブクエリは遅い」は、いまはもう自動的には言えません。 私の基準は、結果に相手側の列を出したいかどうかです。部署名やプロジェクト名を表示したいなら JOIN しか選べません。「いるかどうかだけ」を条件にしたいなら EXISTS や IN のほうが素直で、しかも行が増えません(JOIN は前述のとおり件数が膨らみ、COUNT や SUM を壊します)。「出したいなら JOIN、絞りたいだけなら EXISTS」 で選べば、たいてい外しません。迷ったら両方書いて EXPLAIN を比べてください。 遅いほうを捨てるのが一番確実です。
つまずいたところ
遠回りした順に書きます。
1. 「LEFT JOIN なのに欠けている」を、データの不備だと思い込んだ
社員一覧が1人足りない。データが壊れていると思って、d_emp を何度も SELECT しました。 データは正しく、WHERE に右側の列を書いていただけでした。エラーが出ないので、疑う場所が最後まで分かりませんでした。
いまは、LEFT JOIN を書いたら WHERE を必ず読み返します。
2. WHERE d.dept_id = NULL で0行になり、5分悩んだ
「部署なしの人を出そう」として = NULL と書きました。エラーも警告も出ず、静かに0行です。 IS NULL を知ってはいたのに、手が = を打ちました。NULL は比較できない、を身体で覚えるまでに数回かかりました。
3. 3表繋いだ結果で COUNT(*) を取り、社員数を2倍で報告しかけた
これが一番危なかったところです。数字は「それっぽく」出ます。 5人のはずが10人になっても、10という数字自体は何も言ってくれません。元の表の件数と突き合わせる癖が無かったら、そのまま出していました。
JOIN した結果を数えるときは、必ず COUNT(DISTINCT 主キー) にすると決めました。
4. 自己結合で社長が消えた
INNER JOIN で書いて4行。「1人足りない」に気づくまで、結果を3回見返しました。 木構造は根っこに親がいない、という当たり前が、SQLだと見えなくなります。
5. 20万行を入れようとして Recursion depth exceeded で止まった
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; を先に打てば通ります。SESSION なので、MySQL を抜ければ元に戻ります。
6. 索引を張っても速くならないケースにぶつかった
最初は最小の表(4部署)で試していて、索引を張っても EXPLAIN が ALL のままでした。壊れていたのではありません。 4部署しかないと、1つの部署が全体の25%に当たります。「20万行のうち5万行を索引経由で拾う」より「全部読む」ほうが速いとデータベースが判断したのです。
索引は「張れば効く」ものではなく、絞り込める割合で効きが決まります。 この記事で部署を1000件にしたのは、そのためです。
後片づけ
練習用の表を全部消します。 d_ で始まるものだけです。
DROP TABLE IF EXISTS d_proj;
DROP TABLE IF EXISTS d_emp;
DROP TABLE IF EXISTS d_dept;
DROP TABLE IF EXISTS d_emp_big;
DROP TABLE IF EXISTS d_dept_big;
消えたことと、他の表が残っていることを確認します。
SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| knex_migrations |
| tasks |
+-----------------+
d_ で始まる表が1つも無ければ成功です。 表示される中身は、あなたがここまでに作ったものによって変わります。d_ が消えていることだけ見てください。
MySQL を抜けます。
EXIT;
cte_max_recursion_depth は元に戻ります。 SET SESSION は接続中だけの設定なので、抜けた時点で既定値です。設定ファイルは触っていません。
もし途中で appdb 自体をこの記事のために新規作成したなら、それも消して構いません。本線で使っている場合は消さないでください。
-- 本線で appdb を使っているなら、これは打たないこと
DROP DATABASE appdb;
まとめ
この記事で理解できたことを、5行にします。
JOINは表を横に繋ぐ命令ではない。 行の組み合わせを作り、条件で絞っている。CROSS JOINが素の姿で、INNER JOINはその絞り込みLEFT JOINは「左を全部残す」だけ。 右側の列をWHEREで判定した瞬間にINNER JOINになる。エラーは出ないONは「誰と組ませるか」、WHEREは「結果から何を捨てるか」。INNER JOINでは一致するので、その感覚のままLEFT JOINを書くと事故るRIGHT JOINは書かなくていい(LEFTに統一する)。FULL OUTER JOINは MySQL に無いのでUNIONで作る。自己結合はエイリアスが必須で、LEFT JOINから書き始める- 表を足すたびに行は増える。
COUNTとSUMが真っ先に壊れる。遅いときはEXPLAINのtype: ALLを探し、ONに書いた列の索引を見る
この5つは、全部1つのことの言い換えです。
組み合わせを作って、絞っている。
そこに戻れば、知らない形の JOIN が出てきても、何行になるかは自分で数えられます。
参考リンク
- roadmap.sh — SQL
- roadmap.sh — Backend
- MySQL 公式ドキュメント
- PostgreSQL — Table Expressions(FULL OUTER JOIN の定義)
- PostgreSQL — EXPLAIN の使い方
- SQLite — SELECT 構文
次に読む
この記事は 【深掘り】SQL の 6 / 17 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 内部結合・外部結合・自己結合・交差結合
- ON と WHERE の違い、結合の最適化
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント