A block-nested loop (BNL) is an algorithm used to join two relations in a relational database.

This algorithm is a variation of the simple nested loop join and joins two relations will be scanned once for every tuple tuples, and particularly if there is no applicable index, this operation will be very expensive.

once for every group tuples. Here groups are disjoint sets and the union. For example, one variant of the block nested loop join reads an entire page tuples into memory and loads them into a hash table that are necessary.

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

that are necessary. In fact, this algorithm is essentially a special-case of the classic hash join algorithm.

fits in the available internal memory.

References