初めに
テーブルを結合する方法をまとめました
前提
・PostgreSQL 13で検証
・以下のテーブルで検証
CREATE TABLE tb1 (no integer, name1 text);
CREATE TABLE tb2 (no integer, name2 text);
CREATE TABLE tbu1 (no integer, name text);
CREATE TABLE tbu2 (no integer, name text);
INSERT INTO tb1 (no, name1) VALUES(1,'a'),(2,'b1'),(2,'b2'),(3,'c');
INSERT INTO tb2 (no, name2) VALUES(1,'a1'),(1,'a2'),(2,'b'),(4,'d');
INSERT INTO tbu1 (no, name) VALUES(1,'a'),(2,'b'),(2,'b1'),(3,'c');
INSERT INTO tbu2 (no, name) VALUES(1,'a'),(1,'a1'),(2,'b'),(4,'d');
内部結合
結合条件に指定している値が両方のテーブルに存在するデータを抽出する
WHERE句
WHERE条件で結合する
SELECT * FROM tb1,tb2 WHERE tb1.no = tb2.no
| no | name1 | no | name2 |
|---|---|---|---|
| 1 | a | 1 | a1 |
| 1 | a | 1 | a2 |
| 2 | b1 | 2 | b |
| 2 | b2 | 2 | b |
上記の例では表示列を*にしているため、noの列が2つ表示されている。
INNNER JOIN
各テーブルの結合列を指定する
SELECT * FROM tb1 INNER JOIN tb2 ON (tb1.no = tb2.no)
| no | name1 | no | name2 |
|---|---|---|---|
| 1 | a | 1 | a1 |
| 1 | a | 1 | a2 |
| 2 | b1 | 2 | b |
| 2 | b2 | 2 | b |
WHERE句のケースと同じ結果です
USING
結合列をUSINGで指定する
SELECT * FROM tb1 JOIN tb2 USING (no)
| no | name1 | name2 |
|---|---|---|
| 1 | a | a1 |
| 1 | a | a2 |
| 2 | b1 | b |
| 2 | b2 | b |
no列は重複表示されない
NATURAL JOIN
SELECT * FROM tb1 NATURAL JOIN tb2
| no | name1 | name2 |
|---|---|---|
| 1 | a | a1 |
| 1 | a | a2 |
| 2 | b1 | b |
| 2 | b2 | b |
USING句と同様、no列は重複表示されない
外部結合
基準となるテーブルに存在すれば抽出する結合
左外部結合 left join
左側のテーブルを基準にする
SELECT * FROM tb1 LEFT OUTER JOIN tb2 ON (tb1.no = tb2.no)
| no | name1 | no_1 | name2 |
|---|---|---|---|
| 1 | a | 1 | a1 |
| 1 | a | 1 | a2 |
| 2 | b1 | 2 | b |
| 2 | b2 | 2 | b |
| 3 | c | « NULL » | « NULL » |
右外部結合 right join
右側のテーブルを基準にする
SELECT * FROM tb1 RIGHT OUTER JOIN tb2 ON (tb1.no = tb2.no)
| no | name1 | no_1 | name2 |
|---|---|---|---|
| 1 | a | 1 | a1 |
| 1 | a | 1 | a2 |
| 2 | b1 | 2 | b |
| 2 | b2 | 2 | b |
| « NULL » | « NULL » | 4 | d |
完全外部結合 full join
左外部結合と右外部結合を合わせた結果
SELECT * FROM tb1 FULL OUTER JOIN tb2 ON (tb1.no = tb2.no)
| no | name1 | no_1 | name2 |
|---|---|---|---|
| 1 | a | 1 | a1 |
| 1 | a | 1 | a2 |
| 2 | b1 | 2 | b |
| 2 | b2 | 2 | b |
| 3 | c | « NULL » | « NULL » |
| « NULL » | « NULL » | 4 | d |
クロス結合 cross join
全てのレコードの組み合わせを表示する
full joinと違い結合条件の指定がないため、全レコードの組み合わせになる
SELECT * FROM tb1 CROSS JOIN tb2
| no | name1 | no_1 | name2 |
|---|---|---|---|
| 1 | a | 1 | a1 |
| 1 | a | 1 | a2 |
| 1 | a | 2 | b |
| 1 | a | 4 | d |
| 2 | b1 | 1 | a1 |
| 2 | b1 | 1 | a2 |
| 2 | b1 | 2 | b |
| 2 | b1 | 4 | d |
| 2 | b2 | 1 | a1 |
| 2 | b2 | 1 | a2 |
| 2 | b2 | 2 | b |
| 2 | b2 | 4 | d |
| 3 | c | 1 | a1 |
| 3 | c | 1 | a2 |
| 3 | c | 2 | b |
| 3 | c | 4 | d |
| 1 | a | 1 | a1 |
その他
結合じゃないけど、よく忘れたり混乱する演算
和演算 UNION
複数テーブルを一つのselect文でまとめて表示する
同一レコードは1件で表示する
SELECT * FROM tbu1 UNION SELECT * FROM tbu2
| no | name |
|---|---|
| 1 | a1 |
| 1 | a |
| 2 | b1 |
| 2 | b |
| 3 | c |
| 4 | d |
UNION ALL
ALLを指定すると、重複レコードも表示する
SELECT * FROM tbu1 UNION ALL SELECT * FROM tbu2
| no | name |
|---|---|
| 1 | a |
| 1 | a |
| 1 | a1 |
| 2 | b |
| 2 | b1 |
| 2 | b |
| 3 | c |
| 4 | d |
INTERSECT（積集合）
共通するレコードのみ抽出する
SELECT * FROM tbu1 EXCEPT SELECT * FROM tbu2
| no | name |
|---|---|
| 1 | a |
| 2 | b |
EXCEPT(差集合)
1つ目の検索結果を元にして、2つ目の検索結果に存在するデータを差し引く
SELECT * FROM tbu1 EXCEPT SELECT * FROM tbu2
| no | name |
|---|---|
| 2 | b1 |
| 3 | c |
仮想表
一時的な仮想表。
fromは不要
select 1 as id, 'a' as name
with
with句でselect文に別名を付けるケース
with test as ( select 1 as id, 'a' as name ) select * from test