Nested LOOP ( e.g : 260 rows * 1 rows)
- FOR (small set data)
FOR(big set data)
Hash Join (e.g : 190 rows * 19000 rows)
- build input - small data data - Join column will be hashed
- probe input - bigger data set
Merge Join (e.g : 246 rows * 246 rows)
- both input need to pre-sorted, it is better to have index on join column since index is pre-sorted
otherwise, merge join will increase read operation