Difference between Nested Loop join and Sort Merge Join

Last Updated : 29 Jul, 2020

1. Nested Loop Join :
Nested Loop Join is the simplest join algorithm which usually has better performance than all other types of joins due to a lesser number of comparisons involved in it. Each row in the outer table is compared to each row in the inner table. The Nested Loop Join algorithm for 2 relations R and S :

For each record x of R read in, do
Use the index on B for S
Get all the matching records (having B=x.A)
End

2. Sort Merge Join :
The Sort Merge Join is the fastest join in case of sorted relations. This join is an internal join like the nested loop join. The algorithm cannot be seen or implemented we can only hint the engine to use this algorithm. This algorithm consists of the merge and sort phases, for the merging and sorting operations respectively. The algorithm for the Sorting Merge Join for 2 tables R and S are given below :

If R is sorted on A, S is sorted on B do
Merge R and S to get join result
End

Difference between Nested Loop Join and Sort Merge Join :

S.No.	Nested Loop Join	Sort Merge Join
1.	It is processed by forming an outer loop within an inner loop after which the inner loop is individually processed for the fewer entries that it has.	It is usually used to join two independent sources of data represented in a table.
2.	The nested join has the least performance in case of large tables.	It is better than nested join in case of performance in large tables.
3.	There are two phases in this, outer and inner loop processing.	It consists of 2 phases consisting the sort operation and merge operation.
4.	Steps involved include identifying an outer driving table and assigning the inner table to it, and processing the rows of inner table for every outer table row.	The first row from the first table and second row from the table is taken, if it is not the end then, the selected rows are checked for the merger. If they can be merged, the merged row is returned else next rows are taken from the tables and the steps are repeated until the rows are exhausted.
5.	It is not as fast as sort merge join in case of sorted tables.	It is the fastest join operation in case of sorted tables. This is because it uses merge phase and sort phase, where, if sort is already previously done, then merge is the fastest operation.
6.	It is of three types, namely, nested loop join, indexed nested loop join and Temporary indexed nested loop join.	It does not have further classifications.
7.	It is the most common type of join.	It is not as common as the nested loop join.
8.	Least number of comparisons are required in case of nested loop join.	It needs more comparisons than the nested loop join (generally).
9.	It is the fastest join algorithm due to least number of comparisons.	It is not as fast due to more number of comparisons.
10.	It is better than all other types of join for small transactions and small data.	It is not as good as nested loop join in case of smaller data.

Difference Between Right Join and Right Outer Join

supriya_saxena

Improve

Article Tags :

Difference between Nested Loop join and Sort Merge Join

Similar Reads

Thank You!

What kind of Experience do you want to share?