SELECT O.[OrderId], O.[CustomerId] FROM [Customers] C JOIN [Orders] O JOIN [Employees] E ON O.[EmployeeId] = E.[EmployeeId] ON C.[CustomerId] = O.[CustomerId] WHERE C.[City] = N'London' AND E.[City] = N'London' OPTION (FORCE ORDER, HASH JOIN)
__________________________________________________
The force order hint, instructs sql server the order that the join should be resolved. On join resolution process, sql server analyzes the number of rows on each table and decides which one will be the inner table, and the outer table. With force order sql server will use the order in the query. It could be good in some cases, when the optimizer cannot detect the best plan, but could be bad, because you can mislead the optimizer best choice. Besides the HASH JOIN hint forces the use o a given method, you can use LOOP, HASH or MERGE.
Again, the choice for a method to solve the join depends on cardinality factors that sql server analyzes using the statistics and available indexes.
