Index Join

Microsoft Jet chooses an index join when the inner table has an index on the joined field. The index join tends to be used when the outer table is small or data doesn’t have to be retrieved from the inner table. Index joins are often used for recordsets based on a table.

Algorithm for Performing an Index Join

1. Retrieve a value from the outer table.
   
   
2. Create a primary key for the inner table.
   
   
3. Search on the inner table to find matches, based on the key created in step 2.
   
   
4. Check the remaining restrictions for each match.

Following is an example of an index join:

SELECT 
	Products.ProductID, 
	Orders.OrderDate, 
	Sum(CLng([Order Details].UnitPrice*Quantity*(1 - Discount)*100))/100 
		AS ProductAmount
FROM Orders 
	INNER JOIN (Products 
		INNER JOIN [Order Details] 
		ON Products.ProductID = [Order Details].ProductID) 
	ON Orders.OrderID = [Order Details].OrderID
GROUP BY Products.ProductID, Orders.OrderDate;