テスト環境 #
postgresの公式dockerイメージを利用。
🔗関連記事: Dockerを利用したPostgreSQLの練習環境の構築、基本的なコマンドのメモ
テスト用のテーブル定義 #
CREATE TABLE IF NOT EXISTS test_person1(
id bigserial PRIMARY KEY,
killed_by bigint REFERENCES test_person1(id),
name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS test_person2(
id bigserial PRIMARY KEY,
test_person1_id bigint NOT NULL REFERENCES test_person1(id),
name TEXT NOT NULL
);
テスト用のデータ挿入 #
INSERT INTO test_person1(killed_by,name) VALUES
(1,'Mr.Kys'),
(NULL,'ジョバンニ'),
(NULL,'KBTIT'),
(3,'マヒロ');
INSERT INTO test_person2(test_person1_id,name) VALUES
(1,'Mr.1'),
(4,'Mr.2'),
(3,'Mr.3'),
(3,'Mr.4');
テスト用のDBの最終状態 #
postgres=# select * from test_person1;
id | killed_by | name
----+-----------+------------
1 | 1 | Mr.Kys
2 | | ジョバンニ
3 | | KBTIT
4 | 3 | マヒロ
(4 rows)
postgres=# select * from test_person2;
id | test_person1_id | name
----+-----------------+------
1 | 1 | Mr.1
2 | 4 | Mr.2
3 | 3 | Mr.3
4 | 3 | Mr.4
(4 rows)
joinを感じる #
cross join #
postgres=# select * from test_person1 cross join test_person2;
id | killed_by | name | id | test_person1_id | name
----+-----------+------------+----+-----------------+------
1 | 1 | Mr.Kys | 1 | 1 | Mr.1
1 | 1 | Mr.Kys | 2 | 4 | Mr.2
1 | 1 | Mr.Kys | 3 | 3 | Mr.3
1 | 1 | Mr.Kys | 4 | 3 | Mr.4
2 | | ジョバンニ | 1 | 1 | Mr.1
2 | | ジョバンニ | 2 | 4 | Mr.2
2 | | ジョバンニ | 3 | 3 | Mr.3
2 | | ジョバンニ | 4 | 3 | Mr.4
3 | | KBTIT | 1 | 1 | Mr.1
3 | | KBTIT | 2 | 4 | Mr.2
3 | | KBTIT | 3 | 3 | Mr.3
3 | | KBTIT | 4 | 3 | Mr.4
4 | 3 | マヒロ | 1 | 1 | Mr.1
4 | 3 | マヒロ | 2 | 4 | Mr.2
4 | 3 | マヒロ | 3 | 3 | Mr.3
4 | 3 | マヒロ | 4 | 3 | Mr.4
(16 rows)
cross joinは2つのテーブルから考えられるペアが全て返される。
所謂cartesian product。
inner join #
inner joinの構文はselect * from A inner join B on ...;
on ...の部分には条件を渡す必要がある。
crossjoinの条件付き版と考えて問題ない。
select * from test_person1 inner join test_person2 on true;とすると、cross joinと全く同じ結果が返ってくる。
例えば、
select * from test_person1 inner join test_person2 on test_person1.id = test_person2.test_person1_id;
とすると
id | killed_by | name | id | test_person1_id | name
----+-----------+--------+----+-----------------+------
1 | 1 | Mr.Kys | 1 | 1 | Mr.1
4 | 3 | マヒロ | 2 | 4 | Mr.2
3 | | KBTIT | 3 | 3 | Mr.3
3 | | KBTIT | 4 | 3 | Mr.4
(4 rows)
のようにcross joinの結果のうちのtest_person1.id = test_person2.test_person1_idを満たすものだけが返ってくる。
同テーブルとのjoin #
joinに指定する2つのテーブルは同一のテーブルでも問題ない。
例えば
select * from test_person1 as tp1 inner join test_person1 as tp2 on true;
とすると
id | killed_by | name | id | killed_by | name
----+-----------+------------+----+-----------+------------
1 | 1 | Mr.Kys | 1 | 1 | Mr.Kys
1 | 1 | Mr.Kys | 2 | | ジョバンニ
1 | 1 | Mr.Kys | 3 | | KBTIT
1 | 1 | Mr.Kys | 4 | 3 | マヒロ
2 | | ジョバンニ | 1 | 1 | Mr.Kys
2 | | ジョバンニ | 2 | | ジョバンニ
2 | | ジョバンニ | 3 | | KBTIT
2 | | ジョバンニ | 4 | 3 | マヒロ
3 | | KBTIT | 1 | 1 | Mr.Kys
3 | | KBTIT | 2 | | ジョバンニ
3 | | KBTIT | 3 | | KBTIT
3 | | KBTIT | 4 | 3 | マヒロ
4 | 3 | マヒロ | 1 | 1 | Mr.Kys
4 | 3 | マヒロ | 2 | | ジョバンニ
4 | 3 | マヒロ | 3 | | KBTIT
4 | 3 | マヒロ | 4 | 3 | マヒロ
(16 rows)
のようになる。
実践的な使い方のひとつを示しておく。
test_person1のkilled_byカラムは、値が存在すれば、idがkilled_byの人間に殺されたことを示すカラムだ。
殺された人間の一覧を取得する場合はselect * from test_person1 where killed_by is not null;のようなクエリで簡単に取得することができる。
では、誰かを殺した人間の一覧はどのように取得できるだろうか？
select * from test_person1 where id in (select killed_by from test_person1 where killed_by is not null);
(もしくはFROM内でサブクエリを使うとか)
のようにサブクエリでうまくやる方法もあるが、joinで取得することもできる。
postgres=# select tp1.id, tp1.name from test_person1 as tp1 inner join test_person1 as tp2 on tp1.id = tp2.killed_by ;
id | name
----+--------
1 | Mr.Kys
3 | KBTIT
(2 rows)
ひとつのテーブル内の行の間にカラムによって何らかの関係があるとき、自己joinが役立つのかもしれない。
left join #
select * from test_person1 left join test_person2 on true;
と実行するとcross join、条件がon trueのinner joinと全く同じ結果が返ってくる。
inner joinとの違いはonの条件にマッチしなかった場合、inner joinの方ではその組み合わせは結果から排除されるが、left joinの場合はonでマッチしなくてもNULLで埋められて結果が返ってくる。
文の説明より実例を見たほうがわかりやすいだろう。
select * from test_person1 left join test_person2 on test_person1.id = test_person2.test_person1_id;
id | killed_by | name | id | test_person1_id | name
----+-----------+------------+----+-----------------+------
1 | 1 | Mr.Kys | 1 | 1 | Mr.1
4 | 3 | マヒロ | 2 | 4 | Mr.2
3 | | KBTIT | 3 | 3 | Mr.3
3 | | KBTIT | 4 | 3 | Mr.4
2 | | ジョバンニ | | |
(5 rows)
inner joinのほうの結果では4行しか返ってこなかったが、left joinに変えると5行目が追加されている。
5行目はonで対応するtest_person2の行が存在しないため、カラムの右側のほうはNULLで埋められている。
onの条件がマッチしようがしまいが、最低一回は左側の行が結果に出現するということだ。
先程の自己joinの項のクエリをleft joinに変更すると以下のようになる。
postgres=# select * from test_person1 as tp1 left join test_person1 as tp2 on tp1.id = tp2.killed_by ;
id | killed_by | name | id | killed_by | name
----+-----------+------------+----+-----------+--------
1 | 1 | Mr.Kys | 1 | 1 | Mr.Kys
3 | | KBTIT | 4 | 3 | マヒロ
2 | | ジョバンニ | | |
4 | 3 | マヒロ | | |
(4 rows)
と誰も殺していないジョバンニ、マヒロが左側に出現していることが分かる。
右側がtp2つまり、殺された人間のカラム群なのだが、ジョバンニ、マヒロは誰も殺していないのでその右側はNULLになっている。
right join #
left joinでは左側で指定したテーブルの行がすべて結果に含まれていた。
right joinではそれが右側のテーブルになるだけ。
joinの結果とのjoin #
select * from table1
inner join table2 on ...
left join table3 on ...;
のようにjoin繋げることもできる。
この場合、table1とtable2のinner joinの結果とtable3のleft joinがselectに渡される最終的な出力になる。
まとめ #
- cross join: 2つのテーブルの行のすべての組み合わせが返ってくる。
- inner join: cross joinの条件付きバージョン。
onで指定した条件がマッチしない組み合わせは結果に含まれない。 - left join: 基本はinner joinと同じ。 しかし、左側のテーブルの行はすべて最低一回は結果に出現する。
onで指定した条件がマッチしない場合は、NULLで埋められたものが返される。 - rigth join: left joinでは左テーブルの行が全て必ず含まれるが、right joinではそれが右側のテーブルになる。
結局自分で試すのが一番早い。
手っ取りばやいの
はやいの漢字は早いらしい。
🔗https://dictionary.goo.ne.jp/word/%E6%97%A9%E3%81%84/
難しい。