MYSQL Left Join 如何选择 NULL 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11500150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Left Join how do I select NULL values?
提问by user1177811
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:
我想要select 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.
感谢任何帮助。
回答by Mahmoud Gamal
To compare NULLvalues you have to use the IS NULLpredicate, like this:
要比较NULL值，您必须使用IS NULL谓词，如下所示：
SELECT table1.*, table2.*
FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.surname IS NULL
回答by Zane Bien
You have to use IS NULLinstead of = NULL:
你必须使用IS NULL而不是= NULL：
WHERE table2.surname IS NULL
The reason why you can't simply do = NULLis because NULLis essentially an "unknown" and can't equalor not equalto anything (not even itself), so trying to compare it to something as if it were supposed to be an exact match would simply return NULLinstead of an expected boolean 0or 1, and that's exactly why your query was returning an empty result.
你不能简单地做的原因= NULL是因为NULL本质上是一个“未知”并且不能等于或不等于任何东西（甚至不是它本身），所以试图将它与某些东西进行比较，就好像它应该是完全匹配一样将简单地返回NULL而不是预期的布尔值0or 1，这正是您的查询返回空结果的原因。
There's a clear difference between "is unknown"and "equals unknown". You can surely test if something isunknown or is notunknown, but you can't test if something "equals"unknown because unknown is unknown, and it wouldn't make sense.
“未知”和“等于未知”之间有明显的区别。您当然可以测试某些东西是否未知或不是未知的，但是您不能测试某些东西是否“等于”未知，因为 unknown 是unknown，而且没有意义。
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, where<=>是 MySQL 特定的 NULL-Safe 比较运算符，但尽量不要使用它，而是坚持使用标准的 SQL 方式 ( IS NULL/ IS NOT NULL)
回答by Tudor Constantin
try with:
尝试：
SELECT table1.*,table2.*
FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.surname IS NULL
回答by V.I.S.
According to MySQL specificationyou 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。
