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.
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;