JOINの仕組み — 表を繋いでいない

JOINの仕組み。二組の小さなデータカードと、条件に合う組を選ぶ虫眼鏡。

本線では 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つだということ

この記事でやること

  1. 練習用の表を2つ作る。 部署が未設定の社員と、社員がいない部署を、わざと入れる
  2. CROSS JOIN を打って、5×4=20行が出るのを見る。 そこから INNER JOIN を作る
  3. LEFT JOIN で「いない側」を出す。 WHERE に書いた場合と ON に書いた場合を、両方実行して並べる
  4. 自己結合で上司と部下を出す。 表を3枚に増やして、行数が増えるのを見る
  5. 20万行の表を作って EXPLAIN を読み、索引を張って読み直す
  6. 作った表を全部消す

先に知る言葉

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

言葉意味
結合 / 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 に入ります。

bash
$ sudo 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つです。

  1. WHERE を書き忘れると、黙って直積が出ます。 表が3枚あったら、事故の規模が桁で変わります
  2. 「繋ぐ条件」と「絞り込む条件」が 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 として働いていません。 そしてエラーも警告も出ません。

理由は、順番です。

  1. LEFT JOIN が5行を作る(伊藤 望は右側が NULL)
  2. そのあと 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つを同時に繋いでいません。 上から順に、こう積まれています。

  1. d_emp と d_dept を繋ぐ → 中間結果ができる
  2. その中間結果と 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 と書いたのに、伊藤 望はいません。

理由は山場②とまったく同じ構造です。

  1. LEFT JOIN が伊藤 望を d.dept_id = NULL で残す
  2. 次の 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読むと見込んでいる行数。 あくまで見積もりで、実測値ではない
ExtraUsing 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 が遅いときに見る順番は、これで足ります。

  1. EXPLAIN を付ける
  2. type が ALL の行を探す
  3. その表の、ON に書いた列に索引があるか見る
  4. 無ければ張って、もう一度 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行にします。

  1. JOIN は表を横に繋ぐ命令ではない。 行の組み合わせを作り、条件で絞っている。CROSS JOIN が素の姿で、INNER JOIN はその絞り込み
  2. LEFT JOIN は「左を全部残す」だけ。 右側の列を WHERE で判定した瞬間に INNER JOIN になる。エラーは出ない
  3. ON は「誰と組ませるか」、WHERE は「結果から何を捨てるか」。 INNER JOIN では一致するので、その感覚のまま LEFT JOIN を書くと事故る
  4. RIGHT JOIN は書かなくていい(LEFT に統一する)。FULL OUTER JOIN は MySQL に無いので UNION で作る。自己結合はエイリアスが必須で、LEFT JOIN から書き始める
  5. 表を足すたびに行は増える。 COUNT と SUM が真っ先に壊れる。遅いときは EXPLAIN の type: ALL を探し、ON に書いた列の索引を見る

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

組み合わせを作って、絞っている。

そこに戻れば、知らない形の JOIN が出てきても、何行になるかは自分で数えられます。

参考リンク


次に読む

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

記事
← 前の記事DML(INSERT・UPDATE・DELETE) — WHERE無しのUPDATEは戻せない
→ 次の記事GROUP BYと集計関数 — 付けたら行が減った

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

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

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

Backend›SQL — Joins
  • 内部結合・外部結合・自己結合・交差結合
  • ON と WHERE の違い、結合の最適化

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

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

コメント

コメントする

目次