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 

Answer 2

You have to use IS NULL instead of = 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.

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)