Chapter 10: Advanced Queries and Scripting

Overview

Writing queries is like driving to work. There are probably 18 different ways to get there, and most likely, some routes are clearly better than others. Under different conditions, different choices may be preferable. This might depend on variable traffic conditions, the time of day, or whether you need to stop by the grocery store to pick up milk and flowers. In my case, the route I choose depends largely on the ferry schedule. If I can't make the ferry, I save time by driving a much greater distance rather than waiting for the next boat. Some opt for consistency, choosing to take the same route, regardless of changing conditions, while others weave through traffic to find the fastest lane, and shave-off a few seconds here and there.

Similarly, many queries can be written more than one way. Traditional SQL statements, written for other database products, often use subqueries whereas SQL Server leans toward ANSI-standard join expressions. Most SQL Server professionals will tell you that if you have the option to choose between using a subquery and a join, the joins will execute faster. Generally speaking, I think this is true, but it depends on the expression and other conditions. Using joins gives the database engine more leeway to implement the best type of operations for a query, whereas subqueries may not afford SQL Server as many options.

Depending on the query, the same results can be achieved using either technique. Graphical design tools, such as the Query Designer, build ANSI join statements. This is considered to be SQL Server's native form for combining column data from more than one table. To implement some business logic, joins simply may not give you the flexibility you need and a subquery may be the only answer.