sqlite3 で動く。コピペして5分で同じ数字が出る。
JOIN は行を絞る操作じゃない。ON で一致した組み合わせを全部並べる操作。
だから 1対多 を JOIN すると、基準テーブルの行が「多」の件数だけ複製される。件数がズレる・SUM が水増しされる事故は、ほぼ全部ここが原因。
直し方は「JOIN する前に集計しておく」の一手で、自分の環境では実測 17 ms → 7 ms、2.5倍 速くなった。
想定読者
- SQL の
SELECTとJOINは書けるが、集計値がたまに合わなくて原因を説明できない - BI ツールやダッシュボードの数字が「なんか多い気がする」まま放置している
- ORM が吐いた SQL をレビューする側に回った
実行環境は macOS 15 / SQLite 3.43.2（macOS 標準）。挙動は PostgreSQL・MySQL でも同じで、差分は最後にまとめた。
この記事の流れ
- 再現 — users 3件が LEFT JOIN で5件になる
- 原因 — JOIN は組み合わせを全部返す
- 事故 — SUM が2倍に膨らむ
- 直し方1 — JOIN する前に集計する
- 直し方2 — スカラサブクエリで横に置く
- DISTINCT で誤魔化すと 1000円 が消える
- 検証 — 行数が変わっていないかをテストで固定する
- 速度 — 25万行に膨らんだ JOIN は 2.5倍 遅い
- PostgreSQL / MySQL / SQLite の差
- デメリットと留意点
- 今日 / 今週 / 今月のアクション
再現 — users 3件が LEFT JOIN で5件になる
ユーザー3人、注文4件、レビュー2件。それだけのテーブルで再現する。
-- 1対多のJOINで行が増えるのを最小再現する
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
amount INTEGER NOT NULL
);
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
score INTEGER NOT NULL
);
INSERT INTO users VALUES (1,'佐藤'),(2,'鈴木'),(3,'田中');
INSERT INTO orders VALUES (1,1,1000),(2,1,2000),(3,1,3000),(4,2,5000);
INSERT INTO reviews VALUES (1,1,5),(2,1,3);
保存して流す。
sqlite3 demo.db < join_demo.sql
users は3件。ここに orders を LEFT JOIN する。
-- users は3件のはず。JOINしても3件のはず……?
SELECT COUNT(*) AS joined_rows
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
返ってきた数字。
┌─────────────┐
│ joined_rows │
├─────────────┤
│ 5 │
└─────────────┘
3件 → 5件。行が増えた。中身を全部出すと理由が見える。
┌────┬──────┬──────────┬────────┐
│ id │ name │ order_id │ amount │
├────┼──────┼──────────┼────────┤
│ 1 │ 佐藤 │ 1 │ 1000 │
│ 1 │ 佐藤 │ 2 │ 2000 │
│ 1 │ 佐藤 │ 3 │ 3000 │
│ 2 │ 鈴木 │ 4 │ 5000 │
│ 3 │ 田中 │ │ │
└────┴──────┴──────────┴────────┘
佐藤が3行に増えている。注文を3件持っているから。田中は注文ゼロだが、LEFT JOIN なので NULL 埋めで1行残る。合計 3 + 1 + 1 = 5行。
原因 — JOIN は組み合わせを全部返す
自分がずっと誤解していたのは、JOIN を「別テーブルの列を横にくっつける操作」だと思っていたこと。実際は違う。
PostgreSQL の公式ドキュメントでは、JOIN された表は「左表の各行に対して、ON 条件を満たす右表の各行との組み合わせ」を返すと定義されている（Joined Tables / PostgreSQL 17）。つまり演算としては足し算ではなく掛け算に近い。
| 左の行 | 右で一致した行 | 結果の行数 |
|---|---|---|
| 佐藤 | 注文3件 | 3 |
| 鈴木 | 注文1件 | 1 |
| 田中 | 0件（LEFT JOIN で NULL 補完） | 1 |
「1対1 なら行は増えない」が正しく、「JOIN したら行は増えない」は間違い。1対1 に見えていたのは、たまたま相手が1件しか持っていなかっただけ。ここを取り違えると、後段の集計が静かに壊れる。
事故 — SUM が2倍に膨らむ
本番で刺さるのはここ。「ユーザーごとの売上と、レビュー件数を1本のクエリで出したい」という素直な要求に、素直に JOIN を2本書く。
-- 売上とレビュー件数を1本で出したい(壊れている)
SELECT u.name, SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN reviews r ON r.user_id = u.id
GROUP BY u.id, u.name;
結果。
┌──────┬──────────────┐
│ name │ total_amount │
├──────┼──────────────┤
│ 佐藤 │ 12000 │
│ 鈴木 │ 5000 │
│ 田中 │ │
└──────┴──────────────┘
佐藤の売上は 1000 + 2000 + 3000 = 6000 のはず。返ってきたのは 12000。ちょうど2倍。
理由は行数を数えると分かる。JOIN を2本にした時点で、中間結果は 8行 まで膨らんでいる。
佐藤 : 注文3件 × レビュー2件 = 6行
鈴木 : 注文1件 × レビュー0件 = 1行
田中 : 0件 × 0件 = 1行
佐藤の注文が2回ずつ数えられている。SUM は目の前にある行を素直に足すだけで、その行が複製されたものかどうかを知らない。集計関数は入力行の重複を検知しないと公式にも明記されている（Aggregate Functions / PostgreSQL 17）。
自分はこれを、金額ではなく件数のダッシュボードでやらかした。「今月の申込数が先月比で妙に伸びた」と思ったら、JOIN を1本足したせいで一部ユーザーの行が複製されていただけ。数字が減る方向のバグは気づかれるが、増える方向のバグは喜ばれてしまうので発見が遅れる。ここが本当に怖い。
直し方1 — JOIN する前に集計する
一番素直で、一番速い。「多」側を先に1行にまとめてから JOIN すれば、1対1 になるので行は増えない。
-- 先に集計 → 1対1 になってから JOIN する
WITH order_sum AS (
SELECT user_id, SUM(amount) AS total_amount
FROM orders GROUP BY user_id
),
review_cnt AS (
SELECT user_id, COUNT(*) AS review_count
FROM reviews GROUP BY user_id
)
SELECT u.name,
COALESCE(os.total_amount, 0) AS total_amount,
COALESCE(rc.review_count, 0) AS review_count
FROM users u
LEFT JOIN order_sum os ON os.user_id = u.id
LEFT JOIN review_cnt rc ON rc.user_id = u.id
ORDER BY u.id;
┌──────┬──────────────┬──────────────┐
│ name │ total_amount │ review_count │
├──────┼──────────────┼──────────────┤
│ 佐藤 │ 6000 │ 2 │
│ 鈴木 │ 5000 │ 0 │
│ 田中 │ 0 │ 0 │
└──────┴──────────────┴──────────────┘
6000 に戻った。田中が 0 で残っているのも意図通り。COALESCE を挟んでいるのは、LEFT JOIN の未一致が NULL で返るから。NULL のまま計算に流すと別の事故になるので、ここで潰しておく（NULL の三値論理でハマった話）。
CTE（WITH 句）の構文は WITH Queries / PostgreSQL 17 が一次情報。SQLite でも MySQL 8.0 以降でも同じ書き方で動く。
直し方2 — スカラサブクエリで横に置く
集計対象が1〜2個で、しかも「絞り込み条件が列ごとに違う」場合はこっち。
-- 列ごとに独立した集計を、そのまま列として置く
SELECT u.name,
(SELECT COALESCE(SUM(o.amount),0) FROM orders o WHERE o.user_id = u.id) AS total_amount,
(SELECT COUNT(*) FROM reviews r WHERE r.user_id = u.id) AS review_count
FROM users u
ORDER BY u.id;
結果は直し方1 と同じ 6000 / 5000 / 0。行を一切増やさずに列だけ足せるので、読み手にとっては意図が一番はっきりする。
正直、可読性ではこれが一番好み。ただし相関サブクエリは行ごとに評価されるので、基準テーブルが数十万行あるとつらい。PostgreSQL なら同じ発想を LATERAL で書ける（LATERAL Subqueries）。SQLite には LATERAL がないので、そこは移植性のトレードオフ。
DISTINCT で誤魔化すと 1000円 が消える
ここが今日一番伝えたい落とし穴。行が増えた時、SUM(DISTINCT ...) を付けると数字が「それっぽく」戻る。戻るだけで、正しくはならない。
同じ金額の注文が2件あるケースで試す。
-- 1000円の注文が2件、3000円が1件。正しい合計は 5000
INSERT INTO orders VALUES (1,1,1000),(2,1,1000),(3,1,3000);
SELECT SUM(DISTINCT o.amount) AS distinct合計, COUNT(DISTINCT o.id) AS 注文件数
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN reviews r ON r.user_id = u.id
GROUP BY u.id;
┌────────────┬──────┐
│ distinct合計 │ 注文件数 │
├────────────┼──────┤
│ 4000 │ 3 │
└────────────┴──────┘
正しい合計は 5000、返ってきたのは 4000。1000円 が消えている。20% の欠損。
DISTINCT が消したのは「JOIN で複製された行」ではなく「同じ値の行」。金額のような、本来かぶって当然の列に使うと、実データを巻き添えにする。一方で COUNT(DISTINCT o.id) は 3 と正しい。主キーのように一意性が保証された列でだけ DISTINCT は安全に効く。この違いは COUNT(*) と COUNT(col) で件数が合わなかった話 と同じ根っこ。
結局、DISTINCT は行が増えた原因を消す道具じゃなく、増えた事実を見えなくする道具。エラーも警告も出ないので、レビューでも見逃されやすい。
検証 — 行数が変わっていないかをテストで固定する
目視では追えない。「基準テーブルの行数が JOIN 後も変わっていないこと」を機械で確認する。
# JOINの前後で「基準テーブルの行数」が変わっていないかを検査する
import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount INTEGER);
INSERT INTO users VALUES (1,'佐藤'),(2,'鈴木'),(3,'田中');
INSERT INTO orders VALUES (1,1,1000),(2,1,2000),(3,1,3000),(4,2,5000);
""")
base = conn.execute("SELECT COUNT(*) FROM users").fetchone()[0]
joined = conn.execute("""
SELECT COUNT(*) FROM users u LEFT JOIN orders o ON o.user_id = u.id
""").fetchone()[0]
print(f"users : {base} 行")
print(f"LEFT JOIN後 : {joined} 行")
assert joined == base, f"JOINで行が増えた: {base} 行 -> {joined} 行"
print("OK: 行数は変わっていない")
実行するとちゃんと落ちる。
users : 3 行
LEFT JOIN後 : 5 行
Traceback (most recent call last):
File "test_join_rows.py", line 20, in <module>
assert joined == base, f"JOINで行が増えた: {base} 行 -> {joined} 行"
^^^^^^^^^^^^^^
AssertionError: JOINで行が増えた: 3 行 -> 5 行
集計クエリを直したら、この assert が通る形になっているかを CI に置く。自分は BI 用の SQL を触るたびにこれを走らせるようにした。手元では real 0.02 秒で終わる。「増える方向のバグ」を人間が気づけない以上、機械に見張らせるしかない。
速度 — 25万行に膨らんだ JOIN は 2.5倍 遅い
行が増えるのは正しさの問題だけじゃなく、単純に遅い。ユーザー 5,000 人、注文 50,000 件、レビュー 25,000 件で測った。
# 行が膨らむJOINと、先に集計するJOINの実行時間を比べる
BAD = """
SELECT u.id, SUM(o.amount) AS total, COUNT(r.id) AS reviews
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN reviews r ON r.user_id = u.id
GROUP BY u.id
"""
GOOD = """
WITH order_sum AS (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id),
review_cnt AS (SELECT user_id, COUNT(*) AS reviews FROM reviews GROUP BY user_id)
SELECT u.id, COALESCE(os.total,0), COALESCE(rc.reviews,0)
FROM users u
LEFT JOIN order_sum os ON os.user_id = u.id
LEFT JOIN review_cnt rc ON rc.user_id = u.id
"""
user_id にインデックスを張った状態で、各5回実行の中央値。
users=5000 orders=50000 reviews=25000
JOIN後の中間行数 : 250000 行
膨らんだままGROUP BY : 17 ms -> user1 total=18500
先に集計してからJOIN : 7 ms -> user1 total=3700
速度差 : 2.5倍
元データは 80,000 行なのに、JOIN 後の中間行は 250,000 行。3倍以上に膨らんでいる。しかも total は 18500 と 3700 で、ちょうど 5倍（レビュー件数ぶん）ズレたまま。速い方が正しいという、珍しく気持ちのいい結論。
インデックスは user_id に張ってある（CREATE INDEX / PostgreSQL 17）。インデックスは JOIN の探索を速くするだけで、行が増えること自体は止められない。ここを混同すると「インデックス張ったのに遅い」で詰まる。
PostgreSQL / MySQL / SQLite の差
行が増える挙動そのものは3つとも同じ。標準 SQL の SELECT の定義どおり（SELECT / PostgreSQL 17、SELECT / SQLite、JOIN Clause / MySQL 8.4）。差が出るのは周辺機能。
CTE (WITH) |
LATERAL |
GROUP BY に無い列 | |
|---|---|---|---|
| PostgreSQL | 使える | 使える | エラー |
| MySQL 8.0+ | 使える | 使える |
ONLY_FULL_GROUP_BY 次第 |
| SQLite | 使える | 無し | 通る（任意の1行が返る） |
SQLite が一番ゆるい。GROUP BY に含めていない列を書いても通ってしまうので、複製された行のうちどれが返るか分からない。検証を SQLite でやるなら、この差だけは頭に入れておく。
デメリットと留意点
先に集計する書き方は万能じゃない。自分が実際に困った点を並べる。
- クエリが長くなる。 CTE を2本足すと縦に伸びる。3テーブル以上を集計する場合、素直な JOIN より読むのがつらくなる場面はある
-
CTE の最適化はエンジン依存。 PostgreSQL は 12 で
WITHのインライン展開が入ったが、それ以前は最適化の壁として扱われた。古いバージョンを触る時は実行計画を見た方がいい - スカラサブクエリは行数に弱い。 基準テーブルが数十万行だと、行ごとの評価コストが効いてくる。同じ条件で測ったら 5 ms で、CTE の 7 ms よりむしろ速かった。ただし users 5,000 行・インデックスありという条件での話で、そのまま拡大解釈はできない
- 今回の実測は SQLite の in-memory。 ディスク I/O もネットワークも無い条件で、実務のデータベースとは前提が違う。数字の絶対値ではなく「膨らんだ方が遅い」という向きだけ持ち帰ってほしい
「常に CTE で書け」ではない。1対1 が保証されているマスタの JOIN なら、素直に JOIN するのが読みやすい。判断の分かれ目は、相手が複数行を持ちうるかどうか。それだけ。
ビフォーアフター
| Before | After | |
|---|---|---|
| users への LEFT JOIN 後の行数 | 3件 → 5件 | 3件 → 3件 |
| 佐藤の売上 | 12000（2倍） | 6000 |
| JOIN 後の中間行数 | 250000 行 | 5000 行 |
| クエリ実行時間 | 17 ms | 7 ms |
| ズレの検知 | 目視 | assert で自動 |
今日 / 今週 / 今月のアクション
今日（15分）
手元で再現する。macOS なら sqlite3 が最初から入っているので、この記事の join_demo.sql をコピペして sqlite3 demo.db < join_demo.sql を流す。3件 → 5件 を自分の目で見るのが一番速い。
今週（1時間）
自分が書いた集計クエリを1本選んで、SELECT COUNT(*) を JOIN の前後で比べる。増えていたら WITH で先に集計する形に書き換える。書き換え前後で合計値が変わったなら、それは今まで出していた数字が間違っていた証拠。
# JOIN前後の行数を比べる。数字が違ったら集計が壊れている
sqlite3 demo.db "SELECT COUNT(*) FROM users;"
sqlite3 demo.db "SELECT COUNT(*) FROM users u LEFT JOIN orders o ON o.user_id = u.id;"
今月（半日）
test_join_rows.py の形をプロジェクトに置いて CI に載せる。基準テーブルの行数を assert で固定しておけば、次に誰かが JOIN を1本足した時点で落ちる。レビューで気づく前に CI が気づく状態を作るのが目的。
参考リンク
- Joined Tables / PostgreSQL 17 — JOIN が返す行の定義
- LATERAL Subqueries / PostgreSQL 17 — 相関サブクエリを FROM 句で書く
- WITH Queries (CTE) / PostgreSQL 17 — 先に集計する時の構文
- Aggregate Functions / PostgreSQL 17 — 集計関数が入力行をどう扱うか
- CREATE INDEX / PostgreSQL 17 — JOIN 列のインデックス
- SELECT / PostgreSQL 17
- Tutorial: Joins Between Tables / PostgreSQL 17
- SELECT / SQLite
- JOIN Clause / MySQL 8.4
!=で弾いたはずの行が消えた — SQL の NULL と三値論理- COUNT(*) と COUNT(col) で件数が合わなかった日