2

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.

Mazzy
  • 1,901
  • 2
  • 16
  • 36
  • I don't see why that would help to write an explenation of the behaviour of mysql – Mazzy Apr 18 '15 at 09:24
  • Why would you ever compare strings with NULL? – Al.G. Apr 18 '15 at 09:25
  • This might be of your interest: http://stackoverflow.com/q/16234854/3132718 – Al.G. Apr 18 '15 at 09:26
  • @Ai.G. I need to check if a field called status does not contain 'processing' for example, it can be null or another status, I want to select all rows that do not contain 'processing', so including the rows that have status=null – Mazzy Apr 18 '15 at 09:28
  • 1
    One options is to use [<=>](http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to) (NULL-safe equal). – wchiquito Apr 18 '15 at 09:32

2 Answers2

2

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 |
+---------------------------+
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • I do not want to check if a value is NULL, I want to check if x is not y and if x is null and y is a string, why does it return null instead of a boolean? – Mazzy Apr 18 '15 at 09:22
2

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331