RE: SQL statement
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx>
- Date: Tue, 26 Oct 2004 21:05:33 -0500
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@xxxxxxxxxxxxxx>:
> 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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
