なぜ結合が必要か
注文一覧を出してほしいと頼まれて orders を読むと、顧客の欄には customer_id の 3 や 7 という数字しか並びません。
SQL クエリ
-- これだと誰の注文なのか分からない
SELECT id, customer_id, amount FROM orders;このまま渡しても「3 番って誰ですか」と聞き返されて終わりです。
名前は、別のテーブルに 1 つだけ置いてある
では最初から orders に顧客名を書いておけばよいかというと、それをやると改名のたびに全注文行を書き換える羽目になります。書き換え漏れがあれば、同じ顧客なのに行によって名前が違う、という壊れ方をします。
だから orders は customer_id だけを持ち、名前は customers に 1 つだけ置きます。そのぶん、読むときには 2 つのテーブルを突き合わせる手間が必要になります。この突き合わせが結合 (JOIN) です。
ON で「どの列どうしを合わせるか」を伝える
SQL クエリ
-- 本と出版社を publisher_id で突き合わせる
SELECT b.title, p.name
FROM books b
JOIN publishers p
ON b.publisher_id = p.id;FROM のテーブルに JOIN でもう 1 つ足し、ON に「どの列とどの列が同じなら同じものとみなすか」を書きます。b や p のような短い別名を付けておくと、どちらのテーブルの列なのかが一目で分かります。
ON を書き忘れると、データベースは全部の組み合わせを作ります。100 行と 1000 行なら 10 万行です。本番の数百万行どうしでやると、サーバが止まることもあります。
残る行の決まり方が何通りかある
結合には種類があり、違いは「どの行が結果に残るか」だけです。
| 書き方 | 結果に残る行 |
|---|---|
| INNER JOIN | 両方に相手が見つかった行だけ |
| LEFT OUTER JOIN | 左は全部残し、右に相手がなければ NULL |
| RIGHT OUTER JOIN | 右を全部残す |
| CROSS JOIN | 全部の組み合わせ |
同じテーブルに 2 つの別名を付けて、自分自身と突き合わせる書き方もあります。この章では、それぞれがどう違うかを 1 つずつ確かめていきます。
結合に使う列の型は両側で揃えてください。片方が
VARCHARで片方がINTだと、暗黙の型変換でインデックスが効かなくなり、行数が増えたとたんに極端に遅くなります。
テーブル構造
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount INT
);
INSERT INTO customers VALUES
(1, '田中'),
(2, '鈴木'),
(3, '佐藤');
INSERT INTO orders VALUES
(101, 1, 3000),
(102, 2, 1500),
(103, 1, 2000),
(104, 3, 5000);期待される出力
| order_id | customer_name | amount |
|---|---|---|
| 101 | 田中 | 3000 |
| 102 | 鈴木 | 1500 |
| 103 | 田中 | 2000 |
| 104 | 佐藤 | 5000 |