MySql confusion between empty value (i.e. '') comparision against NULL

Question

Here is my table

+-------------+
|     test    |
+-------------+
| col1 | col2 |
+------+------+
|      | test |
+------+------+
| NULL |      |
+------+------+

Case 1:

Now when I fire a query

SELECT * FROM test WHERE col1 = '';

MySql Return first row as it contains an empty value.

While row#2 contains NULL value in col#1 which is NOT equal to EMPTY VALUE

So the second row is omitted in result set.

This is as expected.

Case 2:

But when I change the query to

SELECT * FROM test WHERE col1 != '';

I get an empty record set, which should be exact opposite of the previous query result.

As empty value check against NULL value is failed in that case NULL should be NOT EQUAL empty value.

So for != check result set should contain row#2 as result, which is not happening in actual scenario.

Instead MySql returns an empty result set.

What I want is to understand this behavior of MySql for empty value comparison for queries like these.

P.S. : Please do not post

Use IS NULL and IS NOT NULL operators instead of empty value.

Not a duplicate try to understand what i am asking instead of marking and editing please read queries and explanation below @martinsmith — Aatman, Jan 07 '16 at 06:37
There is no difference in the behaviour of `NULL != ''` and `'' != NULL`. Or whether it is a NULL literal or a column containing NULL. The reason you don't get that row back is explained in the dupe. You are comparing an empty value *with NULL*. — Martin Smith, Jan 07 '16 at 06:44
So what you are saying is using `=` and `!=` operators `NULL` values are always going to be omitted?? — Aatman, Jan 07 '16 at 06:48
Yes. So you would have to include an `OR Col1 IS NULL` if you wanted to also bring back nulls. — Martin Smith, Jan 07 '16 at 06:49

0 Answers0