「とりあえずLEFT JOIN」から卒業する

SQLのJOINは、書き方自体は簡単なのに選び方を間違えるとデータが重複したり消えたりする厄介な機能です。「迷ったらLEFT JOIN」で乗り切っている人も多いと思いますが、それぞれの違いを理解しておくと、想定外の結果行数に悩まされる時間が減ります。

この記事は、JOIN種類の早見表と結果行数の図解を中心にしたリファレンスです。最後に、複数テーブルJOINの書き方と、既存SQLをJOINタイプごとの図で見返す方法も紹介します。

JOINの基本構文と使い方

JOINは、複数のテーブルを共通の列でつなげて1つの結果にする構文です。基本の形は次のとおりです。

SELECT 列名
FROM テーブルA
INNER JOIN テーブルB ON テーブルA.キー列 = テーブルB.キー列;
  • FROM の次に書くのが左テーブル(起点)、JOIN の後ろに書くのが右テーブルです
  • ON には「どの列同士が対応するか」を書きます(多くは主キーと外部キー)
  • テーブル名が長いときは別名を付けます:FROM users u INNER JOIN orders o ON u.id = o.user_id
  • 両方のテーブルに同じ列名があるときは、users.id のようにテーブル名(別名)を付けて書きます。付けないと曖昧でエラーになります
  • 単に JOIN と書いた場合は INNER JOIN として扱われます

JOINの呼び方:英語名と日本語名

英語名 日本語名 補足
INNER JOIN 内部結合 JOIN だけでも同じ
LEFT JOIN 左外部結合 LEFT OUTER JOIN と同じ(OUTER は省略可)
RIGHT JOIN 右外部結合 RIGHT OUTER JOIN と同じ
FULL JOIN 完全外部結合 FULL OUTER JOIN と同じ
CROSS JOIN 交差結合(クロス結合) 条件なしで全組み合わせ(直積)
SELF JOIN 自己結合 同じテーブルを別名で2回使う

**内部結合は「両方に一致があるものだけ」、外部結合は「片方(または両方)の全件を残す」**というのが基本の違いです。

迷ったときの選び方

  • 両方のテーブルに対応する行だけが欲しい → INNER JOIN
  • 左テーブルは全件残したい(右に無ければNULL) → LEFT JOIN
  • 両方のテーブルの全件が欲しい → FULL JOIN
  • すべての組み合わせを作りたい → CROSS JOIN

JOIN種類の早見表

users(3件: Alice, Bob, Carol)と orders(Aliceが2件、Bobが1件、Carolは0件)を例に、返る行を比較します。

JOIN種類 返る行 AliceとBobの行数 Carol(注文なし)の扱い 注文だけ存在する行
INNER JOIN 両方に一致がある行のみ Alice×2, Bob×1 含まれない 含まれない
LEFT JOIN 左テーブル全件+一致する右 Alice×2, Bob×1 Carolの行が1件(右側はNULL) 含まれない
RIGHT JOIN 右テーブル全件+一致する左 Alice×2, Bob×1 含まれない 存在すれば含まれる(左側はNULL)
FULL JOIN 両テーブルの全件 Alice×2, Bob×1 Carolの行が1件 存在すれば含まれる
CROSS JOIN 全組み合わせ(直積) 3人 × 3件 = 9行 — —

INNER JOINが唯一「絞り込む」JOINで、他はすべて「片方または両方の全件を保証する」方向に働きます。この違いが行数のブレの正体です。

結果行数はなぜ「増える」のか

もっとも事故りやすいのがこれです。「JOINしたら行数が想定より多い」の9割は、1対多の関係を素直にJOINした結果です。

SELECT users.name, orders.id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

users 3件・orders 3件でも、結果は注文テーブルの行数分(Aliceの注文が2件なら、Aliceの行も2行)になります。集計時にここを見落として COUNT(*) をそのまま取ると、ユーザー数ではなく注文数を数えてしまいます。

