SQLite INNER JOIN入門：複数テーブルの結合を解説

JOIN は2つのテーブルをつなぎ合わせる

リレーショナルデータベースでは、データをわざとテーブルごとに分けて持ちます。顧客はcustomersテーブル、注文はordersテーブル、商品はproductsテーブル、といった具合です。こうすることで、同じ事実を一箇所にまとめておけます。とはいえ、現実の問い(「どの顧客が何を注文したのか?」)に答えようとすると、バラバラのピースをもう一度つなぎ直す必要が出てきます。これをやってくれるのが JOIN です。

中でも INNER JOIN は一番よく使う定番で、SQLite の inner join の基本でもあります。指定した条件に一致する行同士を2つのテーブルから組み合わせて、それ以外はバッサリ落とす — そんな動きをします。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

顧客は3人、注文は3件。でも Chen には注文がないので、結果には登場しません。これが「inner（内側）」の意味で、マッチした行だけが残るというわけです。

イメージで理解する：行をマッチさせてから絞り込む

INNER JOIN はこう読み解いてください。1つ目のテーブルの全行に対して、2つ目のテーブルの全行を突き合わせ、ON の条件が真になるペアだけを残す——という流れです。考え方としては、巨大な直積（クロス積）を作ってからフィルタをかけているイメージですね。実際の SQLite はそんな非効率なことはせず、可能ならインデックスを使って処理しますが、結果を予想するうえではこのモデルで十分通用します。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

ここで身につけておきたい習慣をいくつか挙げておきます。

• 同じテーブルを何度も参照するときは、エイリアスを付ける（customers AS c のように）。記述がすっきりします。
• 両方のテーブルにありそうなカラムは、c.name、o.total のようにテーブル名で修飾する。
• ON o.customer_id = c.id の左右の順序は問わない。c.id = o.customer_id と書いても同じ結果になります。

INNER JOIN と JOIN の違い

SQLite（および標準 SQL）では、JOIN だけ書くと INNER JOIN と同じ意味になります。INNER キーワードは省略可能です。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

どちらの書き方でも、実行計画も結果の行も同じになります。INNER JOIN と明示的に書くメリットは、コードの読みやすさがほんの少し上がることくらいです。とはいえ、数行下に LEFT JOIN が出てくるようなクエリでは、結合の種類がひと目でわかるので地味に効いてきます。

ON と USING の違い

両方のテーブルで結合カラムの名前が同じなら、ON a.col = b.col と書くより USING (column) のほうが短くスッキリ書けます。

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) のポイントは2つあります。customer_id が一致する行をマッチさせること、そして結合キーの列を1つにまとめて結果に1回だけ表示することです。両方のテーブルで本当に同じ列名を使っているときに使うとスッキリ書けます。列名が違う場合（orders.customer_id = customers.id のように）や、単純な等価条件以外を書きたい場合は、素直に ON を使いましょう。

SQLiteで3つのテーブルを結合する

JOIN ... ON ... を続けて書けば、いくつでもテーブルをつなげられます。それぞれの JOIN が、ここまでの結合結果に対して次のテーブルを紐付けるイメージです。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'ノート', 2), (10, 'ペン', 5), (11, 'マグカップ', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

上から順に読んでみてください。customers が orders とつながり、orders が items とつながっています。出力される 1 行は、顧客・注文・商品の組み合わせ 1 セットを表します。途中のどこかで対応するレコードがなければ、その行は結果から除外されます。これが、各ステップで適用される内部結合（INNER JOIN）のルールです。

WHERE で絞り込む

ON は行同士をどう紐付けるかを指定し、WHERE は紐付けた後の結果を絞り込みます。INNER JOIN に限って言えば、追加の条件を ON に書いても WHERE に書いても、得られる行は同じです。ただし慣習として、結合条件は ON に、行のフィルタリングは WHERE に書き分けるのが一般的です。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

これを言葉にすると「customers と orders を結合して、UK の顧客かつ注文が 20 を超えるものだけ残す」となります。役割が 2 つ、句も 2 つ ―― あとで自分のコードを読み返したときに、この書き分けに感謝するはずです。（LEFT JOIN を書き始めると ON と WHERE の違いは見た目だけの問題ではなくなりますが、その話は次のページで。）

ON に複数条件を書く

ON には等式 1 つに限らず、任意のブール式を書けます。テーブル同士の関係が複数カラムにまたがるときや、結合の段階で右側のテーブルを絞り込みたいときに便利です。

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

キャンセルされた注文が消えるのは、2つ目の条件にマッチしないからです。INNER JOIN の場合は WHERE o.status = 'paid' と書いても同じ結果になります。ただ ON に書いておけば、「何をもって一致とするか」のロジックを JOIN のすぐそばにまとめられるのが利点です。

よくあるハマりどころ

INNER JOIN を書くときに引っかかりやすいポイントをいくつか挙げておきます。

• ON の書き忘れ。 SQLite では FROM a INNER JOIN b を ON なしで書くと構文エラーになります(なお FROM a, b のようにカンマだけでつなぐ書き方はコンパイル自体は通りますが、結果はクロス結合になり、まず意図した動作ではありません)。
• 想定外の重複行。 ある顧客に注文が3件あれば、結果にはその顧客名が3回出てきます。これはバグではなく JOIN の正しい挙動です。顧客ごとに1行にまとめたいなら GROUP BY で集約しましょう。
• 行が足りない。 出てくるはずの顧客が消えている場合は、JOIN の条件にマッチしていないということです。結合キーに NULL が混じっていないか確認するか、LEFT JOIN への切り替えを検討してください。
• 列名が曖昧になる。 SELECT id FROM customers JOIN orders ON ... はエラーになります。両方のテーブルに id があるためです。c.id や o.id のようにテーブル名で修飾しましょう。

次は LEFT JOIN へ

INNER JOIN は「マッチしない行は捨ててOK」というケースでは非常に便利です。でも、注文がない顧客も含めてすべての顧客を一覧したい、足りない部分は NULL でいい、という場面もありますよね。そんなときに使うのが LEFT JOIN です。次の記事で見ていきましょう。