Comparing Similar Approaches to Writing Queries
By Dan Meyers
I often get asked by clients about the “best” way to write a query. Whether they should use IN or EXISTS, table variables or temp tables, a LEFT OUTER JOIN or NOT EXISTS, etc… Most often it depends on what your data is like (does it contain NULL values for example), how are your tables indexed, and a number of other things. In order to make the best decision you really need to understand the details and internal workings of the query engine when using the various approaches that are available to you in T-SQL.
Below are some links to some blogs posts that I think do a good job of explaining the details about some of the most common questions I get when working at a client. Most of them are from Gail Shaw and her SQL in the Wild blog. The others are from SQL Server Central.
LEFT OUTER JOIN vs NOT EXISTS
EXISTS vs IN
IN vs INNER JOIN
NOT EXISTS vs NOT IN
Table Variables vs Temp Tables
JOINs - ON clause vs WHERE clause
