[PostgreSQL] JOIN の種類

どれを使うか迷うことがあるので備忘録として。
検証で使用したpostgresのversionは12.7

### 前提

以下のようなテーブル定義とデータで検証

```sql
CREATE TABLE companies(
  id bigint primary key, 
  name varchar(50) NOT NULL,
  tel varchar(50)
);

CREATE TABLE employees(
  id bigint primary key, 
  company_id bigint,
  name varchar(50) NOT NULL,
  age integer,
  FOREIGN KEY (company_id) references companies(id)
);

INSERT INTO public.companies(id, name, tel)
VALUES 
    (1, '株式会社ほげ', '00-0000-0000'),
    (2, '株式会社ふが', '00-0000-1111'),
    (3, '株式会社ぴよ', '00-0000-2222');


INSERT INTO public.employees(id, company_id, name, age)
VALUES 
    (1, 1, 'テスト太郎', 25),
    (2, 1, 'テスト次郎', 20),
    (3, 2, 'テスト三郎', 30),
    (4, null, 'テスト五郎', 40);
```


### (INNER) JOIN

内部結合。
結合条件(ON)に一致するレコードだけ表示。

```sql
SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
JOIN companies c ON c.id = e.company_id
```

結果

| employee_id | employee_name | company_id | company_name |
|-------------|---------------|------------|--------------|
|           1 | テスト太郎    |          1 | 株式会社ほげ |
|           2 | テスト次郎    |          1 | 株式会社ほげ |
|           3 | テスト三郎    |          2 | 株式会社ふが |


### LEFT (OUTER) JOIN

左外部結合。
左テーブル(FROMで指定したテーブル)をすべて表示して、
結合条件に一致しない右テーブル(JOINで指定したテーブル)はNULLとして結合される。

```sql
SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
LEFT JOIN companies c ON c.id = e.company_id
```

結果

| employee_id | employee_name | company_id | company_name |
|-------------|---------------|------------|--------------|
|           1 | テスト太郎    |          1 | 株式会社ほげ |
|           2 | テスト次郎    |          1 | 株式会社ほげ |
|           3 | テスト三郎    |          2 | 株式会社ふが |
|           4 | テスト五郎    |            |              |


### RIGHT (OUTER) JOIN

右外部結合。
右テーブル(JOINで指定したテーブル)をすべて表示して、
結合条件に一致しない左テーブル(FROMで指定したテーブル)はNULLとして結合される。

```sql
SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM companies c
RIGHT JOIN employees e ON e.company_id = c.id
```

結果

LEFT JOINと同じ

### FULL (OUTER) JOIN

完全外部結合。
`LEFT JOIN` と `RIGHT JOIN` の結果をあわせたもの。

```sql
SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
FULL JOIN companies c ON c.id = e.company_id
```

結果

| employee_id | employee_name | company_id | company_name |
|-------------|---------------|------------|--------------|
|           1 | テスト太郎    |          1 | 株式会社ほげ |
|           2 | テスト次郎    |          1 | 株式会社ほげ |
|           3 | テスト三郎    |          2 | 株式会社ふが |
|           4 | テスト五郎    |            |              |
|             |               |          3 | 株式会社ぴよ |

### CROSS JOIN

両テーブルのすべてのレコードを取り出して、すべての組み合わせを表示する

```sql
SELECT 
    e.id AS employee_id, 
    e.name AS employee_name, 
    c.id AS company_id, 
    c.name AS company_name
FROM employees e
CROSS JOIN companies c
ORDER BY e.id
```

結果

| employee_id | employee_name | company_id | company_name |
|-------------|---------------|------------|--------------|
|           1 | テスト太郎    |          1 | 株式会社ほげ |
|           1 | テスト太郎    |          2 | 株式会社ふが |
|           1 | テスト太郎    |          3 | 株式会社ぴよ |
|           2 | テスト次郎    |          1 | 株式会社ほげ |
|           2 | テスト次郎    |          2 | 株式会社ふが |
|           2 | テスト次郎    |          3 | 株式会社ぴよ |
| ...         | ...           | ...        | ...          |


### 参照

- [PostgreSQL 12.4文書 7.2. テーブル式](https://www.postgresql.jp/document/12/html/queries-table-expressions.html)
- [「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典](https://wa3.i-3-i.info/word15313.html)