MySQL:JOIN查询，获取具有可为空字段的所有项目

Question

我有一个item表，它有一个对tax表的外键引用。该列tax_id。

我曾经将tax_id作为架构中的NOT NULL，但是我只是更改了它，因为我希望它是可选的，所以现在它在架构中NULL DEFAULT NULL。

这打破了我的查询。现在，当我查询项目并加入税收时，我只获得tax_id字段匹配的项目。

这是我的查询：

SELECT item.*
FROM item
JOIN user_item
ON user_item.item_id = item.id
JOIN tax
ON tax.id = item.tax_id
WHERE user_item.user_id = ?
AND item.deleted_at IS NULL
AND user_item.deleted_at IS NULL
AND tax.deleted_at IS NULL

此查询有效，但是，如果给我以下项目：

+----+--------+--------------------+-------------+-------+---------------------+------------+------------+
| id | tax_id | name               | description | price | created_at          | updated_at | deleted_at |
+----+--------+--------------------+-------------+-------+---------------------+------------+------------+
|  1 |      2 | foo                |             |  1.13 | 2017-07-30 15:20:14 | NULL       | NULL       |
|  2 |   NULL | bar                |             |  0.67 | 2017-07-30 15:20:25 | NULL       | NULL       |
|  3 |   NULL | baz                |             |  1.15 | 2017-07-30 15:22:33 | NULL       | NULL       |
+----+--------+--------------------+-------------+-------+---------------------+------------+------------+

然后，查询仅返回 ID 为 1 的项目。我也想要 id 2 和 3。

如何修改查询以实现此目的？

我知道查询的问题就是这部分：ON tax.id = item.tax_id。

Answer 1

这正是left join的用途：

SELECT item.*
FROM item
JOIN user_item
ON user_item.item_id = item.id
LEFT JOIN tax -- Here!
ON tax.id = item.tax_id
WHERE user_item.user_id = ?
AND item.deleted_at IS NULL
AND user_item.deleted_at IS NULL
AND tax.deleted_at IS NULL

Answer 2

我认为最好的方法是将deleted_at条件放在on子句中。 如果您希望表之间的NULL值匹配，请将其编写为：

SELECT i.*
FROM item i JOIN
user_item ui
ON ui.item_id = i.id LEFT JOIN
tax t
ON NOT (t.id <=> i.tax_id) AND  -- ids are the same or both `NULL`.
t.deleted_at IS NULL
WHERE ui.user_id = ? AND
i.deleted_at IS NULL AND
ui.deleted_at IS NULL ;