I was trying to compare values of some fields while using case when in mysql. I found strange behavior. So I tried following query. It's returning me false in mysql. I can't get reason behind it. Any Help?
select if(null=null,'true','false');
Add a comment
|
4 Answers
you should do with IS
SELECT IF(NULL IS NULL,'true','false');
or if you are comparing other field name then for example
SELECT * FROM `table_name` WHERE `field_name` IS NULL
or to negate it
SELECT * FROM `table_name` WHERE `field_name` IS NOT NULL
Comments
From the MySQL documentation at the Working with null values page:
The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
since NULL is "unknown", the comparison NULL = NULL will be unknown as well and will return NULL, and since NULL is not TRUE your function will return 'false'.
You could use IS NULL operator:
SELECT IF(NULL IS NULL, 'true', 'false')
or you can also use then NULL-safe equal to operator:
SELECT IF(NULL <=> NULL, 'true', 'false')
Comments
You can compare NULL in below way also:
SELECT IF (NULL<=>NULL,'True','False');
Comments
NULL isn't a value; it's the lack of a value. So, logically, it cannot compare equally with itself.
This is not unique to MySQL; it is a phenomenon found across the field of computer programming (though there are some notable exceptions such as C).
The operator IS performs a more intelligent, null-aware "comparison", whereby NULL IS NULL evaluates to TRUE.
answered Apr 28, 2014 at 13:42
Lightness Races in Orbit
387k77 gold badges670 silver badges1.1k bronze badges