Reference Work Entry

Encyclopedia of Database Systems

pp 1895-1895

Nested Loop Join

  • Jingren ZhouAffiliated withMicrosoft Research

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.

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 the smaller relation R and to use one extra page as an input buffer to read in the larger relation S and the other ext ...

This is an excerpt from the content