Join Hints (Transact-SQL)
Join hints specify that the query optimizer enforce a join strategy between two tables in SQL Server 2012. For general information about joins and join syntax, see FROM (Transact-SQL).
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints, including <join_hint>, be used only as a last resort by experienced developers and database administrators.
Join hints are specified in the FROM clause of a query. Join hints enforce a join strategy between two tables. If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords. When a CROSS JOIN is used without the ON clause, parentheses can be used to indicate the join order.
A. Using HASH
The following example specifies that the JOIN operation in the query is performed by a HASH join.
USE AdventureWorks2012; GO SELECT p.Name, pr.ProductReviewID FROM Production.Product AS p LEFT OUTER HASH JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID ORDER BY ProductReviewID DESC;
B. Using LOOP
The following example specifies that the JOIN operation in the query is performed by a LOOP join.
USE AdventureWorks2012; GO DELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh INNER LOOP JOIN Sales.SalesPerson AS sp ON spqh.SalesPersonID = sp.SalesPersonID WHERE sp.SalesYTD > 2500000.00; GO
C. Using MERGE
The following example specifies that the JOIN operation in the query is performed by a MERGE join.
USE AdventureWorks2012; GO SELECT poh.PurchaseOrderID, poh.OrderDate, pod.ProductID, pod.DueDate, poh.VendorID FROM Purchasing.PurchaseOrderHeader AS poh INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod ON poh.PurchaseOrderID = pod.PurchaseOrderID; GO