Re: NULL with IN-Clause

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.--- Received on Tue May 11 1999 - 08:11:42 CDT