Skip to main content

結合と集計 — 複数の表から 1 つの答えを作る

顧客ごとの売上を出すクエリを書いたら、合計が実際の 2 倍になっていた。注文が 1 件も無い顧客の注文数が 1 と表示された。使われていないクーポンを探したら 1 件も返ってこなかった。

どれも SQL としては正しく実行されています。返ってきた数が意図と違うだけです。よくある原因は 3 つです。結合で行が増えたか、集計の粒度がずれたか、NULL で行が落ちたか。この章では、その 3 つがどういう仕組みで起きるかを見ます。原因はこれだけではありません。たとえば日付で範囲を切るときの境界のタイムゾーンでも数は変わります (インデックス)。

この章で学ぶこと​

  • SELECT が評価される順序と、そこから導ける 2 つの制約
  • 内部結合と外部結合の違いを行の増減で捉える
  • 1 対多の結合で親の行が増えること
  • GROUP BY が決める集計の粒度と、WHERE と HAVING の使い分け
  • COUNT(*) と COUNT(列) が違う理由
  • NULL の三値論理と、NOT IN が 1 行も返さなくなる条件
  • 「件数が合わない」ときの切り分け
前提知識

SQL で SELECT と WHERE を書いた経験があれば読めます。結合条件の列にインデックスが要る話が出てくるので、インデックス を先に読んでおくと理解が早くなります。

この章で扱わないこと​

観点参照先
テーブル設計と正規化データベース設計ガイド — 正規化
多対多の中間テーブルの設計データベース設計ガイド — 多対多のテーブル設計
EXPLAIN の読み方と、結合条件の列へのインデックス設計データベース設計ガイド — インデックス設計入門
ORM で一覧を返すときの N+1 問題Laravel API 開発ガイド — 一覧 API
サブクエリ、CTE、ウィンドウ関数サブクエリとウィンドウ関数

例に使う表​

以下のクエリはすべて、この定義とデータに対して実行した結果を載せています。

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  plan        TEXT,                              -- 未設定なら NULL
  referrer_id INTEGER REFERENCES customers(id)   -- 紹介者。いなければ NULL
);

CREATE TABLE coupons (
  id   INTEGER PRIMARY KEY,
  code TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_on  TEXT    NOT NULL,                  -- ISO 8601 の日付 (文字列の並びが日付の並びと一致する)
  amount      INTEGER NOT NULL,
  coupon_id   INTEGER REFERENCES coupons(id)     -- 使っていなければ NULL
);

customers は 5 行です。

idnameplanreferrer_id
1佐藤standard(NULL)
2鈴木premium1
3田中(NULL)1
4高橋standard(NULL)
5伊藤premium2

orders は 7 行です。

idcustomer_idordered_onamountcoupon_id
112026-07-0330001
212026-07-155000(NULL)
322026-07-205000(NULL)
432026-08-0112002
552026-08-059500(NULL)
622026-08-102000(NULL)
712026-08-121500(NULL)

coupons は 3 行 (1 SUMMER / 2 NEWBIE / 3 AUTUMN) で、AUTUMN は一度も使われていません。

この 2 点を覚えておいてください。**高橋には注文が 1 件もなく、佐藤には注文が 3 件あります。**後半の例はこの偏りの上で動きます。

SELECT はどの順で評価されるか​

書く順序と評価される順序は違います。PostgreSQL は SELECT の処理をこの順で定義しています (PostgreSQL — SELECT)。WITH、DISTINCT、UNION は省いてあります。

順段階すること
1FROM表を読み、結合する
2WHERE条件に合わない行を落とす
3GROUP BY と集計関数行をグループにまとめ、集計値を計算する
4HAVING条件に合わないグループを落とす
5SELECT出力する列を計算する。列の別名はここで決まる
6ORDER BY並べ替える
7LIMIT先頭から必要な件数だけ返す

この順序を覚えると、初学者がつまずく 2 つの制約を毎回思い出さずに済みます。

**WHERE に集計関数を書けません。**集計値が計算されるのは 3 で、WHERE が動く 2 の時点ではまだ存在しないからです。

-- エラーになる: WHERE の時点で SUM の値はまだ無い
SELECT customer_id, SUM(amount) FROM orders
WHERE SUM(amount) >= 5000
GROUP BY customer_id;

手元の SQLite 3.51.0 では misuse of aggregate: SUM() で止まります。集計した結果でグループを絞りたいなら、4 の HAVING を使います。

**SELECT で付けた別名を WHERE で使えません。**別名が決まるのは 5 だからです。MySQL はこの制約を「WHERE 句が評価される時点では、その列の値がまだ決まっていない可能性があるため」と説明しています (MySQL — Problems with Column Aliases)。同じ別名を ORDER BY では使えます。6 は 5 の後だからです。

