Use the nested loop if you need every possible row combination joined together. In most other cases, it's probably not the best option. The classic pseudocode description of a nested loop join looks like this:
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 nested loop without an inner-index scan, the type covered in the next section. Joining using merges and hashes is normal for real-world data that tends to be indexed or has a clear relationship between tables. You can see one if you just forget to put a WHERE condition on a join though, which then evaluates the cross product and outputs a ton of rows. Here's one that produces by far the longest runtime of a query in this chapter:
That's a 19-minute runtime, which sounds like a lot until you realize it's producing 200 million output rows. That works out to 175K row/second being processed, not really that shabby.
Note that a Nested Loop is the only way to execute a CROSS JOIN, and it can potentially be the only way to compute complicated conditions that don't map into either a useful merge or hash join instead.