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 句
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.2 比較関数と演算子
• MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.3 論理演算子
• SQL の LEFT OUTER JOIN のサンプル - Qiita