SQL の LEFT OUTER JOIN で NULL 比較の挙動を見る

# 概要

- SQL の LEFT OUTER JOIN で NULL を IS NULL や 比較演算子 <> で比較したときの挙動を確認するためのサンプルを書く
- 環境: MySQL Ver 8.0.29 for macos11.6 on x86_64 (Homebrew)

# サンプルデータを準備

my_users テーブルと my_groups テーブルを作成する。
my_users.group_id と my_groups.id を結合するためのカラムとする。

my_users テーブルを作成してレコードを追加する。

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

INSERT INTO my_users VALUES (1, 'Alice', 'abc');
INSERT INTO my_users VALUES (2, 'Carol', 'xyz');
INSERT INTO my_users VALUES (3, 'Dave',  '');
INSERT INTO my_users VALUES (4, 'Ellen', NULL);
```

my_groups テーブルを作成してレコードを追加する。

```
CREATE TABLE my_groups (id VARCHAR(3), name VARCHAR(16));

INSERT INTO my_groups VALUES ('abc', 'abc group');
INSERT INTO my_groups VALUES ('',    'empty group');
INSERT INTO my_groups VALUES (NULL,  'non group');
```

my_users テーブルのすべてのレコードを確認する。

```
mysql> SELECT * FROM my_users;
+------+-------+----------+
| id   | name  | group_id |
+------+-------+----------+
|    1 | Alice | abc      |
|    2 | Carol | xyz      |
|    3 | Dave  |          |
|    4 | Ellen | NULL     |
+------+-------+----------+
```

my_groups テーブルのすべてのレコードを確認する。

```
mysql> SELECT * FROM my_groups;
+------+-------------+
| id   | name        |
+------+-------------+
| abc  | abc group   |
|      | empty group |
| NULL | non group   |
+------+-------------+
```

# カラム値を NULL や空文字列と比較

## WHERE 条件なし

my_users テーブルのすべてのレコードを確認する。

```
mysql> SELECT * FROM my_users;
+------+-------+----------+
| id   | name  | group_id |
+------+-------+----------+
|    1 | Alice | abc      |
|    2 | Carol | xyz      |
|    3 | Dave  |          |
|    4 | Ellen | NULL     |
+------+-------+----------+
```

## カラム名 IS NULL

my_users テーブルから group_id が NULL のレコードを抽出する。

```
mysql> SELECT * FROM my_users WHERE group_id IS NULL;
+------+-------+----------+
| id   | name  | group_id |
+------+-------+----------+
|    4 | Ellen | NULL     |
+------+-------+----------+
```

## カラム名 IS NOT NULL

my_users テーブルから group_id が NULL ではないレコードを抽出する。

```
mysql> SELECT * FROM my_users WHERE group_id IS NOT NULL;
+------+-------+----------+
| id   | name  | group_id |
+------+-------+----------+
|    1 | Alice | abc      |
|    2 | Carol | xyz      |
|    3 | Dave  |          |
+------+-------+----------+
```

## カラム名 = ''

my_users テーブルから group_id が空文字列のレコードを抽出する。

```
mysql> SELECT * FROM my_users WHERE group_id = '';
+------+------+----------+
| id   | name | group_id |
+------+------+----------+
|    3 | Dave |          |
+------+------+----------+
```

## カラム名 <> ''

my_users テーブルから group_id が空文字列ではないレコードを抽出する (group_id が NULL の場合も抽出されない)。

```
mysql> SELECT * FROM my_users WHERE group_id <> '';
+------+-------+----------+
| id   | name  | group_id |
+------+-------+----------+
|    1 | Alice | abc      |
|    2 | Carol | xyz      |
+------+-------+----------+
```

# LEFT OUTER JOIN (左外部結合)

## WHERE 条件なし

my_users のすべてのレコードに対して、
my_users.group_id と my_groups.id が一致するレコードを、
my_groups から抽出して、
結合する。

```
SELECT
  my_users.id       AS user_id,
  my_users.name     AS user_name,
  my_users.group_id AS group_id_by_my_users,
  my_groups.id      AS group_id,
  my_groups.name    AS group_name
FROM
  my_users
LEFT OUTER JOIN
  my_groups
    ON my_users.group_id = my_groups.id;
