SQLのINNER JOINとLEFT JOINで件数が変わる理由

INNER JOINしたら注文が減った。残すためにLEFT JOINへ変えたら、今度は売上が増えた。あるいはLEFT JOINなのに、条件を加えると注文が消える。

JOINがうまくいかないときは、「未一致の行」と「複数行に一致するキー」を分けて確認してください。INNER JOINは結合条件に一致しない左側の行を残しません。LEFT JOINは左側の行を残しますが、右側で複数行に一致すれば結果の行数は増えます。さらに、結合後のWHERE条件によって行が除外されることもあります。

この記事でわかること

INNER JOINとLEFT JOINで件数が変わる仕組み
消えた行と増えた行を見つけるSQL
WHEREとONを目的に応じて使い分ける方法

結論：JOINの種類だけでは結果件数は決まらない
実行例：同じデータでINNER JOINとLEFT JOINを比べる
1. INNER JOINの結果
2. LEFT JOINの結果
件数が減ったとき：一致しない左側のキーを探す
件数が増えたとき：右側で複数行に一致していないか確認する
1. どう直すか
LEFT JOINなのに件数が減るとき：WHEREとONを比べる
まずここから確認：JOINがうまくいかないときの順番
初学者が詰まりやすい3つのポイント
現場での注意点と、直らない場合の確認先
まとめ
次に読む記事

結論：JOINの種類だけでは結果件数は決まらない

結合後の件数は、主に次の3点で決まります。

確認すること	結果への影響
左側の行に右側の一致行があるか	`INNER JOIN`では未一致行が消え、`LEFT JOIN`では残る
右側に何行一致するか	複数行一致すれば、両方のJOINで結果が増え得る
JOIN後にどんな`WHERE`条件があるか	`LEFT JOIN`で残した行も除外され得る

したがって、「INNERからLEFTにすれば元の件数に戻る」とは限りません。欲しい結果が1行＝注文なのか、1行＝決済なのかも決めてから確認します。

JOINの基本構文を先に整理したい場合は、サイト内のSQL入門記事も参考になります。

実行例：同じデータでINNER JOINとLEFT JOINを比べる

以下の例はPostgreSQLで実行可能です。WITH句のVALUESを使っているため、ほかのDB製品ではサンプルデータを作る構文を調整してください。

注文データ：1行＝1注文

order_id	amount
101	100
102	200
103	300

決済データ：1行＝1件の決済記録

order_id	status
101	paid
101	pending
102	paid

注文は3行、売上合計600です。注文103に対応する決済はなく、注文101には決済記録が2行あります。

INNER JOINの結果

WITH orders (order_id, amount) AS (
  VALUES (101, 100), (102, 200), (103, 300)
),
payments (order_id, status) AS (
  VALUES (101, 'paid'), (101, 'pending'), (102, 'paid')
)
SELECT o.order_id, o.amount, p.status
FROM orders AS o
INNER JOIN payments AS p
  ON o.order_id = p.order_id
ORDER BY o.order_id, p.status;

実行結果

order_id	amount	status
101	100	paid
101	100	pending
102	200	paid

結果は3行、amountの合計は400です。注文103は一致する決済がないため消えます。一方、注文101は決済が2行一致するため、売上100が2行に現れます。

ここで結果の3行と元の注文3行は、件数が偶然同じです。注文103が消え、注文101が1行増えています。COUNT(*)だけでは、この入れ替わりに気づけません。

LEFT JOINの結果

上のSQLのINNER JOINをLEFT JOINに変更します。

WITH orders (order_id, amount) AS (
  VALUES (101, 100), (102, 200), (103, 300)
),
payments (order_id, status) AS (
  VALUES (101, 'paid'), (101, 'pending'), (102, 'paid')
)
SELECT o.order_id, o.amount, p.status
FROM orders AS o
LEFT JOIN payments AS p
  ON o.order_id = p.order_id
ORDER BY o.order_id, p.status;

実行結果

order_id	amount	status
101	100	paid
101	100	pending
102	200	paid
103	300	NULL

