Why does MySQL ignore null values when searching for unequals?
I notice something strange about MySQL and I would like to understand why it behaves like this and is there a way to change it?
Scenario
I have an InnoDB count table with the following columns id, name, type
where the type is null.
Now lets say I have 10 records where type = "TEST" and 100 records with them type IS NULL
and 20 records where type = "STANDARD"
If I run this query
SELECT * FROM accounts WHERE type <> "TEST"
This query will only show 20 records that have type = "STANDARD" and it will ignore 100 records that have a null value.
To get 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 means space "aka no value" and value does not mean <> "TEST"
This is probably the expected behavior, but I'm not sure why it is created this way
source to share
SELECT * FROM accounts WHERE type <> "TEST"
The meaning of this statement would be:
"Select rows from accounts where the column type value is not equal to" TEST ".
Which means that mysql returns records that have a value in type that is not "TEST".
Now, since NULL means there is no value, it does not return those records that have no value for the type column.
source to share