CODEWITHSUNDEEP
mysqlnullcomparison
Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

Null Comparison in MySQL

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');

Upvotes: 1

Views: 319

Answers (4)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385154

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.

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

You can compare NULL in below way also:

SELECT IF (NULL<=>NULL,'True','False');

Upvotes: 1

fthiella
fthiella

Reputation: 49049

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')

Upvotes: 1

Jerko W. Tisler
Jerko W. Tisler

Reputation: 989

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

Upvotes: 2

Related Questions