SQL Server Join Hints

Here is a link to a great article explaining SQL Server Join hints...the how, the when, and the why to use them.

http://www.mssqltips.com/sqlservertip/2917/sql-server-join-hints/

From the article-

"In summary, here's when to use the various types of join:

LOOP JOIN
Query has a small table on the left side of the join
One or both tables are indexed on the JOIN predicate

HASH JOIN
Tables are fairly evenly-sized or are large
Indexes practically irrelevant unless filtering on additional WHERE clauses, good for heaps
Arguably most versatile form of join

REMOTE JOIN
Same as hash join, but good where right side is geographically distant
Only suitable for INNER JOINs
Not suitable for local tables, will be ignored.

MERGE JOIN
Tables are fairly even in size
Works best when tables are well-indexed or pre-sorted
Uses very efficient sort algorithm for fast results
Unlike hash join, no memory reallocation, good for parallel execution

And if in doubt - let the optimizer decide!"