Re: Any known problems using NOT IN ?

Actually, because relational database conform to the rules of set theory, I find it preferable to use "MINUS" wherever possible. Oracle optimizer is trained to spot set operations and they usually generate sort/merge or hash based execution plan, while "NOT IN" and "NOT EXIST" can generate NL plan, which is, generally speaking, undesired when you do set operations.

On 12/18/2003 12:39:26 AM, Charu Joshi wrote:
> Siddharth,
>
> The NOT IN query fails to return rows, if the inner sub-query returns NULL
> values. It is always recommended to use the NOT EXISTS clause, unless you
> are sure that the inner query will not return any NULLs.
>
> Regards,
> Charu.
>
> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Siddharth Haldankar
> Sent: 18 December 2003 10:54
> To: Multiple recipients of list ORACLE-L
> Subject: Any known problems using NOT IN ?
>
> Hi Gurus,
>
> I have a problem using NOT IN clause in Oracle. However using NOT EXISTS,
> gives me the right output. Are there any known limitations.
>
> This query selects from the master records wherein child records are not
> active.
>
> select * from ct_software_release csr where
>
> csr.class = 'NS'
>
> AND csr.active_flag = 'Y'
>
> AND csr.os_id_pk not IN
>
> (SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs
>
> WHERE crs.active_flag = 'Y');
>
> The sub-query in the above case gives 1800 rows. The above query fails to
> give any rows.
>
> select * from ct_software_release csr where
>
> csr.class = 'NS'
>
> AND csr.active_flag = 'Y'
>
> AND NOT EXISTS
>
> (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs
>
> WHERE crs.os_id_fk1 = csr.os_id_pk
>
> AND crs.active_flag = 'Y');
>
> This above query works fine.
>
> Thanks
>
> ----------------------------------------------------------------------------
> --
>
> Siddharth Haldankar
>
> Zensar Technologies Ltd.
>
> Cisco Systems Inc.
>
> (Offshore Development Center)
>
> # : 091 020 4128394
>
> shaldank_at_cisco.com
>
> s.haldankar_at_zensar.com
>
> *********************************************************
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *********************************************************
>
> Visit us at http://www.mahindrabt.com
>
>

--
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).