PostgreSQL SQL結合方法まとめ

## 初めに
 テーブルを結合する方法をまとめました

## 前提
・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');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1308027/7e8b2399-8608-5734-9763-499f4a29b210.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1308027/57ba77b5-17b9-56ea-7656-75f0519031f5.png)


## 内部結合
結合条件に指定している値が両方のテーブルに存在するデータを抽出する

### 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
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1308027/13ece9eb-a8f9-99fb-28ff-bfa02d3d7708.png)

### with
with句でselect文に別名を付けるケース

```
with test as ( select 1 as id, 'a' as name ) select * from test
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1308027/13ece9eb-a8f9-99fb-28ff-bfa02d3d7708.png)