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,
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;
select null <> null;
Here <> is not null safe operator. It is Not equal operator.
Refer official website of mysql.
Null safe operator is <=>
<>is like that in MySQL, use the null safe equal<=>: "NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL."SELECT NOT ISNULL(null)