Nested loop vs hash join vs merge join
For each outer row, look up matches in the inner table - ideal when the outer side is SMALL and the inner side has an index on the join key (each probe is an index seek). Degenerates catastrophically when the outer side is unexpectedly large: 2M outer rows x an inner probe each. The 'loops=2000000' plan node. The only strategy for non-equi joins (<, BETWEEN).
Build a hash table on the smaller input's join key, then stream the larger input probing it. The workhorse for large unsorted equi-joins with no useful index - two passes, no index needed. Needs memory for the build side (spills to batches if it exceeds work_mem); equality joins only. (MySQL only gained it in 8.0 - before that everything was nested loops.)
Walk two inputs sorted on the join key in lockstep - excellent when both sides are ALREADY sorted (clustered/covering indexes on the key) or when the output must be sorted anyway; huge joins stream with minimal memory. If inputs need explicit sorting first, that sort often erases the win.
You don't choose - the planner does, from row estimates; your levers are indexes, statistics, and query shape. Diagnostic map: tiny driving set + indexed lookup should be a nested loop; big-and-unindexed should hash; pre-sorted should merge. The classic incident is a bad row estimate steering the planner into a nested loop over millions of rows - the fix is ANALYZE (fresh stats) or the missing index, not query hints.