MYSQL Left Join如何选择NULL值?

问题描述：

这是我关于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

MYSQL Left Join如何选择NULL值?

相关推荐