Nested Loop Join

Synonyms

Nested loop join; Loop join

Definition

The nested loop join is a common join algorithm in database systems using two nested loops. The algorithm starts with reading the outer relation R, and for each tuple ${\cal R}\in R$, the inner relation S is checked and matching tuples are added to the result.

Algorithm 1: Nested Loop Join: R⋈ pred(r, s) S

foreach R ∈ R do

foreach S ∈ S do

if pred (R.r, S.s) then

add {R, S} to result

end

end

end

Key Points

One advantage of the nested loop join is that it can handle any kind of join predicates, unlike the sort-merge join and the hash join which mainly deal with an equality join predicate. An improvement over the simple nested loop join is the block nested loop join which effectively utilizes buffer pages and reduces disk I/Os.

Block Nested Loop Join

Suppose that the memory can hold B buffer pages. If there is enough memory to hold the smaller relation, say R, with at least two extra buffer pages left, the optimal approach is to read in...