Joins

We can explicitly ask the query optimizer to use any of the available join physical algorithms: Nested Loop Join, Merge Join, and Hash Join. We could do this at the query level, in which case all the existing joins in the query will be affected, or we can request it at the join level, ensuring that only that specific join is impacted (although, as you will see later, this last choice will also impact the join order on the plan).

Let’s focus on join hints at the query level first, in which case, the join algorithm is specified using the OPTION clause. You can also specify two of the three available joins, which asks the query optimizer to exclude the third physical join operator from consideration. The decision between which of the remaining two joins to use will be cost-based. For example, take a look at the following unhinted query:

SELECT *

FROM Production.Product AS p

JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

This will produce...