【PostgreSQL】JOINの使い方とイロハ

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

JOINには
– cross join
– inner join
– left join
– right join
– full outer join
がある。

postgres=# CREATE TABLE example1 (
        id integer primary key,
        name text NOT NULL,
        age integer
);
CREATE TABLE                              ^
postgres=# CREATE TABLE example2 (
        id integer primary key,
        user_id integer,
        hobby text,
        favorite_food text,
        FOREIGN KEY (user_id) references example1(id)
);
CREATE TABLE
postgres=# INSERT INTO example1(id, name, age)
VALUES
        (1, '山田太郎', 31),
        (2, '田中一郎', 25),
        (3, '小林幸子', 27);
INSERT 0 3
postgres=# INSERT INTO example2(id, user_id, hobby, favorite_food)
VALUES
        (1, 1, '散歩', 'りんご'),
        (2, 2, '手芸', '秋刀魚'),
        (3, null, 'サッカー', 'ラーメン'),
        (4, null, '映画鑑賞', '寿司');
INSERT 0 4

### CROSS JOIN
考えうる全ての組み合わせを作成
SELECT * FROM example1 CROSS JOIN example2;

### (INNER) JOIN
指定したレコードを軸に結合(一番イメージが近い)
postgres=# SELECT * FROM example1 JOIN example2 ON example2.user_id = example1.id;
id | name | age | id | user_id | hobby | favorite_food
—-+———-+—–+—-+———+——-+—————
1 | 山田太郎 | 31 | 1 | 1 | 散歩 | りんご
2 | 田中一郎 | 25 | 2 | 2 | 手芸 | 秋刀魚
(2 rows)

### LEFT JOIN
joinで指定したテーブルに対応するレコードがない場合でも全て取得
postgres=# SELECT * FROM example1 LEFT JOIN example2 ON example2.user_id = example1.id;
id | name | age | id | user_id | hobby | favorite_food
—-+———-+—–+—-+———+——-+—————
1 | 山田太郎 | 31 | 1 | 1 | 散歩 | りんご
2 | 田中一郎 | 25 | 2 | 2 | 手芸 | 秋刀魚
3 | 小林幸子 | 27 | | | |
(3 rows)

### RIGHT JOIN
left joinの反対
postgres=# SELECT * FROM example1 RIGHT JOIN example2 ON example2.user_id = example1.id;
id | name | age | id | user_id | hobby | favorite_food
—-+———-+—–+—-+———+———-+—————
1 | 山田太郎 | 31 | 1 | 1 | 散歩 | りんご
2 | 田中一郎 | 25 | 2 | 2 | 手芸 | 秋刀魚
| | | 3 | | サッカー | ラーメン
| | | 4 | | 映画鑑賞 | 寿司

### FULL OUTER JOIN
left joinとright joinを組み合わせたようなもの
postgres=# SELECT * FROM example1 FULL OUTER JOIN example2 ON example2.user_id = example1.id;
id | name | age | id | user_id | hobby | favorite_food
—-+———-+—–+—-+———+———-+—————
1 | 山田太郎 | 31 | 1 | 1 | 散歩 | りんご
2 | 田中一郎 | 25 | 2 | 2 | 手芸 | 秋刀魚
| | | 3 | | サッカー | ラーメン
| | | 4 | | 映画鑑賞 | 寿司
3 | 小林幸子 | 27 | | | |
(5 rows)

なるほど、、、1対1で対応してないとjoinはselectできないね。

更にデータを追加します。
INSERT INTO example2(id, user_id, hobby, favorite_food)
VALUES
(5, 1, ‘買い物’, ‘マンゴー’),
(6, 2, ‘旅行’, ‘パスタ’);

postgres=# SELECT * FROM example1 JOIN example2 ON example2.user_id = example1.id;
id | name | age | id | user_id | hobby | favorite_food
—-+———-+—–+—-+———+——–+—————
1 | 山田太郎 | 31 | 1 | 1 | 散歩 | りんご
2 | 田中一郎 | 25 | 2 | 2 | 手芸 | 秋刀魚
1 | 山田太郎 | 31 | 5 | 1 | 買い物 | マンゴー
2 | 田中一郎 | 25 | 6 | 2 | 旅行 | パスタ
(4 rows)

joinでwhereをつけられる
postgres=# SELECT * FROM example1 JOIN example2 ON example2.user_id = example1.id WHERE example1.id = 1;
id | name | age | id | user_id | hobby | favorite_food
—-+———-+—–+—-+———+——–+—————
1 | 山田太郎 | 31 | 1 | 1 | 散歩 | りんご
1 | 山田太郎 | 31 | 5 | 1 | 買い物 | マンゴー

なるほど、雰囲気は大体わかりました。
これをtransactionとblockでやりたい