Here's a little video I made about the types of joins from a conceptual point



In terms of the optimizer decisions, it is the same as *any* optimizer decision, namely:

- evaluate the estimated cost (resource consumption) and hence estimated elapsed time of each potential strategy
- pick the one that looks cheapest

To understand a driving table, recall that a nested loop is:

A- read one table
B- use the data from that table to perform a lookup into a second table

The table that you read from initially (A) is the "driving" table.