Re: NULL with IN-Clause

Does Oracle have an ISNULL() function?
If so, then this should work.
WHERE Value IN (255,2127) or IsNull(Value)

^Carl

Roman Szklarczyk wrote:

> 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 Tue May 18 1999 - 20:23:10 CDT