If you need every possible row combination joined together, the Nested Loop is what you want. In most other cases, it's probably not. The classic pseudo code description of a Nested Loop join looks like the following:
for each outer row:
for each inner row:
if join condition is true:
output combined row
Both the inner and outer loops here could be executing against any of the scan types: sequential, indexed, bitmap, or even the output from another join. As you can see from the code, the amount of time this takes to run is proportional to the number of rows in the outer table multiplied by the rows in the inner. It is considering every possible way to join every row in each table with every other row.
It's rare to see a real ...
