MySQL mystery: Null value is not different from no

Question

Why is this query returning 0 rows?

select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1<>t.f2;

This is a distilled version of a complex query I have. I want to compare two tables containing one-to-one related data and I want to select those rows that contain different values for certain fields. But also there can be the case where one row is missing in one of the tables. The LEFT JOIN correctly returns null values for these rows, but then, the WHERE clause is incorrectly (or unexpectedly) filtering these rows out.

Why -in this case- 'null' IS NOT DIFFERENT to any not null value (like 'a') ?

What is driving me crazy is that this

select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t;

returns 1 row (as I expected) but this

select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1=t.f2;

returns 0 rows !! So null is not equal to 'a' and null is not different to 'a' !!

Please... Can anybody explain this?

Answer 1

SQL NULL does not work the way you would like it to work: http://en.wikipedia.org/wiki/Sql_null

In short, NULL = NULL is not true. NULL <> NULL is not true. NULL <> 1 is not true. And so forth.

Answer 2

Try to do this query:

   select * from dual where NULL = NULL

It returns 0 rows. That's because to compare a value with null you have to do IS NULL or IS NOT NULL, otherwise it will return false.

Answer 3

The concept of NULL is a common source of confusion for newcomers to SQL, who often think that NULL is treated as the other values.

This is not the case. Conceptually, NULL means "a missing unknown value" and therefore it is treated very differently.

What you are seeing is pretty easy to explain. Consider the following example:

CREATE TABLE mytb (id int, value int);

INSERT INTO mytb VALUES (1, 100);
INSERT INTO mytb VALUES (2, 200);
INSERT INTO mytb VALUES (3, NULL);
INSERT INTO mytb VALUES (4, 400);

The above means that for the row with id = 3, the value is "unknown". It could be 300, or it could be 100, or anything else.

Therefore when you request the following:

SELECT * FROM mytb WHERE value <> 100;
+------+-------+
| id   | value |
+------+-------+
|    2 |   200 |
|    4 |   400 |
+------+-------+
2 rows in set (0.00 sec)

The row with id = 3 is not returned, because NULL <> 100 returns "unknown". We don't know if row id = 3 has a value of 100, so the expression does not return true. I doesn't return false either. It returns "unknown" (NULL).

The condition of the WHERE clause can only be satisfied when the expression is true. When you compare something to NULL, the expression can never be true. It will be "unknown".

Answer 4

NULL value is nothing, it can't be equal or not equal to something. If you want to check if your value is null - use "IS NULL" statement:

select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where t.f1 IS NULL

If you want to check if your values are equal or not equal - you can use COALESCE function on nullable columns:

select t.f1, t.f2
from (select null f1, 'a' f2 from dual) t
where COALESCE(t.f1, '')<>COALESCE(t.f2, '');

Answer 5

Exactly. NULL represents an unknown value, not any specific value (it is not the same as NULL in C, or nil in Ruby, etc.) In SQL, if you compare something to the unknown value, the result is also unknown. And you will not get the rows where WHERE condition is unknown.

Try this:

SELECT NULL <> 2;

and you will see NULL as result.

Try this:

SELECT * FROM t WHERE NULL;

and no rows will come out, even if the table t is huge.

If you really need what you said you wanted (and I am not advocating this), you can do something like this:

SELECT T.f1, T.f2
FROM (SELECT NULL f1, 'a' f2) T
WHERE ((T.f1 IS NULL OR T.f2 IS NULL)
    AND (T.f1 IS NOT NULL OR T.f2 IS NOT NULL))
    OR T.f1 <> T.f2