7

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

Jaylen
  • 39,043
  • 40
  • 128
  • 221
  • 4
    This is the SQL standard. That is the way that operations on `NULL` are defined in the language. And, `NULL` does not mean "no value"; it means "unknown". – Gordon Linoff Nov 04 '14 at 18:03
  • What about `<=>` - is that MySQL specific, and is there an opposite other than wrapping in `NOT()` – Jake Oct 11 '17 at 05:48

2 Answers2

4

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.

davek
  • 22,499
  • 9
  • 75
  • 95
4
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.

Joshua Pinter
  • 45,245
  • 23
  • 243
  • 245
Venkata Krishna
  • 1,768
  • 2
  • 14
  • 21