sort merge join, hash join, nested loop join

nested loop join:
can use for small data set.
easy to use but scan every row to get the match record, performance will be degrade when tables are big

short merge join:
work well for large tables but data should be sorted (data should be bucketed) but sorting is expensive operation.

hash join:
work well for large tables but need extra memory for hash table.
hash table group the record for same id and create a hash key