MySQL - different behaviour for = "" and != "" with regards to null

Question

This may be a silly question, but null seems to neither equal nor unequal any empty string "".

I have a table with the following values:

id field1 field2 field3
1   a        b     c
2   null     b     c
3            b     c
4   a        b     c

My query

select * from table where field1 = ""

does not return row 2 where the value of field1 is null. This makes absolute sense, as null is not equal to an empty string.

But,

select * from table where field1 != "" 

doesn't return the row 2 either.

Does anyone have an explanation for the historic origin of this? Is it because the value null means that we do not know the value and hence it is unknown whether field1 is equal or unequal to an empty string for row 2?

Answer 1

NULL is not equal to an empty. NULL is not equal to anything, including NULL. To compare to NULL you need to use IS NULL or IS NOT NULL

SELECT NULL = NULL,
NULL != NULL,
NULL IS NULL,
NULL IS NOT NULL

NULL = NULL     NULL != NULL        NULL IS NULL    NULL IS NOT NULL
(null)             (null)            1                  0

SQL Fiddle

select * from table where field1 IS NULL

or

select * from table where field1 IS NOT NULL