SQL Lab — MySQL を大容量で叩いて学ぶ

この章で答える問い

注文の表には顧客名がない。どこから持ってくるか
2 つの表をつなぐと、行数はどうなるか
なぜ最初から 1 つの表にしないのか

実物: 名前のない注文

orders の先頭 5 行

期待: 5 行。customer_id は数字

⌘/Ctrl + Enter で実行

SELECT * FROM orders LIMIT 5

customer_id に 1 とか 3 とか入っている。この数字は customers テーブルの id で、「顧客 1 の注文」という意味だ。名前が欲しければ customers を見に行く必要がある。手でやるなら、注文 1 行ごとに customers を id で引く。それを SQL でやるのが JOIN だ。

JOIN: 対応する行をくっつける

注文に顧客名を付ける

期待: 50 行。orders と同じ行数で、右に name が付く

⌘/Ctrl + Enter で実行

SELECT o.id, o.ordered_at, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id

FROM orders o JOIN customers c ON c.id = o.customer_id と読む。「orders の各行について、customer_id と id が一致する customers の行をくっつける」。o と c は表の別名で、両方に id という列があるので o.id、c.id と区別する。ON のあとが「何をもって対応とみなすか」の条件だ。

チケット前半はこれで終わり。ここから、JOIN で起きることをもう少し見る。

行が増える、行が消える

今度は逆に、customers を土台にして orders をくっつける。customers は 10 行だ。行数を予測してから実行する。

顧客に注文をくっつける

期待: 予測してから実行

⌘/Ctrl + Enter で実行

実行する前に予測する返る行数

SELECT c.name, o.id AS order_id, o.ordered_at
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id

50 行のはずだ。10 行ではない。佐藤さんには注文が 12 件あるので、佐藤さんの行が 12 回出る。JOIN の結果の行数は「対応の組み合わせの数」で、 1 人に注文が多数あれば、その人の行は注文の数だけ増える。

注文のある顧客は何人か

期待: 8 行。中村さんと加藤さんがいない

⌘/Ctrl + Enter で実行

SELECT DISTINCT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id

増えるだけでなく、消えもする。中村さんと加藤さんは注文がないので、対応する orders の行がなく、結果に出てこない。これが前章で「0 件の顧客が消えた」のと同じ現象で、ここまでの JOIN(INNER JOIN)は「対応がある行だけ」を返す。

LEFT JOIN: 左側は全部残す

注文がなくても顧客は残す

期待: 52 行。中村さんと加藤さんの行は order_id が NULL

⌘/Ctrl + Enter で実行

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id

LEFT JOIN は、左(FROM に書いた側)の行を必ず残す。対応する右の行がなければ、右の列は NULL で埋める。ここで第 1 章の NULL が「値がない」ではなく「対応する行がない」という意味で出てくる。増える(佐藤)・消える(中村)・NULL で残る、の 3 つを 1 枚で見る。

1 人に注文が複数あれば行はその数だけ増え、注文がなければ INNER では消え、LEFT では NULL で残る。

顧客ごとの注文数(チケット後半)

期待: 10 行。中村さんと加藤さんが 0

⌘/Ctrl + Enter で実行

SELECT c.name, COUNT(o.id) AS 注文数
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY 注文数 DESC

前章の COUNT(*) と COUNT(列) の違いがここで効く。中村さんの行は 1 行あるが、o.id は NULL なので COUNT(o.id) は 0 になる。COUNT(*) にすると NULL の行も 1 と数えて、注文 0 の人が 1 になってしまう。

注文したことのない顧客だけ

期待: 2 行

⌘/Ctrl + Enter で実行

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL

LEFT JOIN + IS NULL は「対応がない行」を探す定番の形だ。

驚き: LEFT JOIN なのに行が減る

「顧客一覧に、キャンセルされた注文を並べる(キャンセルのない人も残す)」を書く。行数を予測してから実行する。

WHERE で右側を絞る

期待: 予測してから実行

⌘/Ctrl + Enter で実行

実行する前に予測する返る行数

SELECT c.name, o.id AS cancelled_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled'
ORDER BY c.id

2 行のはずだ。10 行残るつもりで LEFT JOIN を書いたのに、キャンセルのある 2 人しか残らない。原因は WHERE の位置で、LEFT JOIN で右側が NULL になった行は、o.status = 'cancelled' を評価すると「不明」になって落ちる(第 2 章の 3 値論理)。右側の条件は ON に書く。

ON に書く

期待: 10 行。キャンセルのない人は NULL

⌘/Ctrl + Enter で実行

SELECT c.name, o.id AS cancelled_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
ORDER BY c.id

