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.