Specify build and probe tables to hash join?

Question

I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not able to understand why this reverse mapping is favoured by SQL server.

Following is the query and plan:

SELECT *
FROM HumanResources.Employee e
INNER MERGE JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER HASH JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID

The plan is here: https://www.brentozar.com/pastetheplan/?id=Bk_YvD1iX

I wonder if I can specifically instruct SQL to use HumanResources.Shift for build and the other input to probe?

Answer 1

When you hint a query like that, you force the join order -- you've actually answered your own question.

If you want to test join types and still leave the optimizer with room to explore things, use query options, like this:

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh 
ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s 
ON edh.ShiftID = s.ShiftID
OPTION(MERGE JOIN, HASH JOIN);