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 |
+----------+------------+---------+-----------+