ガイド

JOIN をイメージで理解する

INNER / LEFT / RIGHT / FULL OUTER / CROSS JOIN を「2 つのテーブルをどう重ね合わせるか」の図で直感的に理解する。

JOIN は「2 つのテーブルを重ね合わせる」操作

JOIN は「片方の表の行と、もう片方の表の行を、共通のキー値でつなげる」操作です。「どちら側にしかない行をどう扱うか」で 5 種類に分かれますが、本質はこの 1 行に尽きます。

図はおなじみのベン図に加えて、同じデータ（会員 3 人 × 注文 3 件）を実際に INNER JOIN / LEFT JOIN / FULL OUTER JOIN したときの結果テーブルを並べたものです。先に図のイメージを頭に入れておくと、SQL を読み書きするときに「どの JOIN を使えば結果がこうなるか」が即決できるようになります。

INNER JOIN — 「両方にある行だけ」

もっとも基本的な JOIN。左の表と右の表、両方にキーが存在する行だけを返します。図でいう中央の重なり部分です。

例: 「会員 (members)」と「注文 (orders)」を member_id で INNER JOIN すると、注文を 1 件以上した会員の行だけが出てきます。

注文がない会員（Bob, Eve）→ 消える
会員マスタにいない member_id（id=4）の注文 → 消える

「足りない側を NULL で埋めて欲しくない」「正しくつながった行だけ集計したい」のが INNER JOIN の使いどころです。逆に「JOIN したらレコード数が減った！」と驚くのは、ほぼこの仕様によるもの。意図的に減らしているのか、JOIN 条件のミスで減ったのかを切り分けるには、まず LEFT JOIN に変えてみるのが定番のデバッグ手法です。

sql

SELECT m.name, m.member_id, o.amount
FROM   members m
INNER JOIN orders o ON o.member_id = m.member_id;
-- 結果: Ann が注文 2 件分の 2 行。Bob・Eve・id=4 の注文は消える

LEFT JOIN — 「左を全部残し、右は付くものだけ付ける」

左の表の行を全部保持し、右の表に対応する行があれば結合し、なければ右側の列を NULL で埋めます。SQL で最も多用される JOIN です。

例: 「会員一覧に、注文の有無と最新注文日を付けたい」というよくある要件。

注文した会員（Ann）→ 1 件以上の行で展開（n 対 1 のとき行数が増える点に注意）
注文していない会員（Bob, Eve）→ 注文側が NULL で 1 行
会員マスタにいない member_id（id=4）の注文 → 消える（左に存在しないので）

「マスタは全部残したい」場合は LEFT JOIN です。会員 × 注文・商品 × レビュー・社員 × 出張のように「左側が一覧の主役」のクエリで頻出します。

注意: WHERE 句で右側の列に条件をかけると、せっかく LEFT JOIN で残した「マッチしなかった行」が落ちて、結果的に INNER JOIN と同じになります。右側の条件は ON 句の中に書くか、WHERE r.col IS NULL を許容する条件にします。

sql

SELECT m.name, m.member_id, o.amount
FROM   members m
LEFT JOIN orders o ON o.member_id = m.member_id;
-- 結果: Ann × 2 行 / Bob × 1 行 (amount=NULL) / Eve × 1 行 (amount=NULL)
-- id=4 の注文は左に対応する会員がいないので消える

RIGHT JOIN — LEFT を裏返しただけ

RIGHT JOIN は LEFT JOIN の左右を入れ替えただけで、機能としては LEFT JOIN と同じです。次の 2 つは完全に同じ結果になります。

SELECT * FROM A LEFT  JOIN B ON A.k = B.k;
SELECT * FROM B RIGHT JOIN A ON A.k = B.k;

実務ではほぼすべてのチームが LEFT JOIN に統一しています。読み手の認知負荷を下げるためで、RIGHT JOIN を見かけたら LEFT JOIN に書き換えるのがレビューの定石です。RIGHT JOIN は知識として知っておけばよく、自分から書く必要はほぼありません。

FULL OUTER JOIN — 「どちらかにあれば全部残す」

左にしかない行・右にしかない行・両方にある行のすべてを残します。マッチしない側の列は NULL で埋まります。図でいうベン図全体です。

例: 「会員と注文のどちらかに登場するすべての member_id を見たい」。データクレンジング・移行・整合性チェックで活躍します。

注文した会員（Ann）→ 注文行数だけ展開
注文していない会員（Bob, Eve）→ 注文側 NULL で 1 行
会員マスタにいない注文（id=4）→ 会員側 NULL で 1 行残る（ここが LEFT JOIN との決定的な違い）

「両方の NULL を見つけて孤立行を検出する」のが王道の使い方:

-- 孤立しているデータ（マスタにない注文 / 注文がない会員）だけを抽出
SELECT *
FROM   members m
FULL OUTER JOIN orders o ON o.member_id = m.member_id
WHERE  m.member_id IS NULL OR o.member_id IS NULL;

MySQL は FULL OUTER JOIN をサポートしていないので、LEFT JOIN UNION RIGHT JOIN で代用するのが定番です（詳しくは FULL OUTER JOIN）。

CROSS JOIN — 「全組み合わせ」(直積)

キーで結合せず、左のすべての行 × 右のすべての行の全組み合わせを作ります。3 行 × 3 行 → 9 行。10,000 行 × 10,000 行 → 1 億行という爆発的な行数になるため、意図せず書くと事故ります。

意図的に使う場面:

カレンダー × 商品 など、対比表をすべて埋めたいとき（販売がなかった日も 0 件として 1 行欲しい）
ABテスト群 × バリエーション の母集団を作るとき
連番テーブル（generate_series など）と JOIN して欠損日を補うとき

「FROM a, b で書いて WHERE 条件を忘れた」は古典的な事故パターン。CROSS JOIN を明示的に書くと、レビュアーが「これは意図的に直積している」と分かるので推奨です。

使い分けの 1 行サマリ

JOIN 種類	左にしかない行	右にしかない行	典型用途
INNER JOIN	消える	消える	「両方にあるもの」だけ集計
LEFT JOIN	残る（右は NULL）	消える	マスタを全部残して付加情報を付ける
RIGHT JOIN	消える	残る（左は NULL）	LEFT に書き換えるのが慣習
FULL OUTER	残る	残る	整合性チェック・データクレンジング
CROSS JOIN	キー無し（全組み合わせ）		カレンダー × 商品など

迷ったときは 「マスタの行を消したくないか？」を最初に問います。消したくないなら LEFT JOIN、消えても良いなら INNER JOIN、両側のマスタ整合性をチェックしたいなら FULL OUTER、というのが現場の判断ポイントです。

よくあるつまずき

LEFT JOIN なのに行が減る → 右側の列に WHERE で条件をかけている。ON 句に移すか、IS NULL も許容する条件にする
結果が膨れ上がる → 1 対 N の結合では、左側の行が右側のマッチ件数だけ増える。集計したい場合は JOIN の前に GROUP BY や DISTINCT ON、ROW_NUMBER() を組み合わせる（重複行を消したい）
FULL OUTER が動かない → MySQL は非対応。LEFT JOIN UNION RIGHT JOIN で代用
結合キーの型が違う → 暗黙の型変換でインデックスが使われなくなることがある。VARCHAR と BIGINT を JOIN しないように、ER 図段階で型を揃える
ON 条件を書き忘れて CROSS JOIN になる → 古いカンマ区切り構文 FROM a, b WHERE ... でやらかしがち。明示的な JOIN ... ON を徹底する