「LEFT JOIN なのに行が減った」と言われたら、WHERE に右側の列がないかを見る。ほぼそれだ。

上の 2 つのラボ(WHERE で右側を絞る = 2 行、ON に書く = 10 行)から、鈴木・田中・中村の 3 人を抜き出して、条件がどこで効いたかを並べる。

同じ条件でも、WHERE は結果ができた後に効いて NULL の行を落とし、ON は対応を決めるときに効いて対応のない人を NULL で残す。

3 つ以上をつなぐ、多対多をつなぐ

注文明細(order_items)には商品の番号だけがある。注文 → 明細 → 商品と 2 回つなげば、「誰が何を買ったか」が出る。

誰が何を買ったか

期待: 200 行

⌘/Ctrl + Enter で実行

SELECT o.id AS order_id, c.name AS 顧客, p.name AS 商品, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, oi.id

月ごとの売上(キャンセル除く)

期待: 3 行。6 月 96,920、7 月 50,560、8 月 75,870

⌘/Ctrl + Enter で実行

SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS 月, SUM(oi.quantity * oi.unit_price) AS 売上
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 月
ORDER BY 月

商品とカテゴリは「1 つの商品に複数のカテゴリ、1 つのカテゴリに複数の商品」の関係で、これは orders → customers のような「多対 1」では表せない。product_categories(product_id, category_id) という、組み合わせだけを持つ表を間に置く。中間テーブルと呼ぶ。

カテゴリごとの商品数

期待: 6 行

⌘/Ctrl + Enter で実行

SELECT cat.name, COUNT(pc.product_id) AS 商品数
FROM categories cat
LEFT JOIN product_categories pc ON pc.category_id = cat.id
GROUP BY cat.id, cat.name
ORDER BY 商品数 DESC

商品ごとにカテゴリを 1 列にまとめる

期待: 30 行。GROUP_CONCAT がカテゴリ名をカンマでつなぐ

⌘/Ctrl + Enter で実行

SELECT p.name, GROUP_CONCAT(cat.name ORDER BY cat.name SEPARATOR ', ') AS カテゴリ
FROM products p
LEFT JOIN product_categories pc ON pc.product_id = p.id
LEFT JOIN categories cat ON cat.id = pc.category_id
GROUP BY p.id, p.name
ORDER BY p.id

なぜ最初から 1 つの表にしないのか

JOIN は手間だ。注文の表に顧客名とメールを最初から入れておけば、つなぐ必要がない。それをしない理由を図で見る。

同じ事実(佐藤さんのメール)は 1 か所にだけ書き、読むときは JOIN でつなぎ直す。

注文が 12 件ある佐藤さんのメールを、注文の表に 12 回書くと、佐藤さんがメールを変えたとき 12 行を直す必要があり、1 行直し忘れると 2 つのメールが混在する。「同じ事実を 2 か所に書かない」ように表を分けることを正規化と呼ぶ。分ける代わりに、読むときにつなぐ手間(JOIN)を払う。第二部の 0 章で、あえてこの原則を破って列を複製する判断が出てくる。

表と表の「指す・指される」関係は、宣言できる。orders.customer_id が customers.id を指す、と宣言したものを外部キーと呼び、存在しない顧客の注文を入れようとすると DB が拒否する。

存在しない顧客の注文

期待: エラー。a foreign key constraint fails

⌘/Ctrl + Enter で実行

INSERT INTO orders (customer_id, status, ordered_at)
VALUES (999, 'paid', NOW())

JOIN は対応する行をくっつける。結果の行数は対応の組み合わせの数で、INNER は対応がない行を消し、LEFT は左側を残す。右側の条件は ON に書く。

確認

注文のない顧客も一覧に出すには、JOIN のどこを変えるか。

顧客を左に置いて LEFT JOIN にする。INNER JOIN は対応がある行だけを返すが、LEFT JOIN は左の行を必ず残し、対応する右の行がなければ右の列を NULL で埋める。

LEFT JOIN で書いたのに行が減ったとき、まず何を疑うか。

右側の表の列を WHERE に書いていないかを疑う。右が NULL で埋まった行は、その条件を評価すると「不明」になって落ちる。右側の条件は ON に書く。

LEFT JOIN の結果で注文数を数えるとき、COUNT(*) ではなく COUNT(o.id) を使うのはなぜか。

注文がない顧客も 1 行として残るので、COUNT(*) はその行を 1 と数えてしまう。COUNT(列) は列が NULL の行を数えないため、注文のない顧客が 0 になる。

次の章へ

ここまでは読むだけだった。次の章では注文を入れ、状態を変え、消す。そこで「WHERE を忘れた UPDATE は全行を変える」という、本番で最も怖い事故を、小さな表で先に起こしておく。