-- 誤り:ユーザー数のつもりが「ユーザー×注文」の行数になる
SELECT COUNT(*) FROM users INNER JOIN orders ON users.id = orders.user_id;

-- 正しい:重複を除いたユーザー数
SELECT COUNT(DISTINCT users.id) FROM users INNER JOIN orders ON users.id = orders.user_id;

「JOIN前に何件だったか」を常に意識し、JOIN後の行数がその整数倍になっていない場合は、結合条件を疑うのが実務での鉄則です。

LEFT JOINで一番踏む罠:WHERE句がJOINを台無しにする

「注文がないユーザーも含めたい」からLEFT JOINにしたのに、WHERE で条件を書いた瞬間にINNER JOINと同じ結果に戻ってしまう——これが2番目によくある事故です。

-- 罠:一見LEFT JOINだが、WHEREでorders側を絞るとNULL行が消える
SELECT users.name, orders.status
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';   -- Carol(NULL)はここで弾かれる

Carolは注文が無いので orders.status は NULL です。WHERE orders.status = 'completed' は NULL = 'completed' を評価しますが、**SQLではNULLとの比較は常にNULL(true でも false でもない)**として扱われ、その行は結果から除外されます。結果として、実質的にINNER JOINと同じ挙動になります。

対処法は、条件を ON 句に書くことです。

-- 正しい:ON句に条件を含めればLEFT JOINの意味が保たれる
SELECT users.name, orders.status
FROM users
LEFT JOIN orders ON users.id = orders.user_id AND orders.status = 'completed';
-- Carolの行は残り、orders側は全部NULLになる

**「左側の全件を保証したいなら、右側への絞り込みはON句、左側への絞り込みはWHERE句」**と覚えておくと迷いません。

複数テーブルのJOIN:起点を1つに固定する

3テーブル以上のJOINで迷ったら、「どのテーブルを主語にするか」を先に決めるのがコツです。

SELECT
  users.name,
  orders.id       AS order_id,
  order_items.quantity,
  products.name   AS product_name
FROM users
LEFT JOIN orders      ON users.id = orders.user_id
LEFT JOIN order_items ON orders.id = order_items.order_id
LEFT JOIN products    ON order_items.product_id = products.id
WHERE users.deleted_at IS NULL;

FROM users を起点に、そこから「注文へ」「注文明細へ」「商品へ」とLEFT JOINを連鎖させています。すべて users を主語にしているので、「注文が無いユーザー」「注文はあるが明細行が異常なデータ」もすべて洗い出せます(途中でINNER JOINを混ぜると、そこで絞り込みが発生する点に注意)。

SELF JOINは「別名」で2つのテーブルに見せかける

同じテーブル同士を結合する SELF JOIN は、エイリアス(別名)で2つの異なるテーブルであるかのように扱うのがポイントです。典型例は「社員とその上司」のような自己参照構造です。

SELECT
  emp.name  AS employee_name,
  mgr.name  AS manager_name
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

employees に emp と mgr という別名を与えることで、SQL上は「2つのテーブルのJOIN」として書けます。上司がいない(manager_id が NULL)社員も含めたいので、ここでもLEFT JOINが自然な選択です。

カンマ区切り・USING・NATURAL JOIN

古いコードでは、JOINを使わずテーブルをカンマで並べ、WHERE で結合条件を書く形を見かけます。

-- 古い書き方(INNER JOINと同じ結果)
SELECT users.name, orders.id
FROM users, orders
WHERE users.id = orders.user_id;

結果は INNER JOIN と同じですが、WHERE の条件を書き忘れると全組み合わせ(直積)になるため、新しく書くなら JOIN ... ON を使うのが安全です。

結合する列名が両テーブルで同じなら、ON の代わりに USING が使えます。

SELECT * FROM users INNER JOIN orders USING (user_id);

USING は MySQL・PostgreSQL・Oracle・SQLite で使えますが、SQL Server では使えません。NATURAL JOIN(同名の列をすべて自動で結合)は、列を追加しただけで結果が変わるため、実務では避けるのが無難です。

