JOIN は、条件で一致させた 2 つのテーブルから行を作ります: SELECT ... FROM a JOIN b ON a.id = b.a_id。JOIN (INNER JOIN) は一致した行だけを残し、LEFT JOIN は最初のテーブルのすべての行を、FULL JOIN は両方のテーブルのすべての行を残します。
3 行が返ります。Grace には注文がなく、注文 13 は存在しない顧客のものなので、どちらも除かれます。以下の JOIN も同じ 2 つのテーブルを使います。
JOIN の種類
| JOIN | 返る行 |
|---|---|
INNER JOIN | 条件に一致する組 |
LEFT JOIN | 左のすべての行。一致しない場合、右の列は NULL |
RIGHT JOIN | 右のすべての行。LEFT JOIN の左右逆 |
FULL JOIN | 両側のすべての行。欠けた部分は NULL |
CROSS JOIN | 左のすべての行と右のすべての行の組み合わせ |
LEFT JOIN: 一致しない行も残す
Grace は注文 0 件で表示されます。* ではなく o.id を数えてください。count(*) だと、Grace の NULL だけの行を 1 件と数えてしまいます。
- 一致しない行 (アンチ結合):
WHERE o.id IS NULLを加えると、注文のない顧客だけが残ります。WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)でも同じ結果になり、意図がはっきり伝わります。 - ON と WHERE: 右のテーブルに対する条件は
ONに書きます。LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20では、Grace のNULLの total がWHEREを満たさないので Grace が消えます。JOIN が内部結合になってしまっています。すべての顧客を残すにはON o.customer_id = c.id AND o.total > 20と書きます。
FULL JOIN: どちらかの側に欠けている行
FULL JOIN は両方向の不一致を見つけるので、2 つのリストを比べるときに便利です。
結果には、注文のない Grace と、顧客のいない注文 13 が含まれます。orders.customer_id に 外部キー があれば、2 行目は防げていました。
CROSS JOIN: すべての組み合わせ
CROSS JOIN には条件がありません。片側のすべての行を、もう片側のすべての行と組み合わせるので、3 つのサイズと 2 つの色から 6 行ができます。
ここでの 2 つのリストは、テーブルのように名前を付けた VALUES リストです。実際のテーブルなら FROM sizes CROSS JOIN colors と書き、FROM sizes, colors でも同じです。ON の条件が常に真になる JOIN も同じように行を掛け合わせます。クエリが急に多すぎる行を返すよくある原因です。
LATERAL: 行ごとのサブクエリ
FROM の中のサブクエリは、普通は同じ FROM のほかのテーブルを参照できません。LATERAL を付けると参照でき、前にあるテーブルの行ごとに 1 回評価されます。これで「グループごとの上位 N 件」が解けます。
Ada には金額の大きい 2 件の注文、Linus には 1 件が付き、Grace には LEFT JOIN ... ON true によって NULL の行が付きます。CROSS JOIN LATERAL にすると、注文のない顧客は消えます。orders (customer_id, total) にインデックスがあれば、各サブクエリが読むインデックスのエントリはわずかです。グループごとに 1 行だけなら、DISTINCT ON のほうが短く書けることが多いです。
よくある質問
PostgreSQL の JOIN と INNER JOIN は同じですか?
同じです。JOIN だけなら INNER JOIN、LEFT JOIN は LEFT OUTER JOIN を意味します。INNER と OUTER のキーワードは省略できます。
LEFT JOIN の結果が左のテーブルより多くなるのはなぜですか?
左の行は、一致する右の行の数だけ現れます。注文が 3 件ある顧客は 3 回現れます。GROUP BY で集約するか、キーごとにすでに 1 行になっているサブクエリと結合してください。
ON と USING の違いは何ですか?
ON には任意の条件を書けます。USING (id) は、両方のテーブルにある同じ名前の列が等しいという条件の省略形で、出力の id 列は 2 つではなく 1 つになります。
PostgreSQL では JOIN の順番が性能に影響しますか?
たいていは影響しません。クエリのテーブル数が join_collapse_limit (デフォルトは 8) 以下なら、プランナーが内部結合の順番を入れ替えてコストの低い計画を探します。外部結合は、動かすと結果が変わることがあるので、入れ替えられるのは一部の場合だけです。