NESTED LOOP JOIN

NESTED LOOP JOIN, the simplest join strategy, is a binary operator with the left child forming the outer data stream and the right child forming the inner data stream.

For every row from the outer data stream, the inner data stream is opened. Often, the right child is a scan operator. Opening the inner data stream effectively positions the scan on the first row that qualifies all of the searchable arguments.

The qualifying row is returned to the NESTED LOOP JOIN’s parent operator. Subsequent calls to the join operator continue to return qualifying rows from the inner stream.

After the last qualifying row from the inner stream is returned for the current outer row, the inner stream is closed. A call is made to get the next qualifying row from the outer stream. The values from this row provide the searchable arguments used to open and position the scan on the inner stream. This process continues until the NESTED LOOP JOIN’s left child returns End Of Scan.

 -- Collect all of the title ids for books written by "Bloom".
select ta.title_id
from titleauthor ta, authors a
where a.au_id = ta.au_id
and au_lname = "Bloom"
go
	
QUERY PLAN FOR STATEMENT 1 (at line 2).

STEP 1
The type of query is SELECT.

3 operator(s) under root

ROOT:EMIT Operator (VA = 3)

	|NESTED LOOP JOIN Operator (Join Type: Inner Join)
	|
	|   |SCAN Operator (VA = 0)
	|   |  FROM TABLE
	|   |  authors
	|   |  a
	|   |  Index : aunmind
	|   |  Forward Scan.
	|   |  Positioning by key.
	|   |  Keys are:
	|   |    au_lname ASC
	|   |  Using I/O Size 2 Kbytes for index leaf pages.
	|   |  With LRU Buffer Replacement Strategy for index leaf pages.
	|   |  Using I/O Size 2 Kbytes for data pages.
	|   |  With LRU Buffer Replacement Strategy for data pages.
	|
	|   |SCAN Operator (VA = 1)
	|   |  FROM TABLE
	|   |  titleauthor
	|   |  ta
	|   |  Using Clustered Index.
	|   |  Index : taind
	|   |  Forward Scan.
	|   |  Positioning by key.
	|   |  Keys are:
	|   |    au_id ASC
	|   |  Using I/O Size 2 Kbytes for data pages.
	|   |  With LRU Buffer Replacement Strategy for data pages.

The authors table is joined with the titleauthor table. A NESTED LOOP JOIN strategy has been chosen. The NESTED LOOP JOIN operator’s type is “Inner Join.” First, the authors table is opened and positioned on the first row (using the aunmind index) containing an l_name value of “Bloom.” Then, the titleauthor table is opened and positioned on the first row with an au_id equal to the au_id value of the current authors’ row using the clustered index “taind.” If there is no useful index for lookups on the inner stream, the optimizer may generate a reformatting strategy.

Generally, a NESTED LOOP JOIN strategy is effective when there is a useful index available for qualifying the join predicates on the inner stream.