List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:November 16 2006 9:28pm
Subject:Re: finding NULL records
View as plain text  
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

Thread
finding NULL recordsAlan Nilsson31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsAlan Nilsson31 Oct
    • Re: finding NULL recordsChris31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • RE: finding NULL recordsJerry Schwartz31 Oct
    • Re: finding NULL recordsMartijn Tonies31 Oct
      • Re: finding NULL recordsmizioumt15 Nov
        • RE: finding NULL recordsJerry Schwartz15 Nov
          • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsmizioumt17 Nov
          • Re: finding NULL recordsmizioumt17 Nov