我有以下 MySQL 表:
tbl_pet_owners:
+----+--------+----------+--------+--------------+
| id | name | pet | city | date_adopted |
+----+--------+----------+--------+--------------+
| 1 | jane | cat | Boston | 2017-07-11 |
| 2 | jane | dog | Boston | 2017-07-11 |
| 3 | jane | cat | Boston | 2017-06-11 |
| 4 | jack | cat | Boston | 2016-07-11 |
| 5 | jim | snake | Boston | 2017-07-11 |
| 6 | jim | goldfish | Boston | 2017-07-11 |
| 7 | joseph | cat | NYC | 2016-07-11 |
| 8 | sam | cat | NYC | 2017-07-11 |
| 9 | drew | dog | NYC | 2016-07-11 |
| 10 | jack | frog | Boston | 2017-07-19 |
+----+--------+----------+--------+--------------+
tbl_pet_types:
+----------+-------------+
| pet | type |
+----------+-------------+
| cat | mammal |
| dog | mammal |
| goldfish | fish |
| goldfish | seacreature |
| snake | reptile |
+----------+-------------+
我有以下 SQL 查询,用于搜索宠物类型列表,以及这些宠物类型的所有者的姓名和城市:
SELECT DISTINCT types.type, owners.name, owners.city
FROM tbl_pet_owners owners
LEFT JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE types.type IN ('mammal', 'fish', 'amphibian', 'seacreature');
查询返回:
+-------------+--------+--------+
| type | name | city |
+-------------+--------+--------+
| mammal | jane | Boston |
| mammal | jack | Boston |
| fish | jim | Boston |
| seacreature | jim | Boston |
| mammal | joseph | NYC |
| mammal | sam | NYC |
| mammal | drew | NYC |
+-------------+--------+--------+
结果中省略了 Amphibian,因为在 tbl_pet_types 中未找到它。如何更改我的查询以便该行:
amphibian, NULL, NULL
包含在结果中吗?