ただし**この制約を実装が守るとは限りません。**手元の SQLite では SELECT amount * 2 AS doubled FROM orders WHERE doubled > 5000 が exit 0 で 4 行を返しました。標準 SQL が禁じている書き方を通す実装は珍しくないので、1 つのデータベースで動いたことを移植できる根拠にはできません。

結合は行の増減で捉える​

結合の違いを「左を全部残すかどうか」で覚えると、行数がずれたときに追えなくなります。何行できるかで捉えます。

内部結合 (INNER JOIN) は、左の各行について、結合条件を満たす右の行の数だけ行を作ります (PostgreSQL — Table Expressions)。満たす行が無ければ 0 行、つまりその行は消えます。

SELECT c.name, o.ordered_on, o.amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

結果は 7 行です。顧客は 5 人いるのに 7 行あり、注文が無い高橋は現れません。customers を起点にしても、行数を決めているのは orders の側です。

外部結合 (LEFT JOIN) は、内部結合をしたうえで、相手がいなかった左の行に、右側を NULL で埋めた行を足します。したがって左の各行につき最低 1 行が残ります (出典は同じ)。

SELECT c.name, o.ordered_on, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

結果は 8 行になり、高橋の行が ordered_on と amount を NULL にして 1 行残ります。**高橋の注文が 0 件であることと、高橋の行が 1 行あることは両立します。**この食い違いが後述の COUNT の落とし穴を生みます。

**外部結合は WHERE の書き方で内部結合と同じ結果になります。**右表の列に「値がこうである」型の条件を足すと、NULL で埋めた行はその条件を満たさないので落ちます。上のクエリに WHERE o.amount >= 3000 を足すと 4 行になり、内部結合に同じ条件を付けたときと一致しました。左を残したまま右を絞りたいなら、条件は ON の側に書きます。

-- 田中と高橋を NULL のまま残したうえで、3000 円以上の注文だけを結び付ける
SELECT c.name, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.amount >= 3000
ORDER BY c.id, o.id;

こちらは 6 行で、田中と高橋が amount を NULL にして残ります。

1 対多を 2 つ束ねると行が掛け算になる​

顧客ごとに「注文の合計金額」と「紹介した人数」を 1 本のクエリで出そうとすると、結果が壊れます。

-- 佐藤の合計が実際の 2 倍になる
SELECT c.name, COUNT(o.id) AS orders, SUM(o.amount) AS total
FROM customers AS c
LEFT JOIN orders    AS o ON o.customer_id = c.id
LEFT JOIN customers AS r ON r.referrer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

佐藤は orders が 6、total が 19000 になります。実際の注文は 3 件・9500 円です。注文 3 行と紹介 2 行が組み合わさって 6 行になり、同じ金額を 2 回ずつ足しているからです。

**COUNT(DISTINCT o.id) に変えると件数は 3 に直りますが、SUM は 19000 のままです。**重複を除けるのは数える対象が行の識別子だからで、金額の合計は重複した行をそのまま足し続けます。件数だけを見て直ったと判断すると、金額の誤りが残ります。

直し方は、結合する前に集計を済ませることです。

SELECT c.name, COALESCE(o.orders, 0) AS orders, COALESCE(o.total, 0) AS total
FROM customers AS c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
  FROM orders GROUP BY customer_id
) AS o ON o.customer_id = c.id
ORDER BY c.id;

佐藤は 3 件・9500 円に戻ります。FROM の中に書いた SELECT は派生テーブルと呼びます。詳しくは サブクエリとウィンドウ関数 で扱います。

自分自身と結合する​

紹介者の名前を出すには、customers を 2 回使います。同じ表に別々の別名を付ければ、別の表として扱えます。

SELECT c.name AS customer, r.name AS referrer
FROM customers AS c
LEFT JOIN customers AS r ON r.id = c.referrer_id
ORDER BY c.id;

鈴木と田中には佐藤、伊藤には鈴木が並び、紹介者のいない佐藤と高橋は referrer が NULL になります。**ここを INNER JOIN にすると 5 行が 3 行に減ります。**自己結合は「全員に対して補足情報を付ける」用途で使うことが多いので、既定は外部結合と考えるほうが事故が少なくなります。

なお、ON r.id = c.referrer_id のように結合条件に使う列は、行数が増えると検索の負荷になります。結合条件の列にインデックスが要る理由は インデックス で扱った仕組みと同じです。

集計の粒度は GROUP BY が決める​

GROUP BY は、指定した列の値が同じ行を 1 つのグループにまとめます。出力はグループごとに 1 行です。何行に畳まれるかは指定した列だけで決まります。

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 5000
ORDER BY total DESC, customer_id;

顧客 1 (9500 円・3 件)、顧客 5 (9500 円・1 件)、顧客 2 (7000 円・2 件) の 3 行が返り、1200 円の顧客 3 は HAVING で落ちます。

