JOINとINNER JOINは同じですか？

PostgreSQLではJOINのみの指定はINNER JOINです。省略しても結合条件に一致する組み合わせだけを返します。

JOINは3テーブル以上でも使えますか？

使えます。JOINを続けて書きます。結合を1つ追加するごとに対応件数と結果の粒度を確認すると、想定外の行数増加を発見しやすくなります。

ONとUSINGの違いは？

USINGは両側で同じ名前の列を等値結合し、その結合列を出力上1列にまとめます。ONは列名が違う場合や不等号などの条件にも対応します。

SQLのINNER JOINとは？LEFT JOINとの違い・ONとWHERE・行が増える理由

INNER JOINは、2つのテーブルで条件が一致した行を組み合わせる構文です。顧客名と注文額のように、別々に保存した情報を同じ行へ並べられます。結合キーだけでなく、対応する行がない場合や複数ある場合に、結果がどう変わるかを押さえましょう。

INNER JOINの基本構文

sql

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

INNER JOINとON：主キー・外部キーを対応させる

orders.customer_idをcustomers.idに対応させると、注文に顧客名を付けられます。oとcはテーブルの別名です。両方にidがあるため、o.idのように所属を明示します。外部キーは整合性を守る制約であり、JOINそのものは外部キー制約がなくても書けます。

sql

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

1対多のJOINで行が増える理由

1人の顧客に注文が2件あれば、その顧客名は2行に現れます。JOINは一致した組み合わせをすべて返すためで、重複とは限りません。顧客を1行にしたいなら集約やEXISTSを検討します。DISTINCTを足す前に、結果の1行を「顧客」にするか「注文」にするか決めましょう。

sql

SELECT c.id, c.name, o.amount
FROM customers c
INNER JOIN orders o
  ON o.customer_id = c.id
ORDER BY c.id, o.id;

INNER JOINとLEFT JOINの違い：未一致行を残す

INNER JOINは一致する注文のある顧客だけ、LEFT JOINは注文のない顧客も残します。未一致の右側の列はNULLです。左側に何を置くかが重要で、全顧客を残すならcustomersをFROMに置きます。LEFT JOINでも注文が複数あれば複数行になります。

sql

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

LEFT JOINのONとWHERE：条件の位置で結果が変わる

全顧客を残したままpaid注文だけ結びたい場合、statusの条件をONに置きます。WHERE o.status = 'paid'に置くと、右側がNULLの未注文顧客は除外されます。INNER JOINでは同じになる条件でも、LEFT JOINでは同じとは限りません。

sql

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

-- こちらは未注文顧客を除外する
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.status = 'paid';

LEFT JOINとIS NULLで注文のない顧客を探す

結合後に右側の主キーがNULLの行を選ぶと、対応する注文のない顧客を探せます。判定には元テーブルでNOT NULLと保証された列を使います。注文の備考などNULLを許す列を判定すると、注文がある顧客まで混ざるおそれがあります。

sql

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.id IS NULL;

自己結合：社員と上司を同じテーブルから取り出す

同じテーブルを役割別に2回参照することを自己結合と呼びます。特別なSELF JOINキーワードはなく、通常のJOINに別名を付けます。eは社員、mは上司です。上司のいない社員も含めるにはLEFT JOINを使います。

sql

SELECT e.id, e.name, m.name AS manager
FROM staff_directory e
LEFT JOIN staff_directory m
  ON m.id = e.manager_id;

Tips — 上級Tips：2つの明細テーブルを直接結ぶと集計が膨らむ

ある商品に在庫行が2件、出荷行が3件あると、両方を商品へ直接JOINした中間結果は6行です。在庫や出荷のSUMも水増しされます。それぞれを商品別に集約してから結べば、商品ごとに最大1行同士になり、二重計上を防げます。SUM(DISTINCT quantity)は偶然同じ数量の正当な行まで除くため、代替になりません。

sql

SELECT p.id, st.total_stock, sh.total_shipped
FROM products p
LEFT JOIN (
  SELECT product_id, SUM(quantity) AS total_stock
  FROM stock
  GROUP BY product_id
) st ON st.product_id = p.id
LEFT JOIN (
  SELECT product_id, SUM(quantity) AS total_shipped
  FROM shipments
  GROUP BY product_id
) sh ON sh.product_id = p.id;

INNER JOINでよくある間違い

LEFT JOIN後のCOUNT(*)で未注文顧客を1件と数える

LEFT JOINは一致しなくても左側の行を残すので、COUNT(*)では1になります。注文数にはCOUNT(o.id)を使うとNULLの補完行を除外できます。SUMも未一致ではNULLなので、0表示が必要ならCOALESCEで補います。

sql

SELECT c.id, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

実際に1問解いてみる

INNER JOIN・ON

注文に対応する顧客を結び、注文id、顧客名、注文額をこの順で取得してください。行順は問いません。

⌘ / Ctrl + Enter

テーブル

読み込み中…

実行結果

ヒント

orders.customer_idとcustomers.idが対応します。

解説・解答例

各注文は顧客1人と対応するので、注文7件に顧客名を付けた7行になります。

sql

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

INNER JOINの本格演習へ →

INNER JOINについてよくある質問

JOINとINNER JOINは同じですか？: PostgreSQLではJOINのみの指定はINNER JOINです。省略しても結合条件に一致する組み合わせだけを返します。
JOINは3テーブル以上でも使えますか？: 使えます。JOINを続けて書きます。結合を1つ追加するごとに対応件数と結果の粒度を確認すると、想定外の行数増加を発見しやすくなります。
ONとUSINGの違いは？: USINGは両側で同じ名前の列を等値結合し、その結合列を出力上1列にまとめます。ONは列名が違う場合や不等号などの条件にも対応します。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。

PostgreSQL公式：結合テーブル — 内部・外部結合、ON・USING、組み合わせの生成
PostgreSQL公式：テーブルの結合 — 自己結合と外部結合
PostgreSQL公式：集約関数 — COUNTとSUMのNULL処理