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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL statement

RE: SQL statement

From: Dan Tow <dantow_at_singingsql.com>
Date: Tue, 26 Oct 2004 21:05:33 -0500
Message-ID: <1098842733.417f026d2e699@www.singingsql.com>


Oh, I'm not arguing that NOT IN fails to do what it is designed to do, and what the SQL standard requires, just that what it is designed to do is almost never what the developer *wants*, where the subquery can return NULLs! I've certainly never heard of a case where the NOT IN version of the query was functionally correct (by the standard of what the APPLICATION needed the query to do, not by the standard of how SQL should interpret the query), but the NOT EXISTS form was not. I'd be interested to hear if anyone on this list ever ran into an application requirement that actually *needed* the query containing a NOT IN subquery to return no rows if the subquery returned a null value in its list.

Thanks,

Dan Tow
650-858-1557
www.singingsql.com

Quoting Lex de Haan <lex.de.haan_at_naturaljoin.nl>:

> ahh, null values ... please, allow me to join the conversation!
>
> Dan, talking about NOT IN vs. NOT EXISTS:
> you could argue that the NOT IN does the "right thing"
> considering we are in the world of three-valued logic,
> whereas the NOT EXISTS is doing the "wrong thing" ...
>
> formally stated:
> the EXISTS operator does not correspond with the iterated OR.
>
> Kind regards,
> Lex.
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 26 2004 - 21:01:06 CDT

Original text of this message

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