結合 — どちら側を残すのか

一言でいうと

結合は、2つの集合の行を条件で対応づける演算であり、実務のバグの大半は、対応する行がない行をどう扱うかを誤って決めたことから生まれます。

なぜ必要なのか

正規化の代償が結合です。顧客情報と注文情報を分けておいたので、一緒に見るには、再び結びつけなければなりません。問題は、「対応する行がない行」をどうするかです。注文のない顧客を、結果に残すのか、除くのか。この決定が、そのまま結合の種類です。

どう動くのか

INNER JOIN: 両側に対応する行がある行だけを残します。
LEFT JOIN: 左側はすべて残し、対応する行がなければ右側のカラムをNULLで埋めます。
アンチ結合: 対応する行がない行だけを残します。NOT EXISTSで書くのが最も明確です。
CROSS JOIN: すべての組み合わせです。結合条件を抜かすと、意図せずこれになります。

ここで、最も重要な落とし穴を押さえておく必要があります。LEFT JOINの後のWHEREに右側のテーブルの条件を書くと、その瞬間にINNER JOINになります。

-- 모든 고객 + 그중 결제 완료 주문 (고객 400명 유지)
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id;

-- 결제 완료 주문이 있는 고객만 (고객 수가 줄어든다)
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id;

対応する行がない行は、右側のカラムがNULLになりますが、o.status = 'paid'はNULLに対して不明を返すので、その行たちがまるごと除外されます。左外部結合の絞り込みはON句に、左側のテーブルの絞り込みはWHERE句に置くのがルールです。

集計と組み合わさるときの落とし穴が、もう1つあります。LEFT JOINの後にcount(*)を使うと、対応する行がない行も1と数えてしまいます。count(o.id)のように、結合された側のカラムを数えて初めて、0が出ます。集計関数がNULLを無視するという性質を利用するのです。

現場での姿

結合が行を増やすという事実も、よく忘れられます。注文1つに明細が3つあれば、結合結果は3行になり、その状態でsum(o.total_amount)を行うと、注文金額が3倍になります。このようなファンアウトは、レポートの数字を静かに膨らませる代表的な原因です。解決策は、先に集計してから結合するか、sum(DISTINCT ...)ではなくサブクエリで事前に畳むことです。

性能の面では、結合アルゴリズム3つを知っておく価値があります。外側の行が少なく、内側にインデックスがあればネステッドループ、等号結合で片方をメモリに載せられればハッシュ結合、両側がすでに並べ替えられていればマージ結合が有利です。オプティマイザーが選ぶことですが、計画でネステッドループの繰り返し回数が数万回を超えていたら、外側の行数の推定が間違っているというサインです。

結合の種類を図で整理すると

A (주문)          B (고객)
 ┌───┐            ┌───┐
 │ 1 │────────────│ 1 │      INNER   : 양쪽에 다 있는 것만
 │ 2 │────────────│ 2 │      LEFT    : A 는 전부, 짝 없으면 B 쪽이 NULL
 │ 3 │            │   │      RIGHT   : B 는 전부
 │   │            │ 4 │      FULL    : 양쪽 전부
 └───┘            └───┘

実務の90%は、INNERとLEFTです。RIGHTはLEFTにひっくり返して書くほうが読みやすく、FULLはデータの突き合わせ(どちらか一方にだけあるものを探す)に使います。

LEFT JOINで、条件をどこに書くかが結果を変えます。これが最もよくある間違いです。

-- 취소되지 않은 결제가 있는 주문 + 결제가 아예 없는 주문
select o.*, p.amount
from orders o
left join payments p on p.order_id = o.id and p.status <> 'canceled';
                                            ↑ ON 절 — 짝을 찾는 조건

-- 사실상 INNER JOIN 이 된다. 짝이 없는 행은 p.status 가 NULL 이라 걸러진다
from orders o
left join payments p on p.order_id = o.id
where p.status <> 'canceled';
      ↑ WHERE 절 — 조인 결과를 거르는 조건

LEFT JOINの後で、右側の表の条件をWHEREに書くと、LEFTの意味が失われます。右側の表の条件はONに、左側の表の条件はWHEREに置くのがルールです。

行が膨らむことに気づく

結合は、対応する行が複数あると、行を掛け算します。注文1つに決済が3件あれば、その注文が3行になり、その状態でsum(o.amount)を行うと、金額が3倍になります。

-- ❌ 주문 금액이 결제 건수만큼 부풀려진다
select sum(o.amount) from orders o join payments p on p.order_id = o.id;

-- ✅ 미리 접어서 조인한다
select sum(o.amount)
from orders o
join (select order_id from payments group by order_id) p on p.order_id = o.id;

-- ✅ 또는 존재 여부만 물을 때는 EXISTS
select sum(o.amount) from orders o
where exists (select 1 from payments p where p.order_id = o.id);

EXISTSは、最初の対応する行を見つけると止まるので、行を増やさず、たいてい速いです。「あるかどうかだけわかればよい」場合には、結合よりこちらが合っています。

NOT INのNULLの罠

-- 서브쿼리 결과에 NULL 이 하나라도 있으면 전체가 빈 결과가 된다
select * from orders where customer_id not in (select id from vip_customers);

-- 안전하다
select * from orders o
where not exists (select 1 from vip_customers v where v.id = o.customer_id);

x NOT IN (1, 2, NULL)は、x <> 1 and x <> 2 and x <> NULLですが、最後がUNKNOWNなので、全体が真になりません。NOT INの代わりにNOT EXISTSを使うのが安全です。

次のラボですること

内部結合、外部結合、アンチ結合、自己結合を順に書いて、ON句とWHERE句に同じ条件を置いたときに、結果がどう分かれるかを自分で比較します。