CODEWITHSUNDEEP
mysqlcomparison
Mazzy
Mazzy

Reputation: 1944

MySql returning NULL on comparison

I've been running into an issue.

Can someone explain why

SELECT (null <> 'TEST STRING')

and

SELECT (null != 'TEST STRING')

returns NULL instead of a boolean true as you would expect?

I'm not looking for a solution, rather for an explanation why SQL behaves like this.

You would expect any comparison in no matter what language to return a boolean.

Upvotes: 2

Views: 71

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Arithmetic comparison cannot be performed with NULL operators. So when you perform arithemetic operation with NULL like <> and != it is giving you NULL(a meaningless value) against your expection as boolean value.

The MySQL can be referred for explanation as to how NULL works in MySQL.

Also note that in MySql NULL is special, it means unknown value while in programming it means undefined value.

Upvotes: 2

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. https://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

You may use is null or is not null comparison

mysql> select  'TEST STRING' is not null;
+---------------------------+
| 'TEST STRING' is not null |
+---------------------------+
|                         1 |
+---------------------------+

Upvotes: 2

Related Questions