set statistics profile on
Consider this query:
from Sales S inner join Customers C
on S.Cust_Id = C.Cust_Id
I’ve added a “loop join” hint to force the optimizer to use a nested loops join. We get this plan which I captured by running the query with “set statistics profile on”:
|3||1|||–Nested Loops(Inner Join, WHERE:([C].[Cust_Id]=[S].[Cust_Id]))|
|3||1|||–Table Scan(OBJECT:([Customers] AS [C]))|
|12||3|||–Table Scan(OBJECT:([Sales] AS [S]))|
The outer table in this plan is Customers while the inner table is Sales. Thus, we begin by scanning the Customers table. We take one customer at a time and, for each customer, we scan the Sales table. Since there are 3 customers, we execute the scan of the Sales table 3 times. Each scan of the Sales table returns 4 rows. We compare each sale to the current customer and evaluate whether the two rows have the same Cust_Id. If they do, we return the pair of rows. We have 3 customers and 4 sales so we perform this comparison a total of 3*4 or 12 times. Only 3 of these comparisons result in a match.
Simply put, a join is an operation that links one table to another, and SQL Server can use three algorithms to perform this operation, the Loop, Merge and Hash.
The merge join performs an inner join, an outer join or in some cases even a union operation. The merge join is very fast because it requires that both inputs are already sorted by the respective key columns. To do an analogy with the joins I’ll use the same example that I like to show in my presentations.