MYSQL Left Join如何选择NULL值？

This is a follow up question to my last question about table joins in MySQL

这是我关于MySQL中表连接的上一个问题的后续问题

I need to be able to select the NULL values from the left joined table.

我需要能够从左连接表中选择NULL值。

Here's my join:

这是我的加入：

table1.id | table1.name | table2.id | table2.surname
        1 |        John |         1 |            Doe
        2 |     Michael |         2 |       Anderson
        3 |        Anna |      NULL |           NULL
        4 |         Sue |      NULL |           NULL

I would want to select WHERE table2.surname = NULL, but a query like this doesn't work:

我想选择WHERE table2.surname = NULL，但像这样的查询不起作用：

SELECT table1.*,table2.*
FROM table1
LEFT JOIN table2
    ON table1.id=table2.id
WHERE table2.surname=NULL

I can somewhat understand the logic behind it not giving me any results, but there must be a way to grab them results?

我可以在某种程度上理解它背后的逻辑并没有给我任何结果，但必须有一种方法来获取结果？

Appreciate any help.

感谢任何帮助。

4 个解决方案

#1

To compare NULL values you have to use the IS NULL predicate, like this:

要比较NULL值，您必须使用IS NULL谓词，如下所示：

SELECT table1.*, table2.*
FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.surname IS NULL

#2

You have to use IS NULL instead of = NULL:

您必须使用IS NULL而不是= NULL：

WHERE table2.surname IS NULL

The reason why you can't simply do = NULL is because NULL is essentially an "unknown" and can't equal or not equal to anything (not even itself), so trying to compare it to something as if it were supposed to be an exact match would simply return NULL instead of an expected boolean 0 or 1, and that's exactly why your query was returning an empty result.

你不能简单地做= NULL的原因是因为NULL本质上是一个“未知”并且不能等于或不等于任何东西（甚至不是它自己），所以试图将它与某些东西进行比较，好像它应该是完全匹配只会返回NULL而不是预期的布尔值0或1，这正是您的查询返回空结果的原因。

There's a clear difference between "is unknown" and "equals unknown". You can surely test if something is unknown or is not unknown, but you can't test if something "equals" unknown because unknown is unknown, and it wouldn't make sense.

“未知”和“等于未知”之间存在明显差异。你可以肯定地测试某些东西是未知的还是未知的，但是你无法测试某些东西是否“未知”，因为未知是未知的，而且它没有意义。

Also, since you're using MySQL, another option would be to use table2.surname <=> NULL, where <=> is a MySQL-specific NULL-Safe comparison operator, but try not to use that and just stick with the standard SQL way (IS NULL / IS NOT NULL)

此外，由于您使用的是MySQL，另一种选择是使用table2.surname <=> NULL，其中<=>是一个特定于MySQL的NULL-Safe比较运算符，但尽量不要使用它并且只是坚持使用标准SQL方式（IS NULL / IS NOT NULL）

#3

try with:

尝试：

SELECT table1.*,table2.* 
FROM table1 
  LEFT JOIN table2 ON table1.id=table2.id 
WHERE table2.surname IS NULL

#4

According to MySQL specification you should use "IS NULL" instead of "= NULL". It says that "(NULL = NULL) equals to NULL". But NULL equals False while it used as Boolean.

根据MySQL规范，你应该使用“IS NULL”而不是“= NULL”。它说“（NULL = NULL）等于NULL”。但是当它用作布尔值时，NULL等于False。

#1