SQL Scale Lab｜3,200万行で学ぶMySQL・SQL実践教材

この章で答える問い

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

実際のテーブルを確認: 顧客名の書かれていない注文データ

orders の先頭 5 行

期待: 5 行。customer_id は数字

SELECT * FROM orders LIMIT 5

customer_id 列に 1 や 3 といった数値が入っています。この数値は customers テーブルの主キー（id）であり、「顧客 ID 1 の顧客による注文」を意味しています。注文一覧に顧客の名前を表示したい場合、customers テーブルを参照する必要があります。手動でやるなら、注文 1 件ごとに customers テーブルの該当 id を探すことになりますが、それを SQL 上で自動的に行うのが JOIN（結合） です。

JOIN: 対応する行をつなぎ合わせる

注文に顧客名を付ける

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

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 の後ろには「どのような条件で行同士を対応付けるか（結合条件）」を指定します。

これでチケットの前半「注文一覧に顧客名を表示する」は達成できました。ここからは、テーブル結合によってデータ件数がどのように変動するかを見ていきましょう。

結合による行数の変化: 行が増えるケース・消えるケース

今度は逆に、customers テーブルを起点にして orders テーブルを結合してみます。customers テーブルには 10 行のデータがあります。結果が何行になるか予想してから実行してみてください。

顧客に注文をくっつける

期待: 予測してから実行

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

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

結果は 10 行ではなく、50 行になります。たとえば佐藤さんという顧客が 12 回の注文を行っている場合、結合結果には佐藤さんの行が 12 回登場します。結合結果の行数は「条件に合致した組み合わせの総数」となるため、1 人の顧客に対して複数の注文が紐付いている場合、その顧客の行数は注文の数だけ増えることになります。

注文のある顧客は何人か

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

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

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 が「値が存在しない」だけでなく、「結合相手が存在しない」という意味合いでも現れてきます。行が増えるパターン、消えるパターン、LEFT JOIN で NULL として残るパターンの違いを図で確認してみましょう。

1人の顧客に複数注文があれば行数は増え、注文がなければ INNER JOIN では除外され、LEFT JOIN では NULL で保持されます。

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

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

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 件としてカウントされてしまい、注文ゼロの顧客が「1 件」と誤認されてしまいます。

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

期待: 2 行

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

LEFT JOIN と WHERE ... IS NULL の組み合わせは、「紐付く相手が存在しないレコード」を抽出する実務での鉄板パターンです。

注意すべき挙動: LEFT JOIN を使ったのに行が除外される落とし穴

「全顧客の一覧に、キャンセルされた注文を並べる（キャンセルのない顧客も除外せずに残す）」という要件を考えてみます。実行して結果が何行になるか確かめてください。

WHERE で右側を絞る

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

全顧客 10 行が残ることを期待して LEFT JOIN を記述したにもかかわらず、結果は 2 行にしかなりません。原因は WHERE 句に結合先（右側）のテーブルの条件を指定したこと にあります。 LEFT JOIN によって右側の列が NULL で埋められた行に対して o.status = 'cancelled' を評価すると、第 2 章で学んだとおり結果が「不明（UNKNOWN）」となり、WHERE 句によって除外されてしまうのです。右側テーブルの条件で絞り込みつつ左側テーブルの全行を残したい場合は、条件を ON 句 に記述します。

ON に書く

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

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 句で右側テーブルの列をフィルタしていないか確認するのが基本です。

同じ条件式でも、WHERE 句は結合完了後に適用されて NULL 行を除外し、ON 句は結合の判定時に適用されるため相手がいない顧客も NULL として残ります。

3 つ以上のテーブルの結合と多対多のリレーション

注文明細（order_items）テーブルには商品の ID だけが保持されています。「注文 → 明細 → 商品」と 2 回結合することで、「誰がどの商品を何個買ったか」を特定できます。

誰が何を買ったか

期待: 200 行

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

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 つのカテゴリに複数の商品が含まれる」という「多対多（N:N）」の関係になります。このような関係は、orders と customers のような「多対 1」の直接参照では表現できません。そのため、ID のペアだけを保持する product_categories(product_id, category_id) という橋渡し用のテーブルを間に挟みます。これを中間テーブル（関連テーブル）と呼びます。

カテゴリごとの商品数

期待: 6 行

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 がカテゴリ名をカンマでつなぐ

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 件の注文レコードをすべて更新しなければならず、1 件でも更新漏れがあるとデータに矛盾（不整合）が生じます。このように「同じ事実を複数の場所に重複して持たない」ようにテーブルを適切に分割する設計手法を正規化と呼びます。更新時の整合性を担保する代わりに、読み取り時に結合（JOIN）の手間をかけるというトレードオフになっています。

テーブル間の参照関係は、データベースの機能として制約を設けることができます。たとえば orders.customer_id は必ず実在する customers.id を指していなければならない、という制約を外部キー制約（FOREIGN KEY）と呼びます。存在しない架空の顧客 ID で注文を追加しようとすると、データベースが即座にエラーを返して不整合を防ぎます。

存在しない顧客の注文

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

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

JOIN はキーをもとに対応する行をつなぎ合わせます。INNER JOIN は両方に存在する行のみを残し、LEFT JOIN は左側の行をすべて維持します。右側テーブルの絞り込み条件は 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 になる。

次の章へ

ここまではデータの読み取り（SELECT）を中心に学んできました。次の章では、データの挿入（INSERT）、更新（UPDATE）、削除（DELETE）といったデータ変更の操作を学びます。特に「WHERE 句を書き忘れた UPDATE が全データを書き換えてしまう」という、実務で最も恐れられるインシデントを安全な環境で体験し、防止策を身につけましょう。