Nested loop

If you need every possible row combination joined together, the nested loop is what you want. In most other cases, probably not. The classic pseudocode 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 nested loop without an inner Index Scan, the type covered in the next section. Joining data using merges and hashes are normal for the real world that tends to be indexed or have a clear relationship between tables. You can see one if you...