テーブル結合 ② — OUTER JOIN・CROSS JOIN・自己結合

OUTER JOIN — 片側にしかない行も残す

前回の INNER JOIN は 両テーブルで一致した行だけ を返したため、所属部署のない社員や社員のいない部署は結果から消えていました。「全社員を出したい。部署が無い人は部署名を空欄でよい」という要件では、片側の行を 必ず残す OUTER JOIN（外部結合） を使います。

OUTER JOIN には残す側に応じて 3 種類あります。左テーブルを全部残す LEFT JOIN、右テーブルを全部残す RIGHT JOIN、両方を全部残す FULL OUTER JOIN です。相手が見つからなかった側の列は NULL で埋められます。本講座のコンソールは SQLite を採用しており、LEFT / RIGHT / FULL のすべて をそのまま実行できます。題材は前回と同じ department（6 部署）と employee（社員 30 名）です。

LEFT JOIN — 左テーブルを必ず残す

SELECT 列 FROM 左 LEFT JOIN 右 ON 条件 と書くと、左テーブルの行はすべて残り、右テーブルに一致する行がなければ右側の列は NULL になります。LEFT JOIN は LEFT OUTER JOIN の略で、両方とも同じ動作です。「社員を全員出し、部署が無い人は部署名を空欄にする」のように、主役となる側を漏らさず出す ときに使います。

-- 全社員を残す。部署がなければ dept_name は NULL
SELECT e.name, e.city, d.dept_name
FROM employee e
LEFT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

「どの部署にも所属していない社員を洗い出し、employee 側と department 側の dept_id を並べて、なぜ部署名が引けないのかを確認したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① employee（別名 e）を左、department（別名 d）を右にして LEFT JOIN し、d.dept_id が NULL の行だけに絞ってください。

② e.name・e.city に加えて、`e.dept_id`（employee 側）と `d.dept_id`・`d.dept_name`（department 側） を取り出してください。e.dept_id 自体が NULL（部署が未割り当て）であるために結合相手が見つからず、d.dept_id・d.dept_name も NULL になることを目で確認します。e.name の昇順で並べます。

③ 続けて、同じ結合を INNER JOIN に変えると 1 行も返らない ことを確認してください（結合キーが NULL の行は INNER JOIN では消えるため）。

RIGHT JOIN と FULL OUTER JOIN

RIGHT JOIN は LEFT JOIN の左右を入れ替えたもので、右テーブルの行をすべて残します。employee e RIGHT JOIN department d ON ... と書くと、社員が 1 人もいない Legal 部署も結果に残り、employee 側の列が NULL になります。「全部署を出し、社員がいない部署も空欄で表示する」用途に向きます。

FULL OUTER JOIN は 両テーブルの行をすべて残します。一致した行は横につなぎ、左にしかない行（部署のない 4 名）は右側を NULL に、右にしかない行（Legal 部署）は左側を NULL にして、すべてを 1 つの結果に並べます。

-- RIGHT JOIN: 全部署を残す。Legal は employee 側が NULL
SELECT d.dept_name, e.name
FROM employee e
RIGHT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

-- FULL OUTER JOIN: 部署のない社員も社員のいない部署も残す
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

「社員が 1 人も配属されていない部署を、部署側の列と社員側の列を並べて確認したい」という要件を想定します。

① employee（別名 e）を左、department（別名 d）を右にして RIGHT JOIN し、e.emp_id が NULL の行だけに絞ってください。

② d.dept_id・d.dept_name・d.location（department 側）と、`e.emp_id`・`e.name`（employee 側） を取り出してください。d.dept_id には部署番号の 値が入っているのに、e.emp_id・e.name が NULL（空欄）になること（部署は実在するが所属社員が 0 名であること）を確認します。d.dept_name の昇順で並べます。

「社員と部署を 1 枚の表に突き合わせ、両側の dept_id を並べて、片側だけの行がどう NULL になるかを確認したい」という要件を想定します。

① employee（別名 e）と department（別名 d）を FULL OUTER JOIN してください。結合条件は dept_id の一致です。

② e.emp_id・e.name・`e.dept_id`（社員側） と `d.dept_id`・`d.dept_name`（部署側） を取り出してください。マッチした行は両側に値が入りますが、部署が未割り当ての社員は `e.dept_id` も `d.dept_id` も NULL、社員のいない部署（Legal）は `d.dept_id` に値が入り `e` 側が NULL になります。「片側だけの行」がどちらのパターンかを目で確認します。

③ d.dept_id の昇順で並べてください（d.dept_id が NULL の行が先頭側にまとまります）。

CROSS JOIN — すべての組み合わせを作る

CROSS JOIN は結合条件を持たず、左テーブルの各行と右テーブルの各行のすべての組み合わせ を作ります。結果の行数は 左の行数 × 右の行数（直積）になります。employee（30 行）と department（6 行）なら 30 × 6 = 180 行です。

実務では「全社員 × 全月」「全店舗 × 全商品」のように 存在しうる組み合わせをすべて列挙 して、そこに実績を結合し、実績ゼロのマスを 0 で埋める集計の土台として使います。条件を書かない結合なので、ON は付けません。

-- 全社員 x 全部署の組み合わせ数を数える
SELECT count(*) AS combo_count
FROM employee e
CROSS JOIN department d;

-- 組み合わせの一部を確認 (Sales 部署とのペアだけ)
SELECT e.name, d.dept_name
FROM employee e
CROSS JOIN department d
WHERE d.dept_name = 'Sales'
ORDER BY e.emp_id;

「全社員と全部署の取りうる組み合わせが何通りあるかを数えたい」という要件を想定します。

① employee（別名 e）と department（別名 d）を CROSS JOIN してください（結合条件は書きません）。

② 組み合わせの総数を count(*) で数え、結果列の別名を combo_count にしてください。

自己結合 — 同じテーブルを 2 回使って上司名を引く

employee テーブルには manager_id（上司の社員番号）列があり、上司も同じ employee テーブルの社員です。「社員名と、その上司の名前を並べて表示したい」場合は、同じテーブルを 2 回 JOIN します。これを 自己結合（self join） と呼びます。

書き方は FROM employee e JOIN employee m ON e.manager_id = m.emp_id です。e を「社員側」、m を「上司側」と 別名で役割を分けて 同じテーブルを 2 回登場させます。自己結合では別名が必須で、別名がないとどちらの employee か区別できません。INNER JOIN だと上司のいない社員が消えるので、全社員を出すなら LEFT JOIN を使います。

-- 各社員に上司名を併記 (上司のいる社員のみ: INNER)
SELECT e.name AS employee, m.name AS manager
FROM employee e
JOIN employee m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

「全社員の一覧に、それぞれの上司の名前を併記したい。上司のいない社員は上司名を空欄でよい」という要件を想定します。

① employee を別名 e（社員側）として、もう一度 employee を別名 m（上司側）として LEFT JOIN してください。結合条件は e.manager_id = m.emp_id です。

② e.name を employee、m.name を manager という別名で 2 列取り出してください。

③ e.emp_id の昇順で並べてください。

OUTER JOIN の NULL は WHERE の置き場所で意味が変わる

LEFT JOIN の後で WHERE 右テーブル.列 = 値 のように右側の列に条件を書くと、NULL は比較で必ず偽になるため 相手のいない行が落ち、実質 INNER JOIN と同じ結果 になってしまいます。「相手のいない行」を残したまま絞り込みたいときは、その条件を ON 側に書くか、WHERE 右テーブル.キー IS NULL で「相手がいない行」を明示的に拾います。NULL の判定は = NULL ではなく必ず IS NULL / IS NOT NULL を使います。