```

my_users.group_id と一致する my_groups.id が存在しない場合は、my_groups 由来のカラムに NULL がセットされる。
my_users.group_id が NULL の場合も、my_groups 由来のカラムに NULL がセットされる。

```
+---------+-----------+----------------------+----------+-------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name  |
+---------+-----------+----------------------+----------+-------------+
|       1 | Alice     | abc                  | abc      | abc group   |
|       2 | Carol     | xyz                  | NULL     | NULL        |
|       3 | Dave      |                      |          | empty group |
|       4 | Ellen     | NULL                 | NULL     | NULL        |
+---------+-----------+----------------------+----------+-------------+
```

my_users.group_id が xyz の場合には my_groups.id に一致するものがない。そのため my_groups 由来のカラム (group_id と group_name) に NULL がセットされている。my_users.group_id と my_groups.id が異なるにもかかわらず同じレコードとなっている。

my_users.group_id が NULL の場合は、my_groups.id の NULL とはマッチせず UNKNOWN と評価される。そのため my_groups 由来のカラム (group_id と group_name) に NULL がセットされている。

## カラム名 IS NULL

結合後に my_users.group_id が NULL のレコードを抽出する。

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

```
+---------+-----------+----------------------+----------+------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name |
+---------+-----------+----------------------+----------+------------+
|       4 | Ellen     | NULL                 | NULL     | NULL       |
+---------+-----------+----------------------+----------+------------+
```

結合後に my_groups.id が NULL のレコードを抽出する。

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

```
+---------+-----------+----------------------+----------+------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name |
+---------+-----------+----------------------+----------+------------+
|       2 | Carol     | xyz                  | NULL     | NULL       |
|       4 | Ellen     | NULL                 | NULL     | NULL       |
+---------+-----------+----------------------+----------+------------+
```

## カラム名 IS NOT NULL

結合後に my_users.group_id が NULL ではないレコードを抽出する。

```
SELECT
  my_users.id       AS user_id,
  my_users.name     AS user_name,
  my_users.group_id AS group_id_by_my_users,
  my_groups.id      AS group_id,
  my_groups.name    AS group_name
FROM
  my_users
LEFT OUTER JOIN
  my_groups
    ON my_users.group_id = my_groups.id
WHERE
  my_users.group_id IS NOT NULL;
```

```
+---------+-----------+----------------------+----------+-------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name  |
+---------+-----------+----------------------+----------+-------------+
|       1 | Alice     | abc                  | abc      | abc group   |
|       2 | Carol     | xyz                  | NULL     | NULL        |
|       3 | Dave      |                      |          | empty group |
+---------+-----------+----------------------+----------+-------------+
```

結合後に my_groups.id が NULL ではないレコードを抽出する。

```
SELECT
  my_users.id       AS user_id,
  my_users.name     AS user_name,
  my_users.group_id AS group_id_by_my_users,
  my_groups.id      AS group_id,
  my_groups.name    AS group_name
FROM
  my_users
LEFT OUTER JOIN
  my_groups
    ON my_users.group_id = my_groups.id
WHERE
  my_groups.id IS NOT NULL;
```

```
+---------+-----------+----------------------+----------+-------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name  |
+---------+-----------+----------------------+----------+-------------+
|       1 | Alice     | abc                  | abc      | abc group   |
|       3 | Dave      |                      |          | empty group |
+---------+-----------+----------------------+----------+-------------+
```

## カラム名 <> ''

結合後に my_users.group_id が空文字列ではないレコードを抽出する (my_users.group_id が NULL の場合も抽出されない)。

```
SELECT
  my_users.id       AS user_id,
  my_users.name     AS user_name,
  my_users.group_id AS group_id_by_my_users,
  my_groups.id      AS group_id,
  my_groups.name    AS group_name
FROM
  my_users
LEFT OUTER JOIN
  my_groups
    ON my_users.group_id = my_groups.id
WHERE
  my_users.group_id <> '';
```

```
+---------+-----------+----------------------+----------+------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name |
+---------+-----------+----------------------+----------+------------+
|       1 | Alice     | abc                  | abc      | abc group  |
|       2 | Carol     | xyz                  | NULL     | NULL       |
+---------+-----------+----------------------+----------+------------+
```

結合後に my_groups.id が空文字列ではないレコードを抽出する (my_groups.id が NULL の場合も抽出されない)。

```
SELECT
  my_users.id       AS user_id,
  my_users.name     AS user_name,
  my_users.group_id AS group_id_by_my_users,
  my_groups.id      AS group_id,
  my_groups.name    AS group_name
FROM
  my_users
LEFT OUTER JOIN
  my_groups
    ON my_users.group_id = my_groups.id
WHERE
  my_groups.id <> '';
```

```
+---------+-----------+----------------------+----------+------------+
| user_id | user_name | group_id_by_my_users | group_id | group_name |
+---------+-----------+----------------------+----------+------------+
|       1 | Alice     | abc                  | abc      | abc group  |
+---------+-----------+----------------------+----------+------------+
```

# 参考資料

- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: 13\.2\.10\.2 JOIN 句](https://dev.mysql.com/doc/refman/8.0/ja/join.html)
- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: 12\.4\.2 比較関数と演算子](https://dev.mysql.com/doc/refman/8.0/ja/comparison-operators.html)
- [MySQL :: MySQL 8\.0 リファレンスマニュアル :: 12\.4\.3 論理演算子](https://dev.mysql.com/doc/refman/8.0/ja/logical-operators.html)
- [SQL の LEFT OUTER JOIN のサンプル \- Qiita](https://qiita.com/niwasawa/items/b9c017495abf6020ef93)