結果は4行、amountの合計は700です。未一致の注文103は残りますが、注文101の複数一致もそのまま残ります。

このため、LEFT JOINは「左側の元の行を残す」という性質を持っていても、結果を左表と同じ行数にする機能ではありません。

件数が減ったとき：一致しない左側のキーを探す

INNER JOIN後に件数が減ったら、右側に一致する行がないキーを確認します。

SELECT o.order_id, o.amount
FROM orders AS o
LEFT JOIN payments AS p
  ON o.order_id = p.order_id
WHERE p.order_id IS NULL;

この例なら注文103が返ります。実際の表でも、右側の結合キーがNULLになった行を調べると、未一致の左側の行を特定できます。

次に、なぜ一致しなかったかを確認します。

右表にそのキーの行が存在しない
結合に使う列が違う
文字列の空白や表記、データ型が合っていない
店舗IDや年月など、追加の結合条件が必要
NULLのキーが含まれる

注文103に決済がないのが正常なら、注文を全件出したい一覧にはLEFT JOINが合います。一方、「決済記録がある注文だけ」を抽出したいなら、INNER JOINで注文103が消えるのは意図どおりです。

件数が増えたとき：右側で複数行に一致していないか確認する

LEFT JOIN後に件数や売上が増えた場合は、まず右表の結合キーごとの行数を調べます。

SELECT order_id, COUNT(*) AS payment_rows
FROM payments
GROUP BY order_id
HAVING COUNT(*) > 1;

サンプルでは、注文101が2行と分かります。決済履歴が複数あること自体は正しい場合もあります。問題は、その結果を1行＝注文として扱い、注文売上をそのまま合計することです。

結合前後でどのキーが増えたか、粒度をどうそろえるかは、既存記事で詳しく解説しています。

右表だけでなく、左表のキーにも重複がある場合は、多対多の組み合わせでさらに行が増えます。元データの重複キーと具体的な明細を調べたい場合はこちらです。

どう直すか

注文ごとに「決済記録の件数」が欲しいなら、決済側を注文単位に集計してから結合します。

SELECT
  o.order_id,
  o.amount,
  p.payment_count
FROM orders AS o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS payment_count
  FROM payments
  GROUP BY order_id
) AS p
  ON o.order_id = p.order_id;

この方法は、ordersが1行＝1注文であることを前提とします。注文側にも同じorder_idが複数行ある場合は、そこも先に確認してください。

LEFT JOINなのに件数が減るとき：WHEREとONを比べる

LEFT JOINで注文を残した後に、次の条件を追加したとします。

SELECT o.order_id, p.status
FROM orders AS o
LEFT JOIN payments AS p
  ON o.order_id = p.order_id
WHERE p.status = 'paid';

サンプルでは、注文101と102の2行だけになります。注文103は右表のstatusがNULLなので条件を満たさず、注文101のpending行も除外されます。

注文を全件残しながら、paidの決済だけを結び付けたいなら、条件をONに置きます。

SELECT o.order_id, p.status
FROM orders AS o
LEFT JOIN payments AS p
  ON o.order_id = p.order_id
 AND p.status = 'paid';

この結果は次のとおりです。

order_id	status
101	paid
102	paid
103	NULL

ONはどの右表の行を一致とみなすかを決め、WHEREは結合後の結果を絞ります。

なお、上のONのSQLで注文103のstatusがNULLなのは、paidの決済が結合されなかったことを意味します。「決済記録が一件もない」と常に同じ意味ではありません。

まずここから確認：JOINがうまくいかないときの順番

順番	確認すること	見つかる原因
1	左表の行数・IDの種類数・合計	比較の基準
2	右表でキーごとの行数	1対多・多対多の可能性
3	`INNER JOIN`と`LEFT JOIN`の結果をID別に比較	未一致で消えた行
4	JOIN後のIDごとの行数	複数一致で増えた行
5	`WHERE`を一時的に外して比較	結合後に除外された行
6	修正後にID別・全体の件数と合計を再計算	消失と二重計上の解消