Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: NULL with IN-Clause

Re: NULL with IN-Clause

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Tue, 11 May 1999 18:40:35 GMT
Message-ID: <7h9tj3$e9m$1@nnrp1.deja.com>


I fell into the NULL void (so to speak) When I said that NULL = NULL evaluates to false, it should be "it does not evaluate to TRUE". That blasted three value logic. It actually evaluates to NULL. In this world, the opposite of TRUE is not FALSE; it is (FALSE or IS NULL) :>. AND the opposite of FALSE is not TRUE; it is (TRUE or IS NULL).

The use of NULL in an "IN" clause (at least in Oracle) does seem to be meaningless, but NULL is a valid parameter in Oracle FUNCTIONS and PROCEDURES. The argument
In article <u7wvyflmfu.fsf_at_ebi.ac.uk>,
  Philip Lijnzaad <lijnzaad_at_ebi.ac.uk> wrote:
> "James" == James Lorenzen <james_lorenzen_at_allianzlife.com> writes:
>
> is that 'known to unknown', or 'unknown to be known', or 'unknown to
> be unknown' ... :-)
> NULLs are, of course, useful but ocasionally dubious.

                                                (very tricky)

>
> 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.
>
> I know, I know. I argue that having NULL in an IN construct is
> meaningless,
> and an error should be raised.
>
> At the same time, some database vendors (e.g. Sybase) *do* retrieve
> rows being NULL when NULL is part of an IN construct. So, who's right
  and
> who's wrong ? Cheers,
>

Sybase may "see" the NULL and check "IS NULL"

All of this NULL thinking makes my brain hurt. ;)

James

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 11 1999 - 13:40:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US