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

D'Arcy J.M. Cain <darcy(at){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(at)informix(dot)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 stealth
their Linux boxes so they look like Microsoft servers that somehow
miraculously fail to suck." -- Eric Raymond