Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id hBIFuFF24067 for ; Thu, 18 Dec 2003 09:56:15 -0600 X-ClientAddr: 66.27.56.210 Received: from ns3.fatcity.com (rrcs-west-66-27-56-210.biz.rr.com [66.27.56.210]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id hBIFuEo24058 for ; Thu, 18 Dec 2003 09:56:14 -0600 Received: from ns3.fatcity.com (localhost.localdomain [127.0.0.1]) by ns3.fatcity.com (8.12.8/8.12.8) with ESMTP id hBIFo0kv014082 for ; Thu, 18 Dec 2003 07:50:59 -0800 Received: (from root@localhost) by ns3.fatcity.com (8.12.8/8.12.5/Submit) id hBIFPou6009633 for oracle-l@orafaq.com; Thu, 18 Dec 2003 07:25:51 -0800 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005DA51B; Thu, 18 Dec 2003 07:29:31 -0800 Message-ID: Date: Thu, 18 Dec 2003 07:29:31 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Subject: Re: Re: Any known problems using NOT IN ? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit btw, a straight not in without a hash_aj, tends to get hideous bench marks. > > From: Mladen Gogala > Date: 2003/12/18 Thu AM 09:14:24 EST > To: Multiple recipients of list ORACLE-L > Subject: 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@fatcity.com [mailto:ml-errors@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@cisco.com > > > > s.haldankar@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@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@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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: