MYSQL Left Join 如何选择 NULL 值?

2021-12-17 00:00:00 join sql mysql

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

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

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

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

这是我的加入:

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

我想select WHERE table2.surname = NULL，但是这样的查询不起作用:

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

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?

感谢任何帮助.

推荐答案

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

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

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

相关文章