Re: [HACKERS] Re: Comparisons on NULLs (was Re: A small problem...)

D'Arcy J.M. Cain <darcy@{druid|vex}.net> writes:
> Thus spake Tom Lane
> > >> but I can see the reasonableness of defining "3 != NULL" as TRUE.
> > 
> > > Actually I see it as FALSE.  That's what I was suggesting earlier.  All
> > > comparisons to null should be false no matter what the sense of the
> > > test.
> > 
> > Hmm.  That yields extremely unintuitive results for = and !=.  That is,
> > 
> >     SELECT * FROM t WHERE b = NULL;
> > 
> > will never return any rows, even if there are some where b is null;
> 
> Hmmm.  That would be a problem.  Of course, we could treat the null
> value at the higher level too.  I guess that's why we have the "IS
> NULL" syntax in the first place.  It is different than comparing the
> actual values.

Not sure how serious this discussion is, so if I have wandered into the
middle of a joke, just kick me ...

That said,

SELECT * FROM t WHERE b = NULL;

_should not_ return any rows. NULL is not "=" to anything, not even another
NULL. NULL is also not ">", or "<", or "!=" to anything either.

So, "NULL = NULL" is false as is "NULL != NULL".

This indeed is why we have "IS NULL" and "IS NOT NULL".

-dg

David Gould            dg@informix.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
"Samba is a huge win ... ; it enables open-source techies to stealththeir Linux boxes so they look like Microsoft
serversthat somehowmiraculously fail to suck."         -- Eric Raymond