Applicable situations of nested loops, merge, and hash

1. Applicability of nested loop connections (nested loops)
Two tables: An External table and an internal table.

If the external input is very small and the internal input is very large and an index has been created in advance, nested loop join will be particularly efficient.

For which table is the outer table and the inner table during the connection, I found that SQL server will automatically arrange it for you, and it has nothing to do with the position you write, it automatically selects a table with a small amount of data as an outer table, and a table with a large amount of data as an inner table.

2. Merge join (merge)
It means that both tables have indexes in the on filter conditions and are ordered. In this way, SQL server uses merge join for join, which improves performance.

If an index exists and no index exists, the nested loops join is selected.
 

3. Hash join)
If neither of the two tables has an index in the on filter condition, hash join is used.

That is, use Hash joinAlgorithmIt is due to the lack of ready-made indexes.

References:

Inside SQL Server 2008 T-SQL.

Advanced query optimization concepts
Http://msdn.microsoft.com/zh-cn/library/ms191426 (V = SQL .100). aspx