Nested Loop, Hash Join, Merge Join

These are ways of executing a join, not equivalents of logical INNER or LEFT JOINs.

• Nested Loop: for rows on one side it looks for matches on the other; it can be great for a small input and a good index.
• Hash Join: builds a hash table for one side and looks up matches, usually for equality.
• Merge Join: walks through data ordered by the join keys; the cost of sorting matters too.

In practice: I look at row counts, loops, memory and spills to disk in the plan.

Pitfall: there's no single method that's always best. Changed statistics or data sizes can justify a different plan.