These are three ways a database can physically carry out a join. The SQL says what you want. The optimizer picks how.
Nested loop join
For each row in the outer table, look up matching rows in the inner table. Cheap when the outer side is small and the inner side has an index on the join key, so each lookup is fast. Terrible when both sides are large and there is no index, because it compares every row with every row.
Hash join
Build a hash table in memory from the smaller table (the build side), keyed by the join column. Then scan the bigger table once (the probe side) and look each row up in the hash table. Good for large, unsorted inputs with an equality condition. If the build side does not fit in memory it spills to disk and slows down.
Merge join (sort-merge)
Sort both inputs by the join key, then walk through them together like merging two sorted lists. Good when both inputs are already sorted, for example by an index, or when both are very large. If sorting is needed first, that costs time.
orders (2 TB) JOIN dim_customer (50 MB) -> hash join, build on dim_customer, probe with orders orders (10 rows after filter) JOIN order_items (indexed on order_id) -> nested loop with index lookups
What decides the choice
Table sizes after filters, whether the join keys are indexed or sorted, and the join type. Hash and merge joins only work for equality conditions. A non-equi join such as a.start < b.end usually forces a nested loop or a slower special plan.
How you see it
Run EXPLAIN (or the query profile in a warehouse). You will see node names like Nested Loop, Hash Join, Merge Join. In Spark the same ideas are called broadcast hash join (the small side is copied to every executor, like a hash join without a shuffle) and sort-merge join (shuffle both sides, sort, merge). A wrong choice is nearly always a symptom of bad row estimates, which statistics fix.