Day15 後半
「NULL を味方につけて、“何もしていない人”をちゃんと拾う」
前半で、LEFT JOIN が
「左側のテーブルを主役にして、右側は“あればくっつく・なければ NULL”」
という結合だ、というところまでは来ました。
後半では、この NULL をどう扱うか が主役です。
ここを押さえると、
「一度も注文していないユーザーだけ」
「一度もログインしていないユーザーだけ」
といった、“何もしていない人”を正確に抽出できるようになります。
LEFT JOIN と WHERE の落とし穴
「WHERE で右側の列を絞ると、INNER JOIN っぽくなってしまう」
まず、前半のテーブルをもう一度使います。
users
id | name
---+-----------
1 | 山田太郎
2 | 佐藤花子
3 | 鈴木一郎
orders
id | user_id | amount
---+---------+-------
1 | 1 | 1200
2 | 1 | 3000
3 | 2 | 500
前半でやった LEFT JOIN はこうでした。
SELECT
u.id,
u.name,
o.amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id;
結果はこうです。
id | name | amount
---+----------+-------
1 | 山田太郎 | 1200
1 | 山田太郎 | 3000
2 | 佐藤花子 | 500
3 | 鈴木一郎 | NULL
ここで、「注文があるユーザーだけ見たい」と思って、ついこう書きたくなります。
SELECT
u.id,
u.name,
o.amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
WHERE o.amount IS NOT NULL;
結果はこうなります。
id | name | amount
---+----------+-------
1 | 山田太郎 | 1200
1 | 山田太郎 | 3000
2 | 佐藤花子 | 500
鈴木一郎（amount が NULL の行）は消えました。
つまり、LEFT JOIN したあとに WHERE で「右側の列が NULL じゃない」と絞ると、実質 INNER JOIN と同じ になります。
これは「間違い」ではありませんが、
「左側を全部残したいから LEFT JOIN にしたのに、WHERE で自分で消してしまう」
ということが起きがちなので、意識しておく必要があります。
「注文がないユーザーだけ」を取りたいときの定番パターン
「LEFT JOIN ＋ WHERE 右側 IS NULL」
では逆に、「一度も注文していないユーザーだけ」を取りたいときはどうするか。
これは、LEFT JOIN の 一番おいしい使い方 です。
やりたいことを言葉にすると、
全ユーザーを左側に並べる
注文があればくっつく、なければ NULL
その中から、「注文が NULL の人だけ」を残す
です。
SQL はこうなります。
SELECT
u.id,
u.name
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
WHERE o.id IS NULL;
ここでのポイントは、o.amount IS NULL ではなく、
「右側テーブルの主キー（ここでは o.id）が NULL かどうか」を見る ことです。
結果はこうなります。
id | name
---+-----------
3 | 鈴木一郎
このパターンは、現場でめちゃくちゃよく使います。
「一度も〜していない人」を探す
→ LEFT JOIN して、右側の主キーが NULL の行だけを WHERE で残す
という型を、頭に刻んでおいてください。
LEFT JOIN した結果に GROUP BY をかける
「注文がないユーザーも含めて、合計金額を出す」
次に、LEFT JOIN と GROUP BY を組み合わせてみます。
問い：
「全ユーザーについて、合計購入金額を出したい。
まだ一度も注文していないユーザーも、0 円として出したい」
やりたいことは、
users を左、orders を右にして LEFT JOIN
ユーザーごとに GROUP BY
SUM(o.amount) で合計金額を出す
です。
SELECT
u.id,
u.name,
SUM(COALESCE(o.amount, 0)) AS total_amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
GROUP BY
u.id,
u.name;
ここで新しく出てきた COALESCE(o.amount, 0) は、
「o.amount が NULL なら 0 とみなす」
という関数です。
LEFT JOIN の結果では、注文がないユーザーの o.amount は NULL になります。
そのまま SUM すると、NULL が混ざるので扱いがややこしくなります。
そこで、
NULL → 0 に変換してから SUM する
→ 「注文がないユーザーの合計金額は 0」として扱える
というわけです。
結果のイメージはこうです。
id | name | total_amount
---+----------+-------------
1 | 山田太郎 | 4200
2 | 佐藤花子 | 500
3 | 鈴木一郎 | 0
ここでのポイントは二つ。
一つ目は、「LEFT JOIN してから GROUP BY すると、“何もしていない人”も含めた集計ができる」こと。
二つ目は、「NULL を 0 に変換する COALESCE などを組み合わせると、きれいな数字になる」こと。
LEFT JOIN × GROUP BY × HAVING
「“ほとんど何もしていないユーザー”をあぶり出す」
もう少し踏み込んで、HAVING も絡めてみます。
問い：
「全ユーザーの合計購入金額を出しつつ、
合計が 1000 円以下の“ほとんど何も買っていないユーザー”だけを見たい」
やりたいことは、
LEFT JOIN で全ユーザーと注文を結びつける
ユーザーごとに GROUP BY して SUM(amount) を出す
HAVING で「合計が 1000 以下」のグループだけを残す
です。
SELECT
u.id,
u.name,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
GROUP BY
u.id,
u.name
HAVING
total_amount <= 1000;
（SQLite では HAVING で別名を使えることが多いですが、不安なら HAVING COALESCE(SUM(o.amount), 0) <= 1000 と書いてもOKです。）
結果のイメージはこうです。
id | name | total_amount
---+----------+-------------
2 | 佐藤花子 | 500
3 | 鈴木一郎 | 0
ここでのポイントは、
INNER JOIN だと「そもそも注文がないユーザー」は集計対象に入らない
LEFT JOIN だと「注文がないユーザーも 0 として集計対象に入る」
そのうえで HAVING を使うことで、
「ほとんど何も買っていない人（少額 or 0）」
を一気にあぶり出せる、ということです。
セキュリティの視点から見る LEFT JOIN の“攻め”の使い方
「アカウントだけ作って放置」「ログインしていない管理者」を見つける
セキュリティ的に、LEFT JOIN が光る場面を二つだけ挙げます。
一つ目は、「アカウントだけ作って放置されているユーザー」です。
users に全ユーザーlogin_logs にログイン履歴
があるとして、
「一度もログインしていないユーザー」を探したいときは、こうです。
SELECT
u.id,
u.name
FROM users AS u
LEFT JOIN login_logs AS l
ON u.id = l.user_id
WHERE l.id IS NULL;
これで、「アカウントはあるのに、一度もログインしていない人」が分かります。
二つ目は、「ログインしていない管理者」です。
users に role 列（’admin’ / ‘user’ など）があるとして、
「管理者アカウントのうち、一度もログインしていないもの」を探したいときは、こうです。
SELECT
u.id,
u.name
FROM users AS u
LEFT JOIN login_logs AS l
ON u.id = l.user_id
WHERE
u.role = 'admin'
AND l.id IS NULL;
ここでは、
WHERE u.role = ‘admin’ で「管理者だけ」に絞り、
LEFT JOIN の結果で l.id が NULL の行だけを残すことで、
「ログインしていない管理者アカウント」を特定しています。
こうしたクエリは、
放置された管理者アカウントが乗っ取られるリスク
幽霊アカウントが残り続けるリスク
を洗い出すうえで、とても実用的です。
小さな練習で締める
日本語の「〜したことがない人」を LEFT JOIN に翻訳してみる
頭の中で、次の問いを SQL にしてみてください。
一度も注文していないユーザーだけを一覧で見たい。
全ユーザーの合計購入金額を出し、1000 円以下のユーザーだけを抽出したい（0 円も含む）。
管理者アカウントのうち、一度もログインしていないものだけを知りたい。
どれも、
FROM 左テーブル AS l
LEFT JOIN 右テーブル AS r ON …
WHERE r.主キー IS NULL （「一度も〜していない人」）
あるいは、
LEFT JOIN → GROUP BY → HAVING
という型に当てはめれば書けるはずです。
Day15 後半のまとめ
LEFT JOIN の結果に対して WHERE で「右側が NULL かどうか」を見ることで、「一度も〜していない人」を正確に抽出できる。
「右側 IS NOT NULL」で絞ると、実質 INNER JOIN と同じ振る舞いになることを意識しておく。
LEFT JOIN した結果に GROUP BY / HAVING を組み合わせると、「何もしていない人も含めた集計」や「ほとんど何もしていない人の抽出」ができる。
セキュリティの観点では、「ログインしていないユーザー」「放置された管理者アカウント」など、“静かすぎて危ない存在”を見つけるための強力な武器になる。
ここまで来たあなたは、
INNER JOIN で「正しく紐づいているデータ」を扱い、
LEFT JOIN で「何もしていない人も含めて全体を見る」ことができる状態です。
この先は、これらを組み合わせながら、より複雑なデータ構造やレポートにも対応できるようになっていきます。