INNER JOIN と OUTER JOIN の違い（LEFT・RIGHT・FULL）

JOIN の種類は 4 つしかない。本 4 冊と出版社 3 社、この小さな表だけで 4 つ全部の違いが出せる。

結論

• INNER JOIN … 両方の表にペアがある行だけ残す
• LEFT JOIN … 左の表は全部残す。相手がいなければ右側が NULL
• RIGHT JOIN … 右の表は全部残す。左右が入れ替わっただけで LEFT と同じもの
• FULL OUTER JOIN … 両方の表を全部残す。ペアがない側は NULL
• 迷ったら「消えて困る行はどっちの表にあるか」だけ考えれば選べる

題材：本と出版社

publishers（出版社）

books（本）

books.publisher_id が publishers.id を指している。この「指している列」があるから 2 つの表をくっつけられる。

仕込んであるのは次の 2 点で、これが 4 種類の JOIN の違いを全部生む。

• 詩集・空 は出版社が未設定（publisher_id が NULL）＝ 右に相手がいない
• つばさ文庫 は本が 1 冊もない ＝ 左に相手がいない

erDiagram
    publishers ||--o{ books : "1社が複数の本を出す"
    publishers {
        int id
        string name
    }
    books {
        int id
        string title
        int publisher_id
    }

CREATE TABLE publishers (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE books (
  id           serial PRIMARY KEY,
  title        text NOT NULL,
  publisher_id integer REFERENCES publishers (id)
);

INSERT INTO publishers (id, name) VALUES
  (1, 'あおい書房'), (2, 'ひかる出版'), (3, 'つばさ文庫');

INSERT INTO books (id, title, publisher_id) VALUES
  (101, '深夜のSQL', 1),
  (102, '図解データベース', 1),
  (103, 'はじめてのGo', 2),
  (104, '詩集・空', NULL);

JOIN とは「2 つの表を照合して 1 つの表にする」

JOIN の正体は難しくない。共通の列で突き合わせて、1 枚の表に合体させるだけ。

FROM books
INNER JOIN publishers ON publishers.id = books.publisher_id

ON の後ろが「どう照合するか」の宣言。ここでは「books.publisher_id と publishers.id が同じ値なら、その 2 行はペア」と言っている。

違いが出るのは ペアが作れなかった行をどう扱うか の一点だけ。それが INNER / LEFT / RIGHT / FULL の分岐になる。

flowchart TD
    Q["ペアが作れなかった行をどうする？"] --> Inner["消す<br/>→ INNER JOIN"]
    Q --> Left["左の表の行は残す<br/>→ LEFT JOIN"]
    Q --> Right["右の表の行は残す<br/>→ RIGHT JOIN"]
    Q --> Full["両方とも残す<br/>→ FULL OUTER JOIN"]

INNER JOIN — ペアがある行だけ

SELECT b.title, p.name
FROM books AS b
INNER JOIN publishers AS p ON p.id = b.publisher_id;

本は 4 冊あったのに 3 行しか出ない。詩集・空（出版社なし）も つばさ文庫（本なし）も、ペアを作れず落ちた。

勘所。
INNER JOIN は「くっつける」だけでなく、相手がいない行を除外するフィルタとしても働く。「出版社が紐づいている本だけ欲しい」ときに JOIN を使うのは、この性質を利用している。

INNER は省略でき、単に JOIN と書くと INNER JOIN の意味になる。

LEFT JOIN — 左の表を全部残す

FROM に書いた側（左）の行は必ず残し、相手がいなければ右側を NULL で埋める。

SELECT b.title, p.name
FROM books AS b
LEFT OUTER JOIN publishers AS p ON p.id = b.publisher_id;

本 4 冊が全部残った。出版社が未設定の 詩集・空 は name が NULL になる。

定番テクニック：「持っていないもの」を探す

相手がいない行は右側が必ず NULL になる。そこを WHERE で狙い撃つ。

-- 出版社が紐づいていない本を探す
SELECT b.title
FROM books AS b
LEFT OUTER JOIN publishers AS p ON p.id = b.publisher_id
WHERE p.id IS NULL;

「一度も注文していないユーザー」「レビューが付いていない商品」など、実務で頻出する形。

RIGHT JOIN — 右の表を全部残す

LEFT の鏡写し。JOIN に書いた側（右）の行を全部残す。

SELECT b.title, p.name
FROM books AS b
RIGHT OUTER JOIN publishers AS p ON p.id = b.publisher_id;

今度は出版社 3 社が全部残り、本が 1 冊もない つばさ文庫 の title が NULL になる。

RIGHT JOIN は書かなくても困らない。
A RIGHT JOIN B は B LEFT JOIN A と同じ結果になる。表の順番を入れ替えるだけで LEFT に統一できるので、読み手の頭の負担を減らすために LEFT で書き切る現場が多い。

FULL OUTER JOIN — 両方を全部残す

SELECT b.title, p.name
FROM books AS b
FULL OUTER JOIN publishers AS p ON p.id = b.publisher_id;

ペアがある 3 行＋左だけの 1 行＋右だけの 1 行で 5 行。LEFT と RIGHT を足したものと思えばよい。

「どちらの表にも、相手がいない行がある」＝ データの不整合を洗い出したいときに使う。

-- 紐づいていない行を両側から洗い出す
SELECT b.title, p.name
FROM books AS b
FULL OUTER JOIN publishers AS p ON p.id = b.publisher_id
WHERE b.id IS NULL OR p.id IS NULL;

4 種類の比較

同じデータに 4 つの JOIN をかけた結果の行数。

選び方はこれだけ。
「消えて困る行はどっちの表にあるか」。片方だけなら LEFT（その表を左に置く）、両方なら FULL、消えていいなら INNER。

ハマりどころ

1. JOIN 条件を WHERE に書くと LEFT が INNER になる

これが一番多い事故。

-- ✕ せっかくの LEFT JOIN が台無し
FROM books AS b
LEFT OUTER JOIN publishers AS p ON p.id = b.publisher_id
WHERE p.name = 'あおい書房';

LEFT JOIN で残した「相手なしの行」は右側が NULL になっている。そこに WHERE p.name = ... をかけると NULL の行は条件を満たせず落ちるので、結果は INNER JOIN と変わらなくなる。

右の表への条件は ON 側に書く。

-- ○ 左の行は残したまま、右の表だけ絞る
FROM books AS b
LEFT OUTER JOIN publishers AS p
  ON p.id = b.publisher_id
 AND p.name = 'あおい書房';

• ON … 照合の条件（左の行は残る）
• WHERE … 結合し終わった表への絞り込み（NULL 行は落ちる）

2. MySQL には FULL OUTER JOIN がない

PostgreSQL は FULL OUTER JOIN をそのまま書けるが、MySQL は未対応。LEFT と RIGHT の結果を UNION で足して代用する。

SELECT b.title, p.name FROM books b LEFT  JOIN publishers p ON p.id = b.publisher_id
UNION
SELECT b.title, p.name FROM books b RIGHT JOIN publishers p ON p.id = b.publisher_id;

3. PostgreSQL で大文字を含む識別子はダブルクォートが要る

PostgreSQL は識別子を自動で小文字に畳む。CREATE TABLE "Books" のように大文字で作ってしまうと、SELECT * FROM Books は books を探しに行って «relation does not exist» になる。

SELECT * FROM "Books";   -- 大文字で作った表はクォートが必須

そもそもテーブル名・カラム名は最初から小文字のスネークケースで作るのが回避策。クォートが必要な名前は一生ついて回る。

4. JOIN すると行が増えることがある

あおい書房 は本を 2 冊持っているので、出版社側から見ると 1 行が 2 行に増えている。件数を数えるときはこれが効いてくる。

-- 本を出している出版社は何社？
SELECT COUNT(DISTINCT p.id)
FROM publishers AS p
INNER JOIN books AS b ON b.publisher_id = p.id;

「1 対多」の多側を JOIN したときだけ DISTINCT を意識する。詳細は JOINとGROUP BYを3人のユーザーで理解する に書いた。

参考

• INNER JOINとOUTER JOIN【初心者向け】 - Qiita
• PostgreSQL: Joined Table
• 関連: JOINとGROUP BYを3人のユーザーで理解する（Rails / ActiveRecord から見た JOIN と GROUP BY）
• 関連: リレーション入門（Prisma でのリレーション定義）