A nested loop join is a naive algorithm that joins two relations by using two nested loopss. Join operations are important for database management.

Algorithm

are joined as follows:

algorithm nested_loop_join is
    for each tuple r in R do
        for each tuple s in S do
            if r and s satisfy the join condition then
                yield tuple <r,s>

This algorithm will involve nr*bs+ br block transfers and nr+br seeks, where br and bs are number of blocks in relations R and S respectively, and nr is the number of tuples in relation R.

respectively and can easily be generalized to join any number of relations ...

The block nested loop join algorithm is a generalization of the simple nested loops algorithm that takes advantage of additional memory relation is scanned. It loads large chunks of relation R into main memory. For each chunk, it scans S and evaluates the join condition on all tuple pairs, currently in memory. This reduces the number of times S is scanned to once per chunk.

Index join variation

If the inner relation has an index on the attributes used in the join, then the naive nest loop join can be replaced with an index join.

algorithm index_join is
    for each tuple r in R do
        for each tuple s in S in the index lookup do
            yield tuple <r,s>

See also

• Hash join

• Sort-merge join

References