**WHERE は行を落とし、HAVING はグループを落とします。**評価順序の 2 と 4 の違いがそのまま役割の違いです。同じ「5000 円以上」でも、WHERE amount >= 5000 は 1 件あたり 5000 円以上の注文だけを集めてから合計し、HAVING SUM(amount) >= 5000 は全注文を合計してから 5000 円以上のグループを残します。**集計値に対する条件を WHERE へ移すことはできません。**逆向き (グループ化した列そのものへの条件) なら HAVING から WHERE へ移せますが、その場合は集計する前に行を減らせるぶん WHERE のほうが素直です。

GROUP BY に無い列を SELECT に書けるか​

ここは実装によって答えが変わります。

データベース挙動
PostgreSQL集計式の中を除き、GROUP BY に無い列は参照できない。例外は関数従属で、グループ化した列がその表の主キーなら参照できる (SELECT)
MySQLONLY_FULL_GROUP_BY が既定で有効なので、GROUP BY に無く関数従属でもない列を含むクエリを拒否する (MySQL — GROUP BY Handling)
SQLite**制限しない。**グループ内のどの行の値が返るかは決まらない (SQLite — Quirks)

実際、SQLite で SELECT customer_id, ordered_on, SUM(amount) FROM orders GROUP BY customer_id は exit 0 で通り、ordered_on にはグループ内のいずれかの行の日付が入りました。手元で通ることは、意味のある値が返っていることを保証しません。

COUNT(*) と COUNT(列) は違う​

  • count(*) は入力行の数を数えます
  • count(式) は式が NULL でない行の数を数えます

出典は PostgreSQL — Aggregate Functions です。この違いが表に出るのが、外部結合と組み合わせたときです。

SELECT c.name, COUNT(*) AS cnt_star, COUNT(o.id) AS cnt_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

高橋の行は cnt_star が 1、cnt_orders が 0 になります。外部結合が NULL で埋めた行も 1 行は 1 行なので、COUNT(*) はそれを数えます。注文の件数を知りたいなら、数える対象は右表の列です。

NULL は「不明」を表す​

NULL は 0 でも空文字でもなく、値が不明であることを表します。不明なもの同士を比べた結果は不明にしかならないので、比較の結果は真と偽のどちらでもない第 3 の値になります。

手元で SELECT NULL = NULL を実行すると、返ってくるのは 0 でも 1 でもなく NULL です。そして **WHERE は結果が真の行だけを通します。**偽と不明はどちらも落ちます。だから WHERE plan <> 'premium' で plan が NULL の顧客は返りません。実際、上のデータでは佐藤と高橋の 2 人だけが返り、plan が NULL の田中は落ちました。NULL かどうかを見たいときは IS NULL を使います。

NOT IN に NULL が混じると 1 行も返らない​

一度も使われていないクーポンを探します。答えは AUTUMN です。

-- 0 行しか返らない
SELECT id, code FROM coupons
WHERE id NOT IN (SELECT coupon_id FROM orders);

orders.coupon_id には NULL の行があります。PostgreSQL は「左辺が NULL になるか、等しい右辺の値が無くかつ右辺のいずれかの行が NULL になる場合、NOT IN の結果は真ではなく NULL になる」と明記しています (PostgreSQL — Subquery Expressions)。3 と NULL が等しいかどうかを判定できないため、結果は不明になり、行が通りません。

**エラーは出ません。**空の結果が返るだけなので、「使われていないクーポンは無い」と読み違えます。

-- AUTUMN が返る
SELECT id, code FROM coupons
WHERE id NOT IN (SELECT coupon_id FROM orders WHERE coupon_id IS NOT NULL);

NULL を先に除けば期待どおりに動きます。より安全なのは NOT EXISTS に書き換える方法で、サブクエリとウィンドウ関数 で扱います。

「件数が合わない」ときの切り分け​

期待と違う件数が返ったら、次の順に見ます。上から順に、行が増える原因・畳まれ方の原因・行が消える原因です。

  1. 結合で行が増えていないか — SELECT の中身を COUNT(*) に置き換えて FROM と JOIN だけの行数を数えます。元の表の行数より多ければ、1 対多の結合が入っています
  2. 集計の粒度が想定と違わないか — GROUP BY に並べた列が、出したい単位と一致しているかを見ます。顧客ごとのつもりで注文の日付を混ぜると、顧客ごとには畳まれません
  3. NULL で行が落ちていないか — WHERE の条件に NULL を含みうる列があるか、NOT IN を使っているかを見ます

1 と 3 は逆向きです。多すぎるなら結合、少なすぎるなら NULL から疑うと早く着きます。

この 3 つで説明が付かないなら、条件そのものを疑います。日付で範囲を切るときの境界のタイムゾーンが典型で、インデックス が扱っています。

よくある誤解​

