SQL の LEFT OUTER JOIN のサンプル

## LEFT OUTER JOIN とは

SQL 文で左側に記述されるテーブルのレコードを抽出し、それらのレコードと条件一致する右側テーブルのレコードを抽出して、結合して出力する。
左側テーブルはすべてのレコードを抽出する。条件一致するレコードが右側テーブルにない場合は、右側レコードのカラム値として NULL 値がセットされる。

## 動作確認環境

- MySQL Ver 8.0.26 for macos11.3 on x86_64 (Homebrew)

## サンプルデータの準備

グループ情報 (複数のユーザーが属する) を管理する my_groups テーブルを作成してデータを追加する

```
CREATE TABLE my_groups (id INT, name VARCHAR(10));

INSERT INTO my_groups VALUES (   1, '1st group');
INSERT INTO my_groups VALUES (   2, '2nd group');
INSERT INTO my_groups VALUES (   3, '3rd group');
INSERT INTO my_groups VALUES ( 888, '888 group');
INSERT INTO my_groups VALUES (NULL, 'non-group');
```

ユーザー情報 (1つ以下のグループに属する) を管理する my_users テーブルを作成してデータを追加する。

```
CREATE TABLE my_users (id INT, name VARCHAR(10), group_id INT);

INSERT INTO my_users VALUES (1, 'Alice',    1);
INSERT INTO my_users VALUES (2, 'Bob',      2); -- Bob と Carol は同じグループ (group_id = 2)
INSERT INTO my_users VALUES (3, 'Carol',    2); -- Bob と Carol は同じグループ (group_id = 2)
INSERT INTO my_users VALUES (4, 'Dave',   999); -- 存在しないグループを指定
INSERT INTO my_users VALUES (5, 'Ellen', NULL); -- グループを指定しない (group_id = NULL)
```

## サンプルデータの確認

```
SELECT * FROM my_groups;
```

```
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 1st group |
|    2 | 2nd group |
|    3 | 3rd group |
|  888 | 888 group |
| NULL | non-group |
+------+-----------+
```

```
SELECT * FROM my_users;
```

```
+------+-------+----------+
| id   | name  | group_id |
+------+-------+----------+
|    1 | Alice |        1 |
|    2 | Bob   |        2 |
|    3 | Carol |        2 |
|    4 | Dave  |      999 |
|    5 | Ellen |     NULL |
+------+-------+----------+
```

## my_users テーブルをベースにして my_groups テーブルを結合する例

```
SELECT
  my_users.id,
  my_users.name,
  group_id,
  my_groups.id   AS group_id_by_groups,
  my_groups.name AS group_name
FROM my_users
LEFT OUTER JOIN my_groups
  ON my_users.group_id = my_groups.id
ORDER BY my_users.id;
```

ユーザーごとのレコードが出力される。
Bob と Carol は同じグループに属するのでグループ情報のカラム値が同じ。
Dave の group_id = 999 は my_groups テーブルに存在しないため、グループ情報のカラム値が NULL になる。
Ellen の group_id は NULL のため、NULL 同士の比較はできずに、グループ情報のカラム値が NULL になる。

```
+------+-------+----------+--------------------+------------+
| id   | name  | group_id | group_id_by_groups | group_name |
+------+-------+----------+--------------------+------------+
|    1 | Alice |        1 |                  1 | 1st group  |
|    2 | Bob   |        2 |                  2 | 2nd group  |
|    3 | Carol |        2 |                  2 | 2nd group  |
|    4 | Dave  |      999 |               NULL | NULL       |
|    5 | Ellen |     NULL |               NULL | NULL       |
+------+-------+----------+--------------------+------------+
```

## my_groups テーブルをベースにして my_users テーブルを結合する例

```
SELECT
  my_groups.id   AS group_id,
  my_groups.name AS group_name,
  my_users.id    AS user_id,
  my_users.name  AS user_name
FROM my_groups
LEFT OUTER JOIN my_users
  ON my_groups.id = my_users.group_id
ORDER BY group_id IS NULL ASC, user_id ASC;
```

グループを基準にして、グループに紐付くユーザーを結合して出力される。
group_id = 1 に紐付くユーザーは Alice のみなので1レコード出力される。
group_id = 2 に紐付くユーザーは Bob と Carol なので2レコード出力される。
group_id = 3 に紐付くユーザーは存在しないため、ユーザー情報のカラム値が NULL になる。
group_id = 888 に紐付くユーザーは存在しないため、ユーザー情報のカラム値が NULL になる。
group_id = NULL は、NULL 同士の比較はできないことからどのユーザーも紐付くことができず、ユーザー情報のカラム値が NULL になる。

```
+----------+------------+---------+-----------+
| group_id | group_name | user_id | user_name |
+----------+------------+---------+-----------+
|        1 | 1st group  |       1 | Alice     |
|        2 | 2nd group  |       2 | Bob       |
|        2 | 2nd group  |       3 | Carol     |
|        3 | 3rd group  |    NULL | NULL      |
|      888 | 888 group  |    NULL | NULL      |
|     NULL | non-group  |    NULL | NULL      |
+----------+------------+---------+-----------+
```

## 参考資料

- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: 13\.2\.10\.2 JOIN 句](https://dev.mysql.com/doc/refman/8.0/ja/join.html)
- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: 8\.2\.1\.9 外部結合の最適化](https://dev.mysql.com/doc/refman/8.0/ja/outer-join-optimization.html)
- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: 8\.2\.1\.10 外部結合の単純化](https://dev.mysql.com/doc/refman/8.0/ja/outer-join-simplification.html)
- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: B\.3\.4\.3 NULL 値に関する問題](https://dev.mysql.com/doc/refman/8.0/ja/problems-with-null.html)
- [PostgreSQL 12.4文書 7\.2\. テーブル式](https://www.postgresql.jp/document/12/html/queries-table-expressions.html)