[Last Call] Learn how to a build a cloud-first strategyRegister Now


replace NOT IN with an outer join

Posted on 2011-04-19
Medium Priority
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 tblB.name=tblA.name
    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

chaitu chaitu earned 1000 total points
ID: 35424946
 select distinct tblA.*,tblB.type,
       from tblA
inner join sec on tblB.name=tblA.name
    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

ID: 35425051
How do not exists and not in differ?

in compare
LVL 14

Expert Comment

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

Hope this links helps you



LVL 71

Assisted Solution

Qlemo earned 1000 total points
ID: 35471493
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.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

830 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