CODEWITHSUNDEEP
mysqlsqljoin
user1177811
user1177811

Reputation: 643

MYSQL Left Join how do I select NULL values?

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.

Upvotes: 37

Views: 41734

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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

Upvotes: 46

V.I.S.
V.I.S.

Reputation: 83

According to MySQL specification you should use "IS NULL" instead of "= NULL". It says that "(NULL = NULL) equals to NULL". But NULL equals False while it used as Boolean.

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26871

try with:

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

Upvotes: 4

Zane Bien
Zane Bien

Reputation: 23135

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)

Upvotes: 13

Related Questions