Nested Iteration Join

The nested iteration join is a brute force method and is used only as a final attempt at performing a join. A nested iteration is performed only when the tables contain few records and probably no useful indexes.

Algorithm for Performing a Nested Iteration Join
  1. Pair each record in the outer table with a record in the inner table.

  2. Check each pair to make sure it meets the join restrictions.

    If a record matches the restriction, it’s kept for the result set; otherwise, it’s discarded.

Following is an example of a nested iteration join:

SELECT 
	Categories.CategoryName, 
	Products.ProductName, 
	Categories.Description, 
	Categories.Picture, 
	Products.ProductID, 
	Products.QuantityPerUnit, 
	Products.UnitPrice, Products.Discontinued
FROM Categories 
INNER JOIN Products 
ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued = False
ORDER BY Categories.CategoryName, Products.ProductName;