Nested loop join

A nested loop join is a naive algorithm that joins two relations by using two nested loops.^[1] Join operations are important for database management.

Two relations ${\displaystyle R}${\displaystyle R} and ${\displaystyle S}${\displaystyle S} 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 n_r*b_s+ b_r block transfers and n_r+b_r seeks, where b_r and b_s are number of blocks in relations R and S respectively, and n_r is the number of tuples in relation R.

The algorithm runs in ${\displaystyle O(|R||S|)}${\displaystyle O(|R||S|)} I/Os, where ${\displaystyle |R|}${\displaystyle |R|} and ${\displaystyle |S|}${\displaystyle |S|} is the number of tuples contained in ${\displaystyle R}${\displaystyle R} and ${\displaystyle S}${\displaystyle S} respectively and can easily be generalized to join any number of relations ...

The block nested loop join algorithm^[2] is a generalization of the simple nested loops algorithm that takes advantage of additional memory to reduce the number of times that the ${\displaystyle S}${\displaystyle S} 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.

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>

The time complexity for this variation improves from ${\displaystyle O(|R||S|){\text{ to }}O(|R|\log |S|)}${\displaystyle O(|R||S|){\text{ to }}O(|R|\log |S|)}

• Hash join
• Sort-merge join

• Join algorithms
• Computer science stubs

• Articles with short description
• Short description is different from Wikidata
• Articles needing additional references from January 2021
• All articles needing additional references
• All stub articles