LEFT JOINと外部結合

INNER JOINの落とし穴

crewテーブルの全員と、missionsテーブルをINNER JOINしてみましょう。

SELECT crew.name, missions.title
FROM crew
INNER JOIN missions ON crew.id = missions.crew_id;

結果：

name	title
ユミ	金星大気観測
ケンジ	火星資源調査
サクラ	ケレス採掘権交渉
ハルト	ケレス医療支援
レイ	エウロパ生命探査
ソラ	タイタン氷床探査
リョウ	火星コロニー建設支援

crewテーブルには10人いたはずなのに、結果には7人しか出てきません。ミドリ・トウマ・アカリの3人が消えています。この3人には対応するmissionsの行がないため、INNER JOINは「一致する行がない」と判断して、その行ごと結果から除いてしまうのです。

「全乗組員の稼働状況を一覧したい」ときに、これでは困ります。任務を持たない人ほど目に留めたいのに、INNER JOINだとまさにその人が結果から消えてしまうからです。

LEFT JOIN ― 相手がいなくても残す

そこで使うのがLEFT JOINです。

SELECT crew.name, missions.title
FROM crew
LEFT JOIN missions ON crew.id = missions.crew_id;

結果：

name	title
ユミ	金星大気観測
ケンジ	火星資源調査
サクラ	ケレス採掘権交渉
ハルト	ケレス医療支援
レイ	エウロパ生命探査
ミドリ	NULL
ソラ	タイタン氷床探査
トウマ	NULL
アカリ	NULL
リョウ	火星コロニー建設支援

ミドリ・トウマ・アカリの3人が結果に残り、対応するmissionsの情報がない部分には NULL が入りました。FROM crew と先に書いた側（左側）のテーブルの行は、たとえ相手（missions）に一致する行がなくても、全部残す——これがLEFT JOIN（左外部結合）の意味です。「LEFT」は「FROM句の直後、つまり左に書いたテーブルを主役にする」という意味だと考えると分かりやすいでしょう。

NULLになる列

NULLは「0」でも「空文字」でもなく、「値が存在しない」ことを表す特別な状態です。ミドリの行の title がNULLなのは、「タイトルが空っぽの任務」があるのではなく、そもそも対応する任務の行自体がないことを表しています。

任務を持たない乗組員だけを探したいときは、こう書きます。

SELECT crew.name
FROM crew
LEFT JOIN missions ON crew.id = missions.crew_id
WHERE missions.title IS NULL;

結果：

name
ミドリ
トウマ
アカリ

NULLかどうかを調べるときは = NULL ではなく IS NULL と書くのがルールです。NULLは「値」ではなく「値がない状態」なので、= で比較すること自体ができません。

使いどころ

• INNER JOIN ― 「両方に対応するデータがある行だけ」でよいとき（例：実際に行われた任務と、その担当者・目的地の組み合わせ一覧）
• LEFT JOIN ― 「主役となる表の行は、相手の有無にかかわらず全部見たい」とき（例：全乗組員の任務状況一覧。任務を持たない人も含めて把握したい）

「今のデータで欠けがないか」「まだ何も紐づいていない行はどれか」を調べたいときこそ、LEFT JOINの出番です。

/sql/game/ の SQL DIVER で、INNER JOINとLEFT JOINの結果の違いを、実際にタイプして確かめてみましょう。同じ2つの表でも、JOINの種類ひとつで結果ががらりと変わる感覚を、ぜひ手を動かして体験してください。