We help IT Professionals succeed at work.

replace NOT IN with an outer join

sl1nger
sl1nger asked
on
764 Views
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);

Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
How do not exists and not in differ?














in compare

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

Hope this links helps you


http://decipherinfosys.wordpress.com/2007/01/21/32/

http://hemantoracledba.blogspot.com/2009/07/difference-between-not-in-and-not.html

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.