Day14 後半
「JOIN した“あと”に、いつもの技を全部乗せする」
前半で、INNER JOIN の一番大事なところ――
「2つのテーブルを、キーで結びつけて1枚にする」
という感覚まではつかめました。
後半では、その「JOIN してできた1枚」に対して、
WHERE で絞る
ORDER BY で並べる
GROUP BY で集計する
といった、これまで学んだ技を全部乗せしていきます。
ここがつながると、一気に「実務で使う SQL」の顔になります。
INNER JOIN と WHERE を組み合わせる
「結びつけたうえで、“どの行を見るか”を決める」
まずは、前半のテーブルをもう一度使います。
users
id | name
---+-----------
1 | 山田太郎
2 | 佐藤花子
3 | 鈴木一郎
orders
id | user_id | amount
---+---------+-------
1 | 1 | 1200
2 | 1 | 3000
3 | 2 | 500
4 | 4 | 8000
前半では、こう書きました。
SELECT
u.id,
u.name,
o.amount
FROM users AS u
INNER JOIN orders AS o
ON u.id = o.user_id;
結果はこうでした。
id | name | amount
---+----------+-------
1 | 山田太郎 | 1200
1 | 山田太郎 | 3000
2 | 佐藤花子 | 500
ここに、「金額が 1000 円以上の注文だけを見たい」という条件を足してみます。
SELECT
u.id,
u.name,
o.amount
FROM users AS u
INNER JOIN orders AS o
ON u.id = o.user_id
WHERE o.amount >= 1000;
流れを言葉で追うと、こうなります。
まず、INNER JOIN で「ユーザーと注文を結びつけた1枚」を作る。
次に、その結果に対して WHERE で「amount >= 1000」の行だけを残す。
結果はこうなります。
id | name | amount
---+----------+-------
1 | 山田太郎 | 1200
1 | 山田太郎 | 3000
ここでのポイントは、
JOIN したあとの世界では、u.〜 も o.〜 も同じ1行の中の列として扱える、ということです。
だから WHERE でも、u.name でも o.amount でも自由に条件に使えます。
INNER JOIN と ORDER BY を組み合わせる
「JOIN した結果を、“読みやすい順番”に並べる」
次に、「金額の大きい順に並べたい」という要望を足してみます。
SELECT
u.id,
u.name,
o.amount
FROM users AS u
INNER JOIN orders AS o
ON u.id = o.user_id
ORDER BY o.amount DESC;
JOIN で「誰がいくら買ったか」という1枚を作り、
ORDER BY で「金額の大きい順」に並べる、という流れです。
WHERE と ORDER BY を両方使うなら、こうなります。
SELECT
u.id,
u.name,
o.amount
FROM users AS u
INNER JOIN orders AS o
ON u.id = o.user_id
WHERE o.amount >= 1000
ORDER BY o.amount DESC;
ここまで来ると、もう「1テーブルのときと同じ感覚」で書けているはずです。
違うのは、FROM に2テーブル書いて、ON で結びつけているだけです。
3テーブルを連鎖的に JOIN する
「ユーザー × 注文 × 商品名」を1枚にする
現実のアプリでは、2テーブルで終わらないことが多いです。
もう1枚、products テーブルを追加してみます。
products
id | name
---+-----------
1 | りんご
2 | みかん
3 | バナナ
そして、orders に product_id を足した形を考えます。
orders
id | user_id | product_id | amount
---+---------+------------+-------
1 | 1 | 1 | 1200
2 | 1 | 2 | 3000
3 | 2 | 3 | 500
ここでの問いはこうです。
「各注文について、“誰が・何を・いくらで買ったか”を一覧で見たい」
人間の頭の中では、
orders.user_id → users.id からユーザー名を取る
orders.product_id → products.id から商品名を取る
という二段階の紐づけをしています。
SQL では、INNER JOIN を2回つなげます。
SELECT
u.name AS user_name,
p.name AS product_name,
o.amount
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id
INNER JOIN products AS p ON o.product_id = p.id;
結果のイメージはこうです。
user_name | product_name | amount
----------+--------------+-------
山田太郎 | りんご | 1200
山田太郎 | みかん | 3000
佐藤花子 | バナナ | 500
ここでの大事なポイントは二つです。
一つ目は、「JOIN は何回でも重ねられる」ということ。
二つ目は、「ON で結びつけるペアを、毎回きちんと意識する」こと。
o.user_id = u.ido.product_id = p.id
この対応関係が崩れると、一気におかしな結果になります。
JOIN した結果に対して GROUP BY する
「ユーザー名付きで、合計購入金額を出す」
JOIN の真価が出てくるのは、「JOIN した結果に対して集計する」ときです。
さきほどの3テーブルを使って、次の問いを考えます。
「ユーザーごとに、合計購入金額を出したい。ただし、ユーザー名も一緒に表示したい」
これは、
orders と users を JOIN して、「誰がいくら買ったか」の1枚を作る
その1枚を user_id（あるいは user_name）ごとに GROUP BY して SUM(amount) する
という流れです。
SQL はこうなります。
SELECT
u.id,
u.name,
SUM(o.amount) AS total_amount
FROM orders AS o
INNER JOIN users AS u
ON o.user_id = u.id
GROUP BY
u.id,
u.name;
結果のイメージはこうです。
id | name | total_amount
---+----------+-------------
1 | 山田太郎 | 4200
2 | 佐藤花子 | 500
ここでのポイントは、
GROUP BY に「グループのキーになる列（u.id, u.name）」を書き、
集計したい列（o.amount）には SUM をかける、というルールが、
JOIN していても変わらないことです。
JOIN で「意味のある1枚」を作る
GROUP BY で「その1枚をグループごとに数字にする」
この二段構えが、実務のレポート系 SQL のど真ん中にあります。
JOIN × GROUP BY × HAVING × ORDER BY
「本当に見たい“上位のユーザー”だけを、怪しい順に見る」
ここまで来たら、Day13 までの武器を全部乗せてみましょう。
問い：
「合計購入金額が 5000 円以上のユーザーだけを対象にして、
合計金額の大きい順に並べたい。ユーザー名も一緒に見たい」
分解すると、
orders と users を JOIN して、「誰がいくら買ったか」の1枚を作る
user ごとに GROUP BY して SUM(amount) を計算する
HAVING で SUM(amount) >= 5000 のグループだけを残す
ORDER BY で total_amount の大きい順に並べる
SQL はこうなります。
SELECT
u.id,
u.name,
SUM(o.amount) AS total_amount
FROM orders AS o
INNER JOIN users AS u
ON o.user_id = u.id
GROUP BY
u.id,
u.name
HAVING
SUM(o.amount) >= 5000
ORDER BY
total_amount DESC;
この1本の SQL の中に、
INNER JOIN
GROUP BY
HAVING
ORDER BY
が全部入っていますが、
一つ一つの役割は、ここまでやってきた通りです。
JOIN で「情報をくっつける」
GROUP BY で「グループごとに数字にする」
HAVING で「条件を満たすグループだけに絞る」
ORDER BY で「見る順番を決める」
この流れを日本語で説明できれば、もうかなり“実務の SQL”に近いところにいます。
セキュリティの視点から見る JOIN の怖さと強さ
「必要以上にくっつけない」「くっつけた結果をどう守るか」
JOIN は便利ですが、セキュリティ的には「情報を増やす」操作でもあります。
たとえば、
users にはメールアドレスや本名が入っているaccess_logs にはアクセスパスやIPアドレスが入っている
この二つを JOIN すると、
「どのユーザー（メールアドレス、本名）が、どのIPから、どのページにアクセスしたか」
という、かなりセンシティブな1枚ができます。
ここで意識しておきたいのは、二つです。
一つ目は、「本当に必要なときだけ JOIN する」ということ。
何でもかんでも JOIN して“全部入りの1枚”を作るのは、情報漏洩リスクを上げます。
二つ目は、「JOIN した結果に対するアクセス権限」をちゃんと考えること。
アプリケーション側で、「この情報は誰が見ていいのか」をきちんと制御する必要があります。
一方で、JOIN は「不正なデータをあぶり出す」ためにも使えます。
orders に存在する user_id のうち、users に存在しないものを探す
→ LEFT JOIN などの出番ですが、「正しく紐づいていないデータ」を検知できる
Day14 の段階では、
INNER JOIN は「正しく紐づいているデータだけを前提にした、安全側の結合」
JOIN した結果は、それ自体がセンシティブな情報になり得る
という二つの感覚だけ、頭の片隅に置いておいてくれれば十分です。
小さな練習で締める
日本語の問いを、JOIN 付きの SQL にしてみる
頭の中で、次の問いを SQL にしてみてください。
全ての注文について、「ユーザー名・商品名・金額」を一覧で見たい。
ユーザーごとに合計購入金額を出し、合計が 10000 円以上のユーザーだけを、合計の大きい順に見たい。
どちらも、
FROM orders o
INNER JOIN users u ON o.user_id = u.id
[INNER JOIN products p ON o.product_id = p.id]
という骨格に、GROUP BY / HAVING / ORDER BY を足していく形で書けるはずです。
Day14 後半のまとめ
INNER JOIN で「意味のある1枚」を作ったあとは、WHERE / ORDER BY / GROUP BY / HAVING を、1テーブルのときと同じ感覚で使える。
JOIN は何回でも重ねられ、users × orders × products のように「誰が・何を・いくらで」を1本のクエリで出せる。
JOIN した結果に対して GROUP BY すると、「ユーザー名付きの合計購入金額」のような“人間が読みやすい集計”が作れる。
JOIN × GROUP BY × HAVING × ORDER BY を組み合わせると、「本当に見たい上位のユーザー／怪しいユーザー」だけを抽出して、優先順位付きで眺められる。
JOIN は情報を増やす操作でもあるので、「本当に必要なときだけ」「誰が見ていいか」を意識することが、セキュリティ的にはとても重要。
ここまで来たあなたは、
「1テーブルの世界」から完全に抜け出して、
「複数テーブルをつなぎ、集計し、条件をかけて読む」レベルに足を踏み入れています。
この先は、LEFT JOIN やサブクエリなどを覚えながら、より複雑なデータ構造にも対応できるようになっていきます。