CODEWITHSUNDEEP
mysqlnull
Jaylen
Jaylen

Reputation: 40381

Why does MySQL ignore null values when looking for not equal?

I am noticing something weird in MySQL and I would like to see why it behaves this way and of there a way to change it?

Scenario

I have accounts InnoDB table with the following columns id, name, type where the type is a null-able.

Now, lets say I have 10 records where the type = "TEST" and 100 records with they type IS NULL and 20 records where the type = "STANDARD"

If I run this query

SELECT * FROM accounts WHERE type <> "TEST"

This query will only show me the the 20 records that have type = "STANDARD" only and it ignores the 100 records that have a null value.

To work around this I would have to do something like this

SELECT * FROM accounts WHERE IFNULL(type,"") <> "TEST"

OR 

SELECT * FROM accounts WHERE (type <> "TEST" OR type IS NULL)

NULL value means blank "aka no value" and no value means <> "TEST"

It is probably an expected behavior but I am not sure why is it designed like so

Upvotes: 7

Views: 3713

Answers (2)

Venkata Krishna
Venkata Krishna

Reputation: 1776

SELECT * FROM accounts WHERE type <> "TEST"

The meaning of this statement would be,

"Select the rows from accounts where the value of column type is not equal to 'TEST' ".

Which means MySQL returns the records having a value in type which is not equal to "TEST".

Now here, since NULL means there is no value, it does not return those records which does not have any value for type column.

Upvotes: 4

davek
davek

Reputation: 22925

NULL means "the value cannot be known/is not known": which is different to "no value" - a comparison cannot be carried out at all against an unavailable value.

Upvotes: 4

Related Questions