### 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でやりたい