「LEFT JOIN を使えば左の行は必ず残る」 — WHERE に右表の列の値の条件を書くと、NULL で埋めた行が落ちて内部結合と同じ結果になります。左を残したいなら条件は ON に置きます。逆に WHERE o.id IS NULL と書けば NULL で埋めた行だけが残り、「相手のいない行」を探せます。

「COUNT(*) も COUNT(列) も行数を数える」 — COUNT(*) は行数、COUNT(列) はその列が NULL でない行数です。外部結合と組み合わせると差が出ます。

「NOT IN は IN の否定」 — NULL が混じると NOT IN は真にならず、1 行も返りません。IN の側も NULL があると偽ではなく NULL を返します。

「GROUP BY に無い列を書けるかどうかは SQL の仕様で決まっている」 — PostgreSQL と MySQL はどちらも、関数従属で説明できる場合を除いて拒否します (MySQL は既定で有効な ONLY_FULL_GROUP_BY がこれを担います)。SQLite は通します。手元で通ったことは移植できる根拠になりません。

「集計の間違いはエラーで気づける」 — この章で見た失敗はどれもエラーになりません。金額が 2 倍になる、件数が 1 多い、0 行が返る。すべて正常終了します。

確認問題​

問 1. 上の customers (5 行) と orders (7 行) を LEFT JOIN した結果は何行になりますか。また INNER JOIN なら何行ですか。

LEFT JOIN は 8 行、INNER JOIN は 7 行です。

内部結合では、結合条件を満たす組み合わせの数だけ行ができます。注文 7 件はそれぞれ 1 人の顧客に結び付くので 7 行です。注文が無い高橋はここで消えます。

外部結合では、これに「相手がいなかった左の行」が NULL 埋めで加わります。該当するのは高橋の 1 行だけなので、7 + 1 = 8 行です。

**顧客の人数 (5) と一致しないことが要点です。**顧客ごとに 1 行ほしいなら、結合しただけでは足りず、GROUP BY で畳むか、先に orders を集計してから結合します。

問 2. customers LEFT JOIN orders を顧客ごとに GROUP BY して COUNT(*) と COUNT(o.id) を並べると、高橋の行はそれぞれいくつになりますか。理由も説明してください。

COUNT(*) は 1、COUNT(o.id) は 0 です。

高橋には注文がないので、外部結合は右側の列をすべて NULL で埋めた行を 1 行作ります。この行はグループの中に存在するので、入力行の数を数える COUNT(*) は 1 と答えます。

一方 COUNT(o.id) は o.id が NULL でない行だけを数えます。埋められた行の o.id は NULL なので 0 になります。

**注文の件数を出したいなら後者です。**同じ理由で SUM(o.amount) は 0 ではなく NULL を返すので、0 を出したいなら COALESCE(SUM(o.amount), 0) のように包みます。

問 3. SELECT id, code FROM coupons WHERE id NOT IN (SELECT coupon_id FROM orders) が 1 行も返しません。エラーは出ていません。原因と直し方を説明してください。

原因は orders.coupon_id に NULL が含まれていることです。

NOT IN は、右辺の各値と等しくないことを確かめます。右辺に NULL があると「3 と NULL が等しくない」を判定できず、結果が真になりません。真でない行は WHERE を通らないので、右辺に NULL が 1 つでもあれば結果は常に空になります。

直し方は 2 つあります。

  • 右辺から NULL を除く: WHERE id NOT IN (SELECT coupon_id FROM orders WHERE coupon_id IS NOT NULL)
  • NOT EXISTS に書き換える: WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.coupon_id = coupons.id)

後者は「条件を満たす行が存在するか」だけを見るので、NULL の有無に影響されません。どちらでも AUTUMN が返ります。

この誤りが危険なのは、空の結果が正しい答えに見えることです。「該当なし」が返ったら、まず NULL を疑ってください。

まとめ​

  • 評価順序は FROM → WHERE → GROUP BY と集計 → HAVING → SELECT → ORDER BY → LIMIT です。WHERE に集計関数を書けないことも、別名を使えないことも、ここから導けます
  • 結合は「左を残すか」ではなく何行できるかで捉えます。内部結合は条件を満たす組み合わせの数、外部結合はそれに NULL 埋めの行を足した数です
  • **1 対多を 2 つ束ねると行が掛け算になり、合計が膨らみます。**先に集計してから結合します
  • WHERE は行を落とし、HAVING はグループを落とします。集計値への条件は WHERE へ移せません
  • COUNT(*) は行数、COUNT(列) はその列が NULL でない行数です
  • NULL との比較は真にも偽にもならず、WHERE を通りません。NOT IN の右辺に NULL があると結果は常に空になります
  • 件数が合わないときは、多すぎるなら結合、少なすぎるなら NULL から疑います
関連リファレンス

次に読む​