SQLite LEFT JOIN入門：左テーブルを残す結合の使い方

LEFT JOIN は左側を全部残す

INNER JOIN は両方のテーブルでマッチする行だけを返します。これで十分なケースも多いのですが、いつもそうとは限りません。「マッチしない」ことそのものが知りたい答えになる場面もあるからです。たとえば、まだ注文していないユーザー、一度も売れていない商品、コメントがゼロの投稿といったケース。こういうときに使うのが LEFT JOIN です。

LEFT JOIN は左側のテーブルの行をすべて返します。右側のテーブルに対応する行があればその列の値が入りますし、なければ左側の行はそのまま残り、右側の列には NULL が入ります。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleoは注文を一件も持っていませんが、それでも結果には登場します。total 列が NULL になっているだけです。試しに LEFT JOIN を INNER JOIN に書き換えてみてください。Cleoはきれいに消えてしまいます。

イメージで理解する LEFT JOIN

クエリを上から下へ読み下して、左側のテーブルを「軸」と捉えてください。users の各行は何があっても必ず出力に残ります。そのうえで LEFT JOIN は、ユーザーごとに「orders の中に対応する行はある?」と問い合わせていくイメージです。

• 一致あり → マッチした列をユーザー行に連結する。
• 複数一致 → マッチごとに1行ずつ出力する(Adaは注文を2件持っているので2回登場します)。
• 一致なし → 右側のテーブル由来の列をすべて NULL にして1行返す。

この「一致なし」のケースこそ、LEFT JOIN が存在する理由そのものです。ここでの NULL は「値が不明」という意味ではなく、「右側に連結すべきものが存在しない」という意味になります。

なお LEFT OUTER JOIN は LEFT JOIN とまったく同じ動作です。SQLiteでは OUTER キーワードは省略可能で、実際ほとんどの人は省略して書きます。

一致しない行だけを抽出する

LEFT JOIN の定番の使いどころが、「左側のテーブルにあって、右側に一致する行が ない 行」を見つけるパターンです。コツは、実データでは必ず NOT NULL になる右側テーブルの列(たいていは主キー)を選び、結合後にそれが NULL かどうかをチェックすることです:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

取得できるのは Cleo だけです。JOIN は注文データがあれば結合しますが、その後の WHERE o.id IS NULL によって、結合に失敗した行だけが残ります。これは「アンチジョイン（anti-join）」と呼ばれることもあります。

ON と WHERE の違い：意外とハマる落とし穴

これは LEFT JOIN で一番よくあるバグなので、ここでしっかり押さえておきましょう。条件は ON 句にも WHERE 句にも書けますが、外部結合の場合、両者の挙動はまったく違います。

• ON は結合処理の 最中 に評価されます。ここに書いた条件は、右側のどの行を「一致」とみなすかを決めます。
• WHERE は結合が終わって行が出揃った あと に評価されます。つまり、結合済みの結果を絞り込むフィルターです。

試しに、右側のテーブルに対する条件を WHERE に書くとどうなるか見てみましょう。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- 間違い: LEFT JOIN が暗黙的に INNER JOIN になってしまう
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo は注文を持っていないため、彼女の行では o.status が NULL になります。NULL = 'shipped' は真にならないので、Cleo は弾かれてしまいます。Boris のステータスは 'pending' なので、こちらも除外。結果として、LEFT JOIN がしれっと INNER JOIN のように振る舞ってしまうわけです。

解決策は、条件を ON 句に移すこと。こうすれば「出力される行」ではなく「マッチング対象」を絞り込めます。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

これで全ユーザーが結果に出てきました。Adaは出荷済みの注文が表示され、BorisはNULL（保留中の注文はマッチ条件を満たさなかったため）、CleoもNULL（そもそも注文なし）になります。「全ユーザーを出して、もし出荷済みの注文があればそれも見せて」という問いに対しては、これが正しい答えです。

覚えておきたい使い分け: 左側テーブルの条件はWHEREに書いてOK。一方、右側テーブルの条件はほぼONに書くべきです。例外は、IS NULLを使ってマッチしなかった行をあえて探したいときくらいです。

LEFT JOINで件数を数える

よくある用途として、親テーブルごとに紐づく行数を数える、しかも0件の親も漏らさず出したい、というケースがあります。INNER JOINだと0件の親は消えてしまいますが、LEFT JOINと右側カラムのCOUNTを組み合わせれば狙いどおりの結果になります。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

ここで押さえておきたいポイントが2つあります。

• COUNT(o.id) は右側テーブルの非NULL行をカウントします。だから Cleo は 1 ではなく 0 になります — COUNT は NULL を無視するからです。もし COUNT(*) と書いていたら、Cleo は 1 になります(行自体は存在していて、中身が全部 NULL なだけなので)。基本的には COUNT(right.id) の方が欲しい結果になるはずです。
• COALESCE(SUM(o.total), 0) は Cleo の NULL の合計を 0 に変換してくれます。これがないと売上が NULL と表示されてしまい、間違いではないけれど見た目がイマイチです。

3つ以上のテーブルを結合する

LEFT JOIN は連結できます。各 JOIN はそれまでの結果を受け取り、そこに別のテーブルを結合していくイメージです。一度 LEFT JOIN でカラムが NULL を取りうる状態になったら、そこにぶら下がるテーブルも LEFT JOIN で繋ぎ続けてください — そうしないと、次の INNER JOIN のところで、せっかく残したかった行がしれっと消えてしまいます。

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

戻ってきた結果には、3 人のユーザーが並んでいます。Ada は注文も配送もあり。Boris は注文はあるけれど配送はまだなので carrier が NULL。Cleo に至っては注文すらないので、o.total も s.carrier も両方 NULL です。LEFT JOIN をチェーンさせれば、関係の連鎖がどこで途切れても、すべてのユーザーがちゃんと残ります。

LEFT JOIN を使うべき場面

「主役は左側のテーブルで、右側のテーブルは付随情報にすぎない」——そんな問いには LEFT JOIN がぴったりです。「全ユーザーと、もしあればその注文も」「全商品と、最新レビューがあればそれも」といった言い回しは、そのまま LEFT JOIN に対応します。

一方、両方のテーブルが対等に必須なときは INNER JOIN の出番です。「注文とその注文者の情報」と言うとき、ユーザーがいない注文には意味がないので、内部結合のフィルタリング動作こそが欲しい挙動になります。

ここで left join と inner join の違いを判断するコツをひとつ。もし LEFT JOIN ... WHERE right.col IS NOT NULL と書いている自分に気づいたら、本当は INNER JOIN が欲しかったのです。逆に LEFT JOIN ... WHERE right.col IS NULL を書いているなら、それはアンチジョイン(該当しない行だけを取り出すパターン)で、書き方は正解です。

次回:自己結合(Self-Join)

ときには、結合したい相手のテーブルが、いま問い合わせているテーブルそのものということもあります。社員と上司、カテゴリと親カテゴリ、同じ街に住むユーザー同士のペア、といったケースです。これを自己結合(self-join)と呼びます。次のページで取り上げましょう。