JOIN と GROUP BY を、3人のユーザーで理解する

巨大なテーブルで考えるから難しくなる。ユーザー3人と注文3件、たったこれだけの表で、JOIN も GROUP BY も全部説明がつく。

この2つの表だけで進める

喫茶店の注文システムを想像してほしい。登場するテーブルは2つだけ。

users（ユーザー）

id	name
1	あおい
2	ひかる
3	つばさ

orders（注文）

id	user_id	item
101	1	コーヒー
102	1	ケーキ
103	2	紅茶

注目してほしいのは次の2点。この2つが、これから起きること全部の原因になる。

• あおいさんは2件注文している（id 101 と 102）
• つばささんは1件も注文していない

orders.user_id が users.id を指している。この「指している列」があるから、2つの表をくっつけられる。

---

JOIN は「2つの表を1つの表にする」

JOIN の正体は、難しい操作ではない。2つの表を、共通の列で照合して1つの表に合体させるだけ。

「あおいさんの注文は何？」を知るには、users.id と orders.user_id を突き合わせる必要がある。それを SQL にやらせるのが JOIN。

FROM users
INNER JOIN orders ON orders.user_id = users.id

ON の後ろが「どう照合するか」の指定。ここでは「orders.user_id と users.id が同じ値なら、その2行はペア」と宣言している。

---

INNER JOIN — ペアがある行だけ残る

照合した結果、ペアが成立した組み合わせだけが並ぶ。これが INNER（内側）JOIN。

users.name	orders.id	orders.item
あおい	101	コーヒー
あおい	102	ケーキ
ひかる	103	紅茶

3人いたのに、結果は3行。しかも中身をよく見ると、2つの現象が起きている。

つばささんが消えた

注文が1件もないので、ペアを作れなかった。INNER JOIN は、相手がいない行を落とす。

あおいさんが2行になった

注文が2件あるので、組み合わせが2通りできた。JOIN は行を増やすことがある。

ここが一番の勘所。
INNER JOIN は「くっつける」だけでなく、相手がいない行を除外するフィルタとしても働く。「注文したことがあるユーザーだけ欲しい」ときに JOIN を使うのは、この性質を利用している。

---

LEFT JOIN — 左の表は全部残す

「つばささんも消したくない」場合に使うのが LEFT OUTER JOIN。左（元の表）の行は必ず残し、相手がいなければ NULL で埋める。

users.name	orders.id	orders.item
あおい	101	コーヒー
あおい	102	ケーキ
ひかる	103	紅茶
つばさ	NULL	NULL

これを使った定番テクニック

「一度も注文していないユーザーを探したい」とき。LEFT JOIN すると、そういう人は右側が必ず NULL になる。そこを狙い撃ちする。

# Rails
User.left_joins(:orders).where(orders: { id: nil })

-- SQL
LEFT OUTER JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL

「休眠ユーザーを探す」「レビューが付いていない商品を出す」など、実務で頻出する形。

---

数えたら合わない — 行が増える罠

ここで多くの人がハマる。「注文したことがあるユーザーは何人？」を数えてみる。

User.joins(:orders).count
# => 3     ← 2人のはずなのに！

答えが 3 になるのは、数えているのがユーザーではなく「JOIN 後の行」だから。さっきの表を見返すと、確かに3行ある。あおいさんが2回数えられている。

解決は distinct で重複を潰すこと。

User.joins(:orders).distinct.count
# => 2     ← あおい、ひかる

覚え方。
「1人が複数持つもの」（has_many）を JOIN すると行が増える。「1つしか持たないもの」（belongs_to）を JOIN しても増えない。増える側を JOIN したときだけ distinct を意識する。

---

GROUP BY — 同じ値の行をまとめる

「ユーザーごとに何件注文したか知りたい」。ここで登場するのが GROUP BY。指定した列の値が同じ行を、1グループにまとめる。

まとめる前（orders）

id	user_id
101	1
102	1
103	2

GROUP BY user_id のあと

グループ	中身
1	101, 102
2	103

3行が2グループになった。単位が「注文」から「ユーザー」に変わったのがポイント。

まとめた後は、集計関数でしか中身を取り出せない

グループの中には複数行が入っている。「どの行の値か」を1つに決める必要があるので、COUNT / MIN / MAX / SUM といった集計関数を使う。

Order.group(:user_id).count
# => { 1 => 2, 2 => 1 }

Order.group(:user_id).minimum(:created_at)
# => { 1 => 最初の注文日時, 2 => ... }

---

なぜ Hash が返ってくるのか

「count は数値を返すはずなのに、なぜ group を付けると Hash になるの？」——これは SQL が返す表の形を見ると腑に落ちる。

SELECT COUNT(*), user_id
FROM orders
GROUP BY user_id

SQL が返す表（2列）:

count	user_id
2	1
1	2

2列しかない表は、そのまま「キーと値のペアの並び」と読める。だから Rails はこれを Hash に詰め替えて返す。

{ 1 => 2,
  2 => 1 }
#  ↑グループ列  ↑集計結果

正確に言うと。
「group すると Hash になる」のではなく、「group すると SQL が2列返すようになり、Rails がそれを Hash として扱う」が正しい理解。group なしなら1列1行なので、単一の値が返る。

---

WHERE と HAVING の違い

どちらも絞り込みだが、効くタイミングが違う。

	何を絞る	いつ効く
WHERE	行	グループ化の前
HAVING	グループ	グループ化の後

「2件以上注文したユーザー」を出したいとする。件数はグループにまとめて数えないと分からないので、WHERE では書けない。

Order.group(:user_id).having('COUNT(*) >= 2').count
# => { 1 => 2 }     あおいさんだけ

処理される順番はこうなる。

• WHERE … まず行を絞る（例: 今月の注文だけ）
• GROUP BY … 残った行をユーザーごとにまとめる
• HAVING … グループの集計値で絞る（例: 2件以上）

使い分けの判断。
絞り込みたい条件が1行だけ見れば判定できるなら WHERE。複数行を集計しないと判定できないなら HAVING。

---

joins と preload は別物

最後にひとつ、混同しやすい話を。Rails には関連を扱うメソッドが複数あるが、目的が違う。

メソッド	SQL	目的
joins	INNER JOIN	絞り込み・集計
left_joins	LEFT OUTER JOIN	絞り込み（相手なしも残す）
preload	別クエリを発行	N+1 回避

意外に思うかもしれないが、joins しても関連データはメモリに載らない。

user = User.joins(:orders).first
user.orders   # ← ここで改めて SQL が飛ぶ（N+1 の正体）

user = User.preload(:orders).first
user.orders   # ← SQL は飛ばない（先読み済み）

「注文の中身を表示したい」なら preload、「注文がある人に絞りたい」なら joins。両方必要なら併用する。

---

まとめ

• INNER JOIN … ペアがある行だけ。相手がいない行は消える
• LEFT JOIN … 左は全部残る。相手がいなければ NULL
• LEFT JOIN + IS NULL … 「〜を持っていないもの」を探す定番
• 行が増える … has_many を JOIN したら distinct を検討
• GROUP BY … 単位を「行」から「グループ」へ変える
• Hash が返る … SQL が2列（グループ列＋集計値）を返すから
• WHERE と HAVING … 行を絞るか、グループを絞るか
• joins と preload … 絞り込み用か、N+1 回避用か

実際のテーブルは列も行も膨大だが、起きていることはこの3人3件の表と同じ。詰まったときは .to_sql で生成される SQL を眺めて、頭の中でこの小さな表に置き換えてみる。

puts User.joins(:orders).to_sql