How does a Nested Loops join work?

Nested Loops takes rows from an outer input and, for each row, searches or scans the inner input for matching rows.

It can be extremely efficient when the outer input is small and the inner input has a useful index. It can become expensive when the outer input unexpectedly contains many rows and each row causes additional inner-table work.

When diagnosing a slow Nested Loops plan, inspect actual row counts, inner seeks/lookups, and whether the optimizer underestimated the outer input.