CODEWITHSUNDEEP
mysql
user1100366
user1100366

Reputation: 51

Why does select IF(null <> 1,"true","false") return false? null is not 1?

Why does select IF(null <> 1,"true","false") return false? null is not 1?!

Upvotes: 3

Views: 573

Answers (1)

Rick James
Rick James

Reputation: 142298

NULL cannot be tested with = or <>. By definition, such tests always fail.

Instead, to test a column col for nullness, use one of these, as appropriate:

col IS NULL
col IS NOT NULL

There is also <=>, but read the manual on the details. It may be 'correct' to change to this (but I am not sure):

IF(null <=> 1, "false", "true")

If the "null" is coming from app code, then consider one of these (or many other possibilities) to turn NULL into something that can be compared:

IFNULL(?, 0)
COALESCE(?, 0)

Another thing to consider

IF(<<some test>>, true, false)

can be replaced by simply the test.

But if you really need the strings "true" and "false", then this tip does not apply.

These understand NULL: <=>, IFNULL(), COALESE(), IS NULL, IS NOT NULL, COUNT(col) (by not counting the row when col IS NULL), LEFT JOIN (by generating NULLs), etc

These do not understand NULL, and simply return NULL: +, -, *, /, <, >, etc.

Upvotes: 4

Related Questions