CODEWITHSUNDEEP
mysqlnull
SanMu
SanMu

Reputation: 765

MySQL falsely ignoring Null values

I am trying to run a fairly straightforward query along the lines of:

SELECT * FROM TABLE WHERE NOT COLUMN1 IN ('A','B')

I am expecting this to return the rows where COLUMN1 is NULL, however, in addition to getting rid of anything that is A and B it also removes any NULL values in COLUMN1 and I don't get the logic. Very much new to this so would appreciate any help.

Thanks

Upvotes: 0

Views: 30

Answers (1)

Barmar
Barmar

Reputation: 782653

NULL has some unexpected behaviors. Probably the most confusing is that while it acts like FALSE when used as a condition, it doesn't act like it when used as a sub-expression of a condition. The value of most expressions that include NULL is NULL (the most obvious exceptions are that NULL IS NULL and NULL IS NOT NULL are TRUE and FALSE).

So the value of NULL IN ('A', 'B') is NULL, not FALSE.

And the value of NOT NULL is NULL, not TRUE.

So when COLUMN1 is NULL, the value of NOT COLUMN1 IN ('A', 'B') is NULL, not TRUE or FALSE.

You have to test for NULL explicitly.

WHERE column1 IS NULL OR column1 NOT IN ('A', 'B')

Upvotes: 1

Related Questions