SQL Server Join Hints

JOIN hints can be used in a query to specify the type of JOIN the Query Optimizer is to use for the execution plan. The JOIN options are:

Loop
Merge
Hash
The syntax for a JOIN hint is (using an INNER JOIN as an example):

FROM table_one INNER [LOOP | MERGE | JOIN] JOIN table_two

Here's an example:

FROM header_table INNER LOOP JOIN detail_table

As you can see, the JOIN hint is between the type of JOIN (in this example, INNER) and the JOIN keyword. Only one JOIN hint can be used per JOIN in a query. In addition, JOIN hints can only be used when the ANSI JOIN syntax is used, not the older Microsoft JOIN syntax.

The syntax above is not the only option to add a JOIN hint to a query. In addition, you can also use OPTION clause. Using the OPTION clause specifies that the hint be used throughout all of the query. While multiple hints can be added to the OPTION clause, each query hint can be used only once. In addition, only one OPTION clause can be used per query.

Here's an example of using the OPTION clause:

OPTION (INNER) or OPTION (MERGE) or OPTION (HASH)

The Query Optimizer always tries to identify the fastest way to JOIN tables. The fastest JOIN method is the Loop JOIN, followed by the Merge and the Hash JOIN. While the Query Optimizer always tries to perform a Loop JOIN if possible, it is not always possible, and one of the other two types may have to be used.

Before you attempt to use JOIN hint to specify a JOIN type, you should first take a look at your query, and the indexing of the relevant tables, to see if you can find a way, other than using a hint, to induce the Query Optimizer to use a Loop JOIN. Keep in mind that if you specify a Loop JOIN hint for a JOIN that is currently using a HASH JOIN, that you may get worse, not better performance. So always test JOIN hints to see what their end result in performance really is.

Occasionally, you may find yourself having to perform an INNER JOIN on tables located in databases on different servers. Generally speaking, SQL Server will evaluate if the performing the JOIN will perform better on the local or remote server, and run the JOIN their accordingly. But in some cases, such as when the remote table is not a SQL Server table, this is not possible.

SQL Server provides a special JOIN hint that you can use to force a JOIN to occur on the remote server, if this is appropriate, called REMOTE. The REMOTE hint is used to specify that the JOIN operation be performed on the right table of the JOIN. You might consider this if the left table of the JOIN is the local table and the right table in the JOIN is the remote table. Of course, doing this only makes sense if the right table has more rows that the left table. REMOTE cannot be used if the tables use different collation.