PostgresのJOINについて備忘録的まとめ

JOINとは

テーブル同士を結合させるときに使用するSQL句

どんなときに結合する？

→　2つ以上のテーブルのレコードをまとめて参照したいとき

JOIN句を使用して、結合したい列を指定する
その指定した列の値を比較して、同じ値のデータを結合することができる

JOINには
・CROSS JOIN
・(INNER) JOIN
・LEFT (OUTER) JOIN
・RIGHT (OUTER) JOIN
・FULL OUTER JOIN
が存在する

見本テーブル

動作環境：PostgreSQL 9.3.2

CREATE TABLE example1(
  id integer primary key, 
  name text NOT NULL,
  age integer
);

CREATE TABLE example2(
  id integer primary key, 
  user_id integer,
  hobby text,
  favorite_food text,
  FOREIGN KEY (user_id) references example1(id)
);

INSERT INTO example1(id, name, age)
VALUES 
    (1, 'ほげ太郎', 31),
    (2, 'ふが次郎', 25),
    (3, 'ぽよ美', 27);


INSERT INTO example2(id, user_id, hobby, favorite_food)
VALUES 
    (1, 1, '散歩', 'りんご'),
    (2, 2, '手芸', '秋刀魚'),
    (3, null, 'サッカー', 'ラーメン'),
    (4, null, '映画鑑賞', '寿司');

example1

example2

CROSS JOIN

考えられる全ての組み合わせを作成する結合方式
他のJOIN句と違い、ONで結合する際に対応させるカラムを指定しないので、
無造作にテーブルを結合するような感じ
実行結果のレコード数は結合したテーブルのレコード数をかけた数になる
この場合、（example1のレコード数）×（example2のレコード数）
あまり実用性はない気がする

SQL

SELECT * FROM example1 CROSS JOIN example2;

実行結果

＊ベン図は省略

(INNER) JOIN

テーブル間の指定した関連性のあるカラムやレコードを軸にして結合する方式
これを内部結合という
見本では、example1のidとexample2のuser_idが関連するカラムなので、
これを軸に内部結合を行って、example1のレコードを取得する
対応するカラムを指定する際はONを使用する

SQL

SELECT * FROM example1 JOIN example2 ON example2.user_id = example1.id;

*INNERは省略可

実行結果

ベン図

LEFT (OUTER) JOIN

JOIN句で指定したテーブルに対応するレコードがない場合でも、FROM句で指定したテーブルのレコードを全て取得する方式
見本では、example1のid 3に対応するレコードはexample2に存在しないが、カラムにnullが入って抽出されている

SQL

SELECT * FROM example1 LEFT JOIN example2 ON example2.user_id = example1.id;

*OUTERは省略可

実行結果

ベン図

RIGHT (OUTER) JOIN

LEFT JOINの反対で、FROM句に対応するレコードがない場合でも、JOIN句で指定したテーブルのレコードを全て取得する方式

あまり使用しない気がする、軸にするテーブルを反対にしてLEFT JOINすればいいのではと考えてしまう
（ただ無知なだけなので、意義あれば教えていただきたいです）

SQL

SELECT * FROM example1 RIGHT JOIN example2 ON example2.user_id = example1.id;

*OUTERは省略可

実行結果

ベン図

FULL OUTER JOIN

結合したレコードと、対応しないレコード全てを取得する方式
これとLEFT JOIN、RIGHT JOINの内部結合+αを外部結合と呼ぶ
これもあまり使用しない気がする
postgresでは使用できるが、他のデータベースシステムによっては使用できない

SQL

SELECT * FROM example1 FULL OUTER JOIN example2 ON example2.user_id = example1.id;

実行結果

ベン図

まとめ

INNER JOINとLEFT JOIN以外はあまり使わない気がする
(なにか間違っているところあればコメント頂けるとありがたいです)