JOIN の連鎖 ― 3つ以上の表をつなぐ

導入

「誰が・何を・いくつ買ったか」を一覧したい。ところがこの情報は、1つの表には入っていません。会員名は users、商品名は products、個数は order_items、その紐付けは orders。4つの表にまたがっています。正規化された DB では、こうして JOIN を数珠つなぎにして情報を組み立てます。

説明

JOIN は、ON を足しながら何個でもつなげられます。関係の鎖にそって「明細 → 注文 → 会員」「明細 → 商品」とたどります。

flowchart LR
    OI["order_items<br/>（誰が何をいくつ、の“いくつ”）"]
    O["orders<br/>（注文＝会員との橋渡し）"]
    U["users<br/>（会員名）"]
    P["products<br/>（商品名・価格）"]
    OI -->|"oi.order_id = o.id"| O
    O -->|"o.user_id = u.id"| U
    OI -->|"oi.product_id = p.id"| P

SELECT u.name AS 会員, p.name AS 商品, oi.quantity AS 個数
FROM order_items AS oi
JOIN orders   AS o ON oi.order_id   = o.id
JOIN users    AS u ON o.user_id     = u.id
JOIN products AS p ON oi.product_id = p.id;

ここまで来ると、実務でよくある「売上の集計」も書けます。金額は「個数 × 単価」なので、order_items.quantity と products.price を掛けて SUM します。「会員ごとの購入金額合計」はこうです。

SELECT u.name AS 会員, SUM(oi.quantity * p.price) AS 購入金額
FROM order_items AS oi
JOIN orders   AS o ON oi.order_id   = o.id
JOIN users    AS u ON o.user_id     = u.id
JOIN products AS p ON oi.product_id = p.id
GROUP BY u.name
ORDER BY 購入金額 DESC;

金額を各注文の行にコピーして持たず、必要なときに掛け算で導く――これが正規化された設計の実力です。単価が変わっても、products を1か所直すだけで、過去も含めた計算がやり直せます。

やってみよう

初期表示のクエリを実行し、「会員・商品・個数」が1つの表に組み上がることを確認しましょう。次に上の「購入金額」クエリを実行し、正規化されたバラバラの表から売上ランキングが作れることを体感してください。

演習

上と同じ4表の JOIN を使って、商品ごと（p.name）の販売個数合計を、多い順に取り出してください（SUM(oi.quantity) を使います）。