The nested loops join, also called nested iteration, uses one join input as the outer input

table(shown as the top input in the graphical execution plan) and one as the inner (bottom)

input table.The outer loop consumes the outer input table row by row. The inner loop, executed

for each outerrow, searches for matching rows in the inner input table.In the simplest case,

the search scans anentire table or index; this is called a naive nested loops join.If the search

exploits an index, it is calledan index nested loops join.If the index is built as part of the query

plan (and destroyed upon completionof the query),it is called a temporary index nested loops join.

All these variants are considered by thequery optimizer.A nested loops join is particularly effective

if the outer input is quite small and the innerinput is PReindexed and quite large. In many small

transactions, such as those affecting only a small setof rows, index nested loops joins are far

superior to both merge joins and hash joins.In large queries,however, nested loops joins are

often not the optimal choice.【摘自technet】

嵌套循環連接，也被稱作嵌套迭代，用于連接外部輸入表（顯示為圖形執行計劃的頂部輸入）和內部輸入表的輸入

。外部循環按照行檢索外部輸入表。內部循環，執行外部的每一行，查詢內部輸入表匹配的行。最簡單的情況，查

詢掃描整個表或者索引；這叫做純嵌套循環連接。如果查詢利用了索引，被稱為索引嵌套循環連接。如果索引作為

查詢計劃的一部分被構建，這被稱作臨時索引嵌套循環連接。所有這些變體都會被查詢優化器考慮到。當外部輸入

非常小并且內部輸入很大且預先添加過索引的時候嵌套循環連接是非常有效的。在許多小的轉換中，比如僅僅影響

很小的行集的情況。索引嵌套連接是遠遠優于合并連接和哈希連接的。然而，在大數據的查詢情況下，嵌套循環連

接通常不是最好的選擇。