what are Hash, Nested loop and merge-sorted Join.

In hash join hash table gets build using the inputs. Nested loops as the name implies uses looping construct strategy for storing and retrieving. In terms of performance comparison while retrieving data from both strictures hash join is best in terms of number of input and output operations and number of comparisons for retrieving data. But if the data to be retrieved is among the first few searches then the nested loop takes its win over the stake.

There are three types of alogrithm used by SQL compiler for joining purpose:
1) Nested join algo
2) Hash join algo
3) Merge join algo

Complier choose any one of these algo based on the best suited for performace.

1) Nested join is the used in most of the cases by the compiler
2) Hash join is used in large result set.
In this case first hash key is generated then the match is find based on the hash key.
3) Used in the cases where both the list are in sorted order.