Synonyms
Loop join; Nested 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 \(\mathcal {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 for each R ∈ R do for each 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...
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Recommended Reading
Mishra P, Eich MH. Join processing in relational databases. ACM Comput Surv. 1992;24(1):63–113.
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Section Editor information
Rights and permissions
Copyright information
© 2018 Springer Science+Business Media, LLC, part of Springer Nature
About this entry
Cite this entry
Zhou, J. (2018). Nested Loop Join. In: Liu, L., Özsu, M.T. (eds) Encyclopedia of Database Systems. Springer, New York, NY. https://doi.org/10.1007/978-1-4614-8265-9_868
Download citation
DOI: https://doi.org/10.1007/978-1-4614-8265-9_868
Published:
Publisher Name: Springer, New York, NY
Print ISBN: 978-1-4614-8266-6
Online ISBN: 978-1-4614-8265-9
eBook Packages: Computer ScienceReference Module Computer Science and Engineering