Re: NULL with IN-Clause

James Lorenzen wrote:
>
> In article <u790awm1p2.fsf_at_ebi.ac.uk>,
> Philip Lijnzaad <lijnzaad_at_ebi.ac.uk> wrote:
> > On Fri, 07 May 1999 09:40:24 -0100,
> > "Gernot" == Gernot <Ewert_Ahr._Electronic_GmbH_at_t-online.de> writes:
> >
> > Gernot> I've just tried the following SQL-expression:
> > Gernot> SELECT ...
> > Gernot> WHERE Value IN (NULL,255,2127)
> > I have no idea, but I just tried it in Oracle 7.3.2.3.0, and it
> accepts
> > ``... WHERE VALUE IN (NULL, 1)''. However, this query returns just the
> row
> >with VALUE=1, not the row with VALUE=null (which is also present in
> the test
> >table). ``... WHERE VALUE IN (NULL)'' also fails. It looks like in
> Oracle,
> >an IN NULL clause never yields any results, regardless of table
> content. I
> >don't know about the standard, but would argue that such a query is
> invalid
> >(since meaningless, and difficult to spot), and a parse error should
> have
> >been raised (which should be easy enough). Cheers,
> >
> Philip
> >
> Philip :
> A NULL is an unknown value. That means a NULL is never equal to
> anything, not even another NULL. It also means that a NULL is never not
> equal to anything. IE NULL = NULL evaluates to FALSE, therefore the IN
> clause with a NULL in the value list will never return a NULL value.
>
> BTW: NULL != NULL also evaluates to FALSE.
>
> HTH
> James
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---

IMHO NULL=NULL evaluates to NULL. Received on Mon May 17 1999 - 09:18:02 CDT