Nested Join, Hash Join and Merge Join in SQL

I also faced similar problems while I was studying and consulting different books got the answer. The system scans the entire inner-join relation for each tuple in the outer join relation in nested joins and appends any tuples that match the join-condition to the result set.

 

If both join relations are sorted by the join attribute(s) and come in order, the system performs the join trivially in Merge joins. Current group of tuples from the inner relation with a set of contiguous tuples in the inner relation with the same value is considered in the join attribute.

 

For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group. Hash join A hash join algorithm can only produce equi-joins.

 

The database system pre-forms access to the tables concerned by building hash tables on the join-attributes.

A join is whenever the two inputs are compared to determine and output. There are three basic types of strategies for this and they are: nested loops join, merge join and hash join. When a join happens the optimizer determines which of these three algorithms is best to use for the given problem, however any of the three could be used for any join. All of the costs related to the join are analyzed the most cost efficient algorithm is picked for use. These are in-memory loops used by SQL Server.

 

Nested Join

 

If you have less data this is the best logic. It has two loops one is the outer and the other is the inner loop. For every outer loop, its loops through all records in the inner loop. You can see the two loop inputs given to the logic. The top index scan is the outer loop and bottom index seek is the inner loop for every outer record. It’s like executing the below logic:-

So you visualize that if there fewer inner records this is a good solution.

 

Hash Join

 

Hash join has two input “Probe” and “Build” input. First the “Build” input is processed and then the “Probe” input. Which ever input is smaller is the “Build” input. SQL Server first builds a hash table using the build table input. After that he loops through the probe input and finds the matches using the hash table created previously using the build table and does the processing and gives the output.

 

Merge Join

 

In merge joins both the inputs are sorted on the merge columns. Merge columns are determined depending on the inner join defined in SQL. Since each input join is sorted merge join takes input and compares for equality. If there is equality then matching row is produced. This is processed till the end of rows.