|
The Nested Loops Join computes the join of its left and right sides by completely reading the right-hand side for each row of the left-hand side. (The syntactic order of tables in the query does not matter because the optimizer chooses the appropriate join order for each block in the request.)
The optimizer may choose a Nested Loops Join if the join condition does not contain an equality condition, or if the statement is being optimized with a first row optimization goal (that is, either the optimization_goal option is set to First-Row, or FASTFIRSTROW is specified as a table hint in the FROM clause.
Since a Nested Loops Join reads the right-hand side many times, it is very sensitive to the cost of the right-hand side. If the right-hand side is an index scan or a small table, then the right-hand side can likely be computed using cached pages from previous iterations. On the other hand, if the right-hand side is a sequential table scan or an index scan that matches many rows, then the right-hand side needs to be read from disk many times. Typically, a Nested Loops Join is less efficient than other join methods. However, Nested Loops Join can provide the first matching row quickly compared to join methods that must compute their entire result before returning.
The Nested Loops Join algorithm is the only join algorithm that can provide sensitive semantics for queries containing joins. This means that sensitive cursors on joins can only be executed with a Nested Loops Join.
A Semijoin fetches only the first matching row from the right-hand side. It is a more efficient version of the Nested Loops Join, but can only be used when an EXISTS, or sometimes a DISTINCT, keyword is used.