Differentiate between Nested loop, Hash loop and Merge Join.


Posted On: Feb 22, 2018


Nested loop- A nested loop is a loop within a loop. It is an inner loop within an outer body that allows fewer entries. Individual entries in nested loop is processed individually in the inner loop. Example-

.Select col.*, col2.* from coll, col2 where coll.coll=col2.col2;

The nested way works in following format-

For i in (select*from coll) loop

For j in (select*from col2 where col2 = i.coll) loop

Results are displayed

End of the loop;

End of the loop;

For nested loop, first outer (driving) table is identified. Then inner tavle is assigned to the outer table and every row in inner column is accessible from every row of outer table. Nested loop is executed from hash join, inner loop and then outer loop.

Hash join- it is divided into-

  • Build- this hash table has in-memory which is present on the smaller table.
  • Probe – it is the hash value present in each second row element.
  • Sort merge element- it joins two independent sources of data.

It is considered a better option as compared with nested loop when data volume is big. The full operation of hash join can be classified as-

Sort join operation-

Get first row R1 from input1

Get first row R2 from input2

Merge join operation- 'while' command is not used at loop's end.

If R1 joins with R2

Next row is got R2 from the input2

Return  (R1,R2)

else if R1 ,style="". Next row is got from R1 from



next row is got from R2 from input 2

end of the loop.

    Related Questions

    Please Login or Register to leave a response.

    Related Questions

    DBMS Interview Questions

    What is DBMS?

    DBMS (Database Management System) is a computer software application that allows users and other applications to view,..

    DBMS Interview Questions

    Enlist various types of interactions created by DBMS ?

    There are various kinds of interactions supported by DBMS like- Data definition Update Retrieval Administration..

    DBMS Interview Questions

    What are the features of Database language?

    Database language is used to create and store data in computer system. DBMS itself is one of the features of database l..