Index-Merge Join

An index-merge join can be used when each input is a table in native Microsoft Jet database format. Each input must have an index on its join field, and at least one of the indexes must not allow Null values if there is more than one join field.

Algorithm for Performing an Index-Merge Join

1. In the current record, move to the next record where the foreign key is greater than or equal to the primary key.
   
   
2. If equal, return the record.
   
   
3. If greater, advance the outer input one record.
   
   
4. Move backward the number of matches for the previous key on the inner input if the outer input reference isn’t unique and matches the current inner input.

Following is an example of an index-merge join:

SELECT 
	[Order Details].OrderID, 
	Products.ProductName, 
	[Order Details].ProductID, 
	[Order Details].UnitPrice
FROM Products 
INNER JOIN [Order Details] 
ON Products.ProductID = [Order Details].ProductID;