Bert Wagner walks us through nested loop joins:
Nested loops joins work like this: SQL Server takes the first value from our first table (our “outer” table – by default SQL Server decides for us which table of the two this will be), and compares it to every value in our second “inner” table to see if they match.
Once every inner value has been checked, SQL Server moves to the next value in the outer table and the process repeats until every value from our outer table has been compared to every value in our inner table.
This description is a worst case example of the performance of a nested loop join.
Read the whole thing. Understanding physical join operators is a key to figuring out if your data retrieval is as fast as it should be.