在MySQL查询结果中包含未找到的值 [英] Including values NOT FOUND in MySQL query results

问题描述

我有以下MySQL表:

I have the following MySQL tables:

tbl_pet_owners:

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:

tbl_pet_types:

+----------+-------------+
| pet      | type        |
+----------+-------------+
| cat      | mammal      |
| dog      | mammal      |
| goldfish | fish        |
| goldfish | seacreature |
| snake    | reptile     |
+----------+-------------+

我有以下SQL查询，用于搜索宠物类型列表以及具有这些宠物类型的所有者的姓名和城市:

I have the following SQL query that searches for a list of pet types, along with the name and city of the owner with those pet types:

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

两栖动物从结果中省略，因为在tbl_pet_types中找不到.如何更改查询以使该行:

Amphibian is omitted from the results because it is not found in tbl_pet_types. How can I change my query so that the row:

amphibian, NULL, NULL

包括在结果中吗?

推荐答案

left join是正确的，但是您必须将types放在第一位:

A left join is correct, but you need for the types to be first:

SELECT DISTINCT types.type, owners.name, owners.city
FROM tbl_pet_types types LEFT JOIN
     tbl_pet_owners owners
     ON owners.pet = types.pet
WHERE types.type IN ('mammal', 'fish', 'amphibian', 'seacreature');

因为WHERE子句仅引用tbl_pet_types，所以它不会更改.

Because the WHERE clause refers only to tbl_pet_types, it does not change.

left join的工作方式很简单:它将所有行保留在 first 表中.第二个中不匹配的列变为NULL.

How left join works is simple: It keeps all rows in the first table. Unmatched columns in the second become NULL.

如果您有tbl_pet_types以外的类型列表，则需要一个left join，其所有值都在派生表中:

If you have a list of types that are not in tbl_pet_types, then you need a left join with all the values in a derived table:

SELECT DISTINCT tt.type, po.name, po.city
FROM (SELECT 'mammal' as type UNION ALL
      SELECT 'fish' as type UNION ALL
      SELECT 'amphibian' as type UNION ALL
      SELECT 'seacreature' as type 
     ) tt left join
     tbl_pet_types pt
     ON pt.type = tt.type LEFT JOIN
     tbl_pet_owners po
     ON po.pet = pt.pet;

这篇关于在MySQL查询结果中包含未找到的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！