Hi all,
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
yields UNKNOWN.
If you really want two NULL expressions to match a comparison, this
should work:
SELECT x, y, z FROM tab
WHERE x = y OR x IS NULL AND y IS NULL
(or any other comparison operator replacing the '=').
Regards,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.