replace NOT IN with an outer join

Posted on 2011-04-19
Last Modified: 2012-05-11
I'm trying to speed-up a query and I was told to remove the NOT IN that 's in the query with something else like an OUTER JOIN.

 select distinct tblA.*,tblB.type,
       from tblA
inner join sec on
    left join item_details on rtrim(parentid)=atd_unique_cust_key and appid='myApp'
where atd_unique_cust_key not in (
     select distinct atd_unique_cust_key
                 from tblC
inner join tblD on tblD.account=tblC.account);

Question by:sl1nger
    LVL 20

    Accepted Solution

     select distinct tblA.*,tblB.type,
           from tblA
    inner join sec on
        left join item_details on rtrim(parentid)=atd_unique_cust_key and appid='myApp'
    where not exists (
         select 'X' from tblC
    tblA.atd_unique_cust_key = tblC.atd_unique_cust_key
    inner join tblD on tblD.account=tblC.account);

    Open in new window


    Author Comment

    How do not exists and not in differ?

    in compare
    LVL 14

    Expert Comment

    Performance wise EXISTS is much faster than NOT IN as EXISTS operates on boolean value

    Hope this links helps you

    LVL 67

    Assisted Solution

    NOT IN performs indeed bad in situations where you get many items in the NOT IN list. If there are few, it's absolutely ok to use.

    Replacing NOT IN or NOT EXISTS with an outer join is worse. Outer joins need more resources than exist/in relations, since they force an active join. Only if you need to join the outer table again for getting the IN or EXISTS list there might be a performance gain with using a well-formed outer join.

    EXISTS and NOT EXISTS have the pro that they dismiss the results, and concentrate on the boolean value, which allows for many internal optimizations, and in particular for making use of otherwise unused, small indexes, if existing. However, the need to use a join in EXISTS or NOT EXISTS can render that more costly, if not well-formed and bad indexed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now