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

Question

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

Answer 1

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.

Answer 2

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.