I have to follow-up to myself, as I forgot one important thing:
Joerg Bruehe wrote:
> so in SQL for *any* comparison operator "op" ( = < > <> ... LIKE ... )
> and *any* known (= non-NULL) value x (column, literal, expression, ...)
> the result of
> x op NULL
> is UNKNOWN.
The same holds if you compare two NULL expressions:
It is UNKNOWN how two unknown (missing) values relate to each other.
> As a rough analogy, consider comparing some visible object to one which
> is hidden behind a curtain:
> You cannot tell whether they are identical or not, which one is larger,
> ... , so the result is UNKNOWN. Especially, the result is *not* FALSE !
And similar, you cannot say anything about two objects which are both
hidden, especially, you cannot claim them to compare equal !
So even if two expressions "x" and "y" (say two columns) both are known
to be NULL, the comparison
x = y
If you really want two NULL expressions to match a comparison, this
SELECT x, y, z FROM tab
WHERE x = y OR x IS NULL AND y IS NULL
(or any other comparison operator replacing the '=').
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com