sql - MYSQL Left Join how do I select NULL values?

Question

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

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

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 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.

Answer 1

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