I am reviewing code in a SQL database that was coded by a BA and a Old oracle developer. Never a good idea to begin with. I am finding a lot of queries that should be using a JOIN, or LEFT OUTER JOIN, and they are using sub-queries with NOT IN. Plus s lot of just poorly written code.
I am looking for some solid proof as to which type of query is better. I have always known that using JOIN is always better for performance since one can index that column(s) that the JOIN will use. Which is usually a Primary key in the tables involved.
Can someone shed some light on this.
Thank you in advance for all your advice, suggestions and time.
To be honest, IN and NOT IN are actually a little bit faster than the related joins. There are some caveates for both that people need to understand. NOT IN can actually be a fair bit faster than an OUTER JOIN with a predicate that looks for NULLs.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs