Join Hint (Transact-SQL)

Specifies that query optimizer enforce a join strategy between two tables.

Applies to:

DELETE

SELECT

UPDATE

Transact-SQL Syntax Conventions

Syntax

<join_hint> ::= 
     { LOOP | HASH | MERGE | REMOTE }

Arguments

• LOOP | HASH | MERGE
  Specifies that the join in the query should use looping, hashing, or merging. Using LOOP |HASH | MERGE JOIN enforces a particular join between two tables. LOOP cannot be specified together with RIGHT or FULL as a join type.

• REMOTE
  Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.

  If the right table is local, the join is performed locally. If both tables are remote but from different data sources, REMOTE causes the join to be performed on the site of the right table. If both tables are remote tables from the same data source, REMOTE is not required.

  REMOTE cannot be used when one of the values being compared in the join predicate is cast to a different collation using the COLLATE clause.

  REMOTE can be used only for INNER JOIN operations.

Remarks

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.

Examples

A. Using HASH

The following example specifies that the JOIN operation in the query is performed by a HASH join.

USE AdventureWorks;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER HASH JOIN Production.ProductReview 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 AdventureWorks;
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 AdventureWorks;
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

See Also

Reference

Hints (Transact-SQL)

Other Resources

Understanding Hash Joins
Understanding Nested Loops Joins
Understanding Merge Joins
Join Fundamentals
Using Joins

Help and Information

Getting SQL Server 2005 Assistance

Change History