NESTED LOOPS JOIN v7.0
There are three join processing strategies in SQL Server v7.0. These 
are the Nested Loops Join, the Merge Join, and the Hash Join. The most 
familiar join (because it was used in SQL Server v6.5) is the Nested 
Loops Join. This type of join requires that two loops be formed based 
upon the join criteria in the SQL statement--one loop as an inner loop 
and one as the outer loop. The outer loop is processed one row at a 
time and then the inner loop is searched for the value obtained from 
the outer loop.
An example may make this more understandable. Assume the following SQL 
statement and data set.SELECT a.CName, b.PName FROM Client a, Project b  
WHERE a.CID = b.CID  Client              Project CID CName           CID  Pname 
--- ----            ---  ----- 1   KFC              1    Project X 
2   AOL             3    Project Y 3   WCOM
You can quickly see the result set that would be returned, but observe 
how SQL Server solves this problem.
Assume the Client table is loop #1 (inner) and the Project table is 
loop #2 (outer). First, SQL forms the two inputs as loops. It takes the 
first row of the outer set and searches for the CID in the inner set 
for a match. If a match were found (which in this case, two are), this 
would be returned in the result set. SQL then proceeds to the next 
value in the outer set and then continues to search the inner set again 
for a match.
This process continues until all the rows from the outer loop have been 
processed, thus returning the following:CName    Pname -----    ----- 
KFC      Project X WCOM     Project Y
This type of join can be expensive when you have a large volume of 
information that is being joined. Largely, this join type works well if 
your tables are indexed properly and data sets are small. As sets begin 
to increase in size, I/O increases, which will reduce the effectiveness 
of this strategy.------------------------------------------