Type of Join Hints in SQL Server

In my previous post, we have gone through about the basic of SQL Server Hints. Now here we are moving with type of Join Hints.

1. LOOP:- Loop Join also known as "NESTED JOIN". This type of join made up by an outer loop and an inner loop. When the query runs for each row of the outer loop, the inner loop is executed completely. This join is effective only when the outer loop query is small and the inner loop query has all the proper optimizations applied. This join method is very useful with small transactions.
   
   
2. HASH: - Hash Join works with large data set. It does not require ordered result set to join. Hash join requires equijoin predicate to join tables. Equijoin predicate is comparing values between one table to other table using “equals to” (“=”) operator. Hash join gives best performance when two more join tables are joined and at-least one of them have no index or is not sorted. It is also expected that smaller of the either of table can be read in memory completely (though not necessary).
   
   
   The query optimizer makes a Hash join in two phases: build and probe. So, Hash join has two inputs: the build input and the probe input.
   
   
3. MERGE: - The merge join requires that both inputs be sorted on the merge columns.This join keeps both of the tables sorted in parallel and compares each table row by row simultaneously with each other. It compares one row of the first table with one row of the second table. If they are equal, that row qualifies; otherwise, whichever row has the lower value is discarded and another row is obtained from that input. This process repeats until all rows have been processed.
   
   
   Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained pre-sorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
   
   
4. 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.