データベースごとの違い

データベース FULL JOIN 補足
MySQL 非対応 UNION で LEFT JOIN と RIGHT JOIN を合成して代用する
PostgreSQL 対応
SQL Server 対応 USING は使えないので ON を書く
SQLite 3.39.0 以降は対応 それ以前は RIGHT JOIN / FULL JOIN が使えない
Oracle 対応 ANSI の JOIN 構文は9i以降。古い (+) 記法のコードも残っている

実際に使うバージョンの対応状況は、各データベースのドキュメントで確認してください。

JOINを組み立てる・整形する

複数テーブルのJOINをゼロから書くのが面倒なら、Visual SQL Builder でテーブルとJOIN条件をフォームから組み立てられます。JOIN種類(JOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN)を切り替えながら生成結果を見比べられるので、この記事の早見表と合わせて挙動を確認するのに向いています。

既存の複雑なJOINクエリを読み解きたいときは、SQLフォーマッター で整形すると、どのJOINがどのテーブルに掛かっているかが見やすくなります。どちらもブラウザ内で完結し、テーブル名やスキーマ情報が外部に送信されることはありません。

まとめ

  • INNER JOINだけが「絞り込む」方向。LEFT/RIGHT/FULLは片方または両方の全件を保証する
  • 1対多のJOINは行が増える。集計前に COUNT(DISTINCT ...) を検討する
  • LEFT JOINで右側を絞りたいなら ON句、左側を絞りたいなら WHERE句。WHERE句に右側の条件を書くとLEFT JOINが実質INNER JOINになる
  • 複数テーブルは主語となるテーブルを固定し、そこからLEFT JOINを連鎖させると崩れにくい
  • SELF JOINはエイリアスで同じテーブルを2つに見せかける

よくある質問

内部結合と外部結合の違いは何ですか?

内部結合(INNER JOIN)は、両方のテーブルに一致する行だけを返します。外部結合(LEFT JOIN・RIGHT JOIN・FULL JOIN)は、片方(または両方)のテーブルの全件を残し、一致しない側はNULLで埋めます。「注文のないユーザーも一覧に出したい」ときは外部結合、「注文があるユーザーだけ」なら内部結合です。

LEFT JOIN と LEFT OUTER JOIN は違いますか?

同じです。OUTER は省略できるので、LEFT JOIN と書くのが一般的です。RIGHT JOIN と FULL JOIN も同様です。

JOINとカンマ区切り(WHEREで結合)はどちらがよいですか?

結果は同じですが、新しく書くなら JOIN ... ON をおすすめします。結合条件(ON)と絞り込み条件(WHERE)を分けて書けるので読みやすく、条件の書き忘れによる直積も防げます。また、外部結合はカンマ区切りでは標準の書き方ができません。

INNER JOINとJOINは違うものですか?

同じです。多くのRDBMSで JOIN は INNER JOIN の省略形として扱われます。ただし、コードレビューでの明確さのために INNER JOIN と明示的に書く運用も一般的です。

RIGHT JOINはあまり使われないと聞きましたが本当ですか?

A RIGHT JOIN B は B LEFT JOIN A とテーブルの順序を入れ替えれば同じ結果になるため、「常にLEFT JOINで統一し、RIGHT JOINは使わない」というコーディング規約のチームは多いです。ただし、既存のクエリを読む機会はあるので、意味を理解しておく価値はあります。

FULL JOINが使えないデータベースがあると聞きました

MySQLは長らく FULL JOIN を直接サポートしていません(UNION で LEFT JOIN と RIGHT JOIN を合成して代用します)。PostgreSQLやSQL Serverでは標準でサポートされています。使用するデータベースの対応状況を事前に確認してください。

JOINの結合条件を貼り付けて確認したいのですが、データは送信されますか?

いいえ。Visual SQL Builder と SQLフォーマッター はどちらもブラウザ内で完結し、入力したテーブル名やクエリがサーバーに送信されることはありません。