SQL の JOIN・GROUP BY・サブクエリ：集計がずれる罠と実測
SQL講座3。SQLite 3.51 で INNER/LEFT JOIN、合計を3倍にする行の爆発、HAVING と WHERE、COUNT(*) と COUNT(列)、サブクエリ対 JOIN の速度を実測。
シリーズ：SQL 講座 3/6（中級）。 1つのテーブルへのクエリは書けるようになり（第1・2回）、次は「顧客別の売上」「一度も注文していない顧客」のように複数テーブルにまたがる答えが必要な人向けです。読み終えると、適切な JOIN を選び、グループ化と絞り込みを正しく行い、レポートの数字を静かに狂わせる3つのミスを見抜けるようになります。
この回のクエリはエラーになりません。もっともらしいけれど間違った数字を返すだけです。ちょうど3倍に膨らんだ顧客別合計、注文件数と同じになる「都市別の顧客数」、見たかった顧客をこっそり落とす LEFT JOIN。月末に営業部から「ダッシュボードの数字が Excel と合わない」と言われたとき、原因はたいていこのどれかです。手で数えられるほど小さなデータで1つずつ再現しました。すべて Apple Silicon Mac（M1 Pro）の sqlite3 3.51.0 で実行した実際の出力です。
題材データ：顧客・注文・支払い
テーブルは3つです。顧客1人に注文が0件以上、支払いが0件以上あります（1対多の関係）。わざと扱いにくい行を2つ入れました。Dan には注文がなく、注文107には顧客がいません（customer_id が NULL のゲスト購入）。
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (
id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
amount INTEGER NOT NULL, ordered_on TEXT NOT NULL
);
CREATE TABLE payments (
id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
paid INTEGER NOT NULL
);
INSERT INTO customers (id, name, city) VALUES
(1, 'Aoi', 'Osaka'), (2, 'Ben', 'Seoul'), (3, 'Chloe', 'Osaka'), (4, 'Dan', NULL);
INSERT INTO orders (id, customer_id, amount, ordered_on) VALUES
(101, 1, 3000, '2026-10-01'), (102, 1, 1200, '2026-10-15'),
(103, 2, 800, '2026-10-20'), (104, 3, 5000, '2026-11-02'),
(105, 3, 700, '2026-11-03'), (106, 3, 2500, '2026-11-05'),
(107, NULL, 999, '2026-11-06');
INSERT INTO payments (customer_id, paid) VALUES (1, 2000), (1, 1000), (1, 1200), (3, 8200);
sqlite3 shop.db < shop.sql で作り、シェルで .headers on、.mode table、.nullvalue NULL をオンにします。
INNER JOIN と LEFT JOIN
JOIN は ON の条件で2つのテーブルの行を組み合わせます。JOIN（INNER JOIN の略）は一致した組だけを残し、LEFT JOIN は左テーブルの全行を残して、相手がいなければ右側を NULL で埋めます。
SELECT c.name, o.id AS order_id, o.amount
FROM customers AS c JOIN orders AS o ON o.customer_id = c.id;
+-------+----------+--------+
| name | order_id | amount |
+-------+----------+--------+
| Aoi | 101 | 3000 |
| Aoi | 102 | 1200 |
| Ben | 103 | 800 |
| Chloe | 104 | 5000 |
| Chloe | 105 | 700 |
| Chloe | 106 | 2500 |
+-------+----------+--------+
LEFT JOIN にすると7行でした。同じ6行に Dan | NULL | NULL が加わります。注文107は組む顧客がいないので、どちらにも出てきません。SQLite 3.39.0 以降は RIGHT JOIN と FULL JOIN も使え、FULL JOIN は 107 | 999 | NULL を含む8行を返しました。両側の「相手のいないデータ」をまとめて探すのに便利です。
LEFT JOIN の結果には注目すべき点があります。Aoi の行が 102, 101、Chloe の行が 105, 106, 104 の順で返ってきました。EXPLAIN QUERY PLAN が理由を教えてくれます。
QUERY PLAN
|--SCAN c
|--BLOOM FILTER ON o (customer_id=?)
`--SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
SQLite がこのクエリのためだけに orders.customer_id に一時インデックスを作り、そのインデックス順に注文を読んだのです。INNER JOIN のほうはたまたま挿入順に読んだだけです。どちらも約束ではありません。ORDER BY がなければ順序は実行計画しだいで、データが増えれば計画も変わります。
GROUP BY・HAVING と3種類の COUNT
GROUP BY は同じ値を持つ行をグループごとに1行にまとめ、集約関数（count、sum、avg、min、max）がグループごとに要約します。注文のない顧客も含めた顧客別の注文：
SELECT c.name, count(*) AS rows_, count(o.id) AS orders_, coalesce(sum(o.amount), 0) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id ORDER BY total DESC;
+-------+-------+---------+-------+
| name | rows_ | orders_ | total |
+-------+-------+---------+-------+
| Chloe | 3 | 3 | 8200 |
| Aoi | 2 | 2 | 4200 |
| Ben | 1 | 1 | 800 |
| Dan | 1 | 0 | 0 |
+-------+-------+---------+-------+
Dan に注目してください。count(*) は1、count(o.id) は0です。count(*) は行を数え、LEFT JOIN が Dan のために（NULL だらけの）1行を作ったからです。count(列) は NULL でない値だけを数えます。orders テーブル単体では count(*) = 7、count(customer_id) = 6（注文107が NULL）、count(DISTINCT customer_id) = 3 でした。
WHERE はグループ化前の行を、HAVING はグループ化後のグループを絞り込みます。答えが変わります。
| クエリ | 結果 |
|---|---|
… GROUP BY c.id HAVING sum(o.amount) >= 1000 |
Chloe 8200、Aoi 4200（Ben のグループ800が落ちる） |
… WHERE o.amount >= 1000 GROUP BY c.id |
Chloe 7500、Aoi 4200（Chloe の700円の注文と Ben の800円の注文が先に落ちる） |
集約関数を WHERE に書くと SQLite は拒否します。
Parse error near line 16: misuse of aggregate: count()
ers c JOIN orders o ON o.customer_id = c.id WHERE count(o.id) > 1 GROUP BY c.i
error here ---^
よくある間違い：LEFT JOIN＋WHERE が黙って INNER JOIN になる
目標：全顧客の11月の注文数、0件も含めて。自然に思いつく最初の書き方：
SELECT c.name, count(o.id) AS nov_orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.ordered_on >= '2026-11-01'
GROUP BY c.id ORDER BY c.id;
+-------+------------+
| name | nov_orders |
+-------+------------+
| Chloe | 3 |
+-------+------------+
実際に起きたこと： LEFT JOIN は Aoi・Ben・Dan をちゃんと残しましたが、WHERE は JOIN の後に実行されます。Dan の o.ordered_on は NULL で、NULL >= '2026-11-01' は真にならないので消されます。Aoi と Ben は注文がすべて10月なので、やはり消されます。直し方： 条件を ON に移し、どの顧客が残るかではなく、どの注文をくっつけるかを決めさせます。
SELECT c.name, count(o.id) AS nov_orders
FROM customers c LEFT JOIN orders o
ON o.customer_id = c.id AND o.ordered_on >= '2026-11-01'
GROUP BY c.id ORDER BY c.id;
+-------+------------+
| name | nov_orders |
+-------+------------+
| Aoi | 0 |
| Ben | 0 |
| Chloe | 3 |
| Dan | 0 |
+-------+------------+
ルール：LEFT JOIN では、右側テーブルの条件は ON に、左側テーブルの条件は WHERE に。
よくある間違い：1対多を2つつなぐと行が爆発する
目標：顧客ごとの注文合計と支払い合計。Aoi は4,200円分注文し、4,200円を支払いました。それらしいクエリ：
SELECT c.name, sum(o.amount) AS ordered, sum(p.paid) AS paid
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN payments p ON p.customer_id = c.id
GROUP BY c.id;
+-------+---------+-------+
| name | ordered | paid |
+-------+---------+-------+
| Aoi | 12600 | 8400 |
| Chloe | 8200 | 24600 |
+-------+---------+-------+
Aoi の注文は3倍、支払いは2倍、Chloe の支払いは3倍になり、Ben は支払いがないので消えました。GROUP BY を外すと理由が見えます。Aoi の注文2件×支払い3件が6行になっていました。
+------+----------+--------+------+
| name | order_id | amount | paid |
+------+----------+--------+------+
| Aoi | 101 | 3000 | 1000 |
| Aoi | 101 | 3000 | 1200 |
| Aoi | 101 | 3000 | 2000 |
| Aoi | 102 | 1200 | 1000 |
| Aoi | 102 | 1200 | 1200 |
| Aoi | 102 | 1200 | 2000 |
+------+----------+--------+------+
注文と支払いは互いに関係がなく、顧客とだけ関係しているので、JOIN するとすべての組み合わせができてしまいます。直し方： 子テーブルごとに別々に集計し、顧客1人1行同士を JOIN します。
WITH o AS (SELECT customer_id, sum(amount) AS ordered FROM orders GROUP BY customer_id),
p AS (SELECT customer_id, sum(paid) AS paid FROM payments GROUP BY customer_id)
SELECT c.name, o.ordered, p.paid
FROM customers c
LEFT JOIN o ON o.customer_id = c.id
LEFT JOIN p ON p.customer_id = c.id
ORDER BY c.id;
+-------+---------+------+
| name | ordered | paid |
+-------+---------+------+
| Aoi | 4200 | 4200 |
| Ben | 800 | NULL |
| Chloe | 8200 | 8200 |
| Dan | NULL | NULL |
+-------+---------+------+
（WITH は名前付きのサブクエリ、つまり CTE です。第6回で詳しく扱います。）スカラーサブクエリ2つ (SELECT sum(amount) FROM orders o WHERE o.customer_id = c.id) でもまったく同じ表になりました。レポートを作るときの簡単なセルフチェック：JOIN した行の count(*) を予想と比べること。いちばん大きいテーブルの行数より多ければ、どこかで爆発しています。
同じ爆発が「都市別の顧客数」にも潜んでいます。customers LEFT JOIN orders に count(*) を使うと Osaka が 5（実は注文件数）で、count(DISTINCT c.id) にすると正しい2になりました。
GROUP BY のエラーと静かな落とし穴
最初の週に出会うエラー：
Parse error near line 17: ambiguous column name: id
SELECT id, name FROM customers c JOIN orders o ON o.customer_id = c.id;
^--- error here
両方のテーブルに id があるので、c.id か o.id と所属を書きます。テーブルが2つ以上になったら、常に別名を付ける習慣にしましょう。
静かなほう：SQLite は、グループ化も集約もしていない列（bare column）を SELECT しても受け付けます。
SELECT c.name, o.ordered_on, sum(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id;
+-------+------------+---------------+
| name | ordered_on | sum(o.amount) |
+-------+------------+---------------+
| Aoi | 2026-10-01 | 4200 |
| Ben | 2026-10-20 | 800 |
| Chloe | 2026-11-02 | 8200 |
+-------+------------+---------------+
Chloe の3つの日付のうち、これはどれでしょう。SQLite がたまたま見た行の値で、意味はありません。PostgreSQL はこのクエリを拒否し、既定の ONLY_FULL_GROUP_BY が有効な MySQL も拒否します（どちらもここでは未実行）。だから SQLite で動いていたコードが、ほかの DB に移すと壊れます。文書化された SQLite の例外が1つあります。max() か min() がちょうど1つだけなら、bare column はその最大値・最小値を持つ行から取られます。SELECT c.name, o.id, max(o.amount) … GROUP BY c.id は Aoi | 101 | 3000、Ben | 103 | 800、Chloe | 104 | 5000 と正確でしたが、SQLite 専用です。移植性のあるコードはウィンドウ関数（第6回）かサブクエリを使います。
サブクエリ vs JOIN vs EXISTS
サブクエリはほかの文の中の SELECT です。3つの問いに3つの道具。
「2,500円以上の注文が1件でもある顧客」
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount >= 2500);
-- Aoi, Chloe
SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.amount >= 2500;
-- Aoi, Chloe, Chloe
JOIN は Chloe を2回返しました（該当する注文が2件）。IN や EXISTS は「1件でもあるか」に重複なしで答え、JOIN は「一致する組を列挙」します。
「一度も注文していない顧客」 ここで NOT IN が牙をむきます。注文107の customer_id が NULL だからです。
| クエリ | 結果 |
|---|---|
WHERE id NOT IN (SELECT customer_id FROM orders) |
0件 |
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) |
Dan |
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL) |
Dan |
第1回と同じ NULL の論理です。4 NOT IN (1, 2, 3, NULL) は真ではなく NULL です。NULL を気にしなくてよい NOT EXISTS を基本にしましょう。
| やりたいこと | 使うもの | 注意点 |
|---|---|---|
| 両テーブルの列をまとめて | JOIN |
右側に一致が複数あると重複 |
| 相手のいない行も残す | LEFT JOIN |
右テーブルの条件は ON に |
| 「1件でもある」 | EXISTS / IN |
特になし、重複なし |
| 「1件もない」 | NOT EXISTS |
NOT IN はサブクエリに NULL があると0件 |
| 子テーブル2つの合計 | それぞれ先に集計（CTE）してから JOIN | 直接 JOIN すると合計が掛け算になる |
| 行ごとに値を1つ | SELECT 内のスカラーサブクエリ |
内側の列にインデックスが必要（下の実測） |
実測：相関サブクエリ vs JOIN、インデックスの有無
「サブクエリは遅い」は半分だけ正しい話です。顧客2,000件と注文200,000件を作り（顧客番号は固定の式 (i * 7919) % 2000 + 1、乱数なし）、顧客別の注文合計を3通りの方法で求めました。3つが同じ行を返すことも確認しています。Python の sqlite3 モジュール（SQLite 3.51.0）で M1 Pro 上で測り、インデックスなしは3回、ありは7回の中央値です。
-- A：相関サブクエリ
SELECT c.id, (SELECT sum(o.amount) FROM orders o WHERE o.customer_id = c.id) FROM customers c ORDER BY c.id;
-- B：LEFT JOIN + GROUP BY
SELECT c.id, sum(o.amount) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id ORDER BY c.id;
-- C：先に集計してから JOIN
SELECT c.id, t.total FROM customers c
LEFT JOIN (SELECT customer_id, sum(amount) AS total FROM orders GROUP BY customer_id) t
ON t.customer_id = c.id ORDER BY c.id;
| A：相関 | B：JOIN + GROUP BY | C：先に集計して JOIN | |
|---|---|---|---|
| インデックスなし（1回目） | 13,389.6 ms | 183.5 ms | 117.4 ms |
| インデックスなし（2回目） | 12,649.4 ms | 120.7 ms | 42.2 ms |
CREATE INDEX ON orders(customer_id)（2回目） |
88.9 ms | 93.4 ms | 86.1 ms |
インデックスなしの A の実行計画は CORRELATED SCALAR SUBQUERY 1 → SCAN o でした。顧客2,000人ごとに注文20万件を全部読むので、行の訪問は約4億回です。B と C はインデックスなしでも速かったのですが、それは上の計画で見たとおり SQLite が JOIN のために AUTOMATIC COVERING INDEX を作ったからで、相関サブクエリにはそうしてくれませんでした。本物のインデックスを作ると計画は SEARCH o USING INDEX idx_orders_customer (customer_id=?) に変わり、3つの書き方の差は約10%以内に収まりました。
つまり教訓は「サブクエリを使うな」ではなく、「実行計画を読み、行ごとに繰り返される部分の中に SCAN がないか探せ」です。小さい数字（B と C）は2回の計測の間で最大3倍近くぶれましたが、A の100倍の差はぶれませんでした。
正直な限界とバージョンメモ
- 実行したのは SQLite 3.51.0 だけです（CLI と Python 3.9 の
sqlite3モジュール、どちらも 3.51.0）。SQLite のリリースノートではRIGHT・FULL OUTER JOINが 3.39.0、CTE（WITH）が 3.8.3 で追加されています。計画に出てくるBLOOM FILTERは比較的新しい最適化で、3.51 でのみ確認しました。 - 自動インデックスは SQLite の機能です（既定で有効、
PRAGMA automatic_index）。MySQL や PostgreSQL はハッシュ結合など別の戦略を使うので、B と C の比較は変わる可能性があります（ここでは未実行）。 - bare column：PostgreSQL はエラー、MySQL は
ONLY_FULL_GROUP_BY（5.7 から既定）でエラー、SQLite は許可（実行したのは SQLite のみ）。 - 時間はノート PC 1台、データがすべてキャッシュに載った状態の値です。相関サブクエリのコストは顧客数×注文数に比例するので、データが10倍ならインデックスなしでおよそ100倍を見込んでください。
練習問題
- Osaka の顧客のうち、注文合計が5,000円以上の顧客を挙げてください。
- 都市ごとの顧客数と注文数を1つのクエリで出してください。（注意：素直な COUNT の片方は間違えます。）
解答
1.
SELECT c.name, sum(o.amount) AS total
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Osaka'
GROUP BY c.id HAVING total >= 5000;
実際の出力：Chloe | 8200。都市の条件は行の条件（WHERE）、合計の条件はグループの条件（HAVING）です。SQLite は HAVING で別名 total を使えますが、PostgreSQL では使えません（sum(o.amount) と書く。ここでは未実行）。
2.
SELECT c.city, count(DISTINCT c.id) AS customers, count(o.id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.city ORDER BY c.city;
出力：NULL | 1 | 0、Osaka | 2 | 5、Seoul | 1 | 1。count(DISTINCT c.id) の代わりに count(*) を使うと Osaka の顧客が5人になりました。JOIN が注文ごとに1行を作ったからです。注文107（顧客なし）は customers 起点の LEFT JOIN なので、どの都市にも入りません。
次回
第4回は速度です。EXPLAIN QUERY PLAN を深く読み、100万行のテーブルをインデックスの有無で測り、複合インデックスの列順と、作ったインデックスが無視されるケースを扱います。
よくある質問
JOIN を1つ足したら SUM の合計が2倍・3倍になったのはなぜ？
同じ親に1対多のテーブルを2つ（例：注文と支払い）まとめて JOIN したからです。注文の各行に支払いの全行が組み合わされ、金額が支払い件数の分だけ重複して足されます。実験では注文2件・支払い3件の顧客の注文合計が4,200ではなく12,600になりました。テーブルごとに先に集計（サブクエリか CTE）してから合計同士を JOIN します。
LEFT JOIN なのに INNER JOIN のように行が消えるのはなぜ？
右側テーブルの条件を WHERE に書くと、右側が NULL の行、つまり LEFT JOIN がわざわざ残した行が消えます。条件を ON 句に移します。11月の注文数のクエリは、条件が WHERE にあると1人、ON にあると4人（うち3人は0件）でした。
サブクエリは JOIN より遅い？
それ自体が遅いわけではありません。内側の列にインデックスのない相関サブクエリは、顧客2,000件・注文20万件で12.6秒、LEFT JOIN＋GROUP BY は121ms でした。orders(customer_id) にインデックスを作ると、3つの書き方すべてが86〜93ms でした。EXPLAIN QUERY PLAN で相関サブクエリ内の SCAN を確認します。