Microsoft® SQL Server 2012 Unleashed

Determining the Optimal Join Order

In addition to determining the best join strategies, the Query Optimizer also evaluates and determines the optimal join order that would result in the most efficient query plan. In the query’s execution plan, you might find that the order of the tables in the execution plan is a different order than specified in the query. Regardless of the join strategy used, the Query Optimizer needs to determine which table is the outer input and which is the inner input to the join strategy chosen. For example, consider the following query:

Click here to view code image

select soh.SalesOrderID, OrderDate, Status, ProductNumber, ListPrice    from Sales.SalesOrderHeader soh    join Sales.SalesOrderDetail sod    on soh.SalesOrderID ...