mysql - 在 MySQL SELECT 结果中包含 NULL 值

Question

我有以下 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     |
+----------+-------------+

我有以下 SELECT 语句

SELECT DISTINCT owners.name, owners.pet, owners.city,
group_concat(DISTINCT types.type separator ', ') AS type
FROM tbl_pet_owners owners
INNER JOIN tbl_pet_types types ON owners.pet = types.pet
WHERE owners.city = 'Boston' OR owners.city = 'NYC'
GROUP BY owners.name, owners.pet
ORDER BY owners.city

..返回这个结果:

+--------+----------+--------+-------------------+
| name   | pet      | city   | type              |
+--------+----------+--------+-------------------+
| jack   | cat      | Boston | mammal            |
| jane   | cat      | Boston | mammal            |
| jane   | dog      | Boston | mammal            |
| jim    | goldfish | Boston | fish, seacreature |
| jim    | snake    | Boston | reptile           |
| drew   | dog      | NYC    | mammal            |
| joseph | cat      | NYC    | mammal            |
| sam    | cat      | NYC    | mammal            |
+--------+----------+--------+-------------------+

不幸的是，结果中省略了 jack 的 Frog ，因为 tbl_pet_types 中没有 Frog 条目。如何编辑我的查询以在结果中包含 jack's frog(type = NULL)？

Answer 1

您使用的是 INNER JOIN，因此由于两个表都没有匹配项，因此不会显示该行。尝试使用 LEFT JOIN，这样左(第一个)表中的所有值都会显示出来，而不管右(第二个)表中是否有答案。

你可以用这张图片形象化