CODEWITHSUNDEEP
mysql
Rajesh Kumar
Rajesh Kumar

Reputation: 1290

Why is null<>null=null in mysql

I am going through a tutorial for MySql, and I came through the following query.

mysql> select null <> null;
+--------------+
| null <> null |
+--------------+
|         NULL |
+--------------+

I did not understand why the result is Null, it needs to be either 1 or 0 I think (based on results from other comparison operators)?

Why it is giving the result as Null.

Thanks,

Upvotes: 1

Views: 127

Answers (2)

Pablo Francisco P&#233;rez Hidalgo
Pablo Francisco P&#233;rez Hidalgo

Reputation: 27702

Because any comparison operator over NULL appearing in in a sql filter should (and does) make the row not be selected.

You should use null safe operator <=> to compare to column containing NULL values and other NOT NULL value but <=> will return 1 when both operands are NULL because NULL is never considered equal to NULL.

This is an example of a situation where null safe operator is useful:

You have a table:

Phones
----
Number
CountryCode (can be NULL)

And you want to select all phone numbers not being from Spain (country code 34). The first try is usually:

SELECT Number FROM Phones WHERE CountryCode <> 34;

But you notice that there are phones with no country code (NULL value) not being listed and you want to include them in your result because they are nor from Spain:

SELECT Number FROM Phones WHERE CountryCode <=> 34;

Upvotes: 2

unknown
unknown

Reputation: 5017

select null <> null;

Here <> is not null safe operator. It is Not equal operator. Refer official website of mysql.
Null safe operator is <=>

Upvotes: 1

Related Questions