SQL Server 7.0: Nested-Loop Joins

SQL Server 7.0 supports three types of join operations:

In this article I want to tell you about Nested-Loop joins, what kinds
of Nested-Loop joins exist, and when SQL Server will choose this kind
of join operation.

If you join two tables, and SQL Server optimizer choose Nested-Loop
join, then one table will be selected as outer table and the other
as inner table. SQL Server will scan the outer table row by row and
for each row in the outer table, it will scan the inner table, looking
for matching rows.

Nested-Loop join will be chosen if one of the tables is small and
the other table has an index on the column that joins the tables.

This is the example:

USE pubs

GO

SET SHOWPLAN_TEXT ON

GO

SELECT a.au_id FROM authors a JOIN titleauthor b

   ON a.au_id = b.au_id

GO

SET SHOWPLAN_TEXT OFF

GO

These are the results:

StmtText

—————————————————————————-

SELECT a.au_id FROM authors a JOIN titleauthor b

   ON a.au_id = b.au_id

(1 row(s) affected)
StmtText

——————————————————————————————————————-

  |–Nested Loops(Inner Join)

       |–Clustered Index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]))

       |–Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [b]), SEEK:([b].[au_id]=[a].[au_id]) ORDERED)
(3 row(s) affected)

Because the query optimizer usually selects the best execution plan
for a given select statement, it is not necessary to change the kind
of join, but sometimes it can be useful. You can enforce the desirable
join type with OPTION clause.

This is the example to enforce Nested-Loop join:

USE pubs

GO

SET SHOWPLAN_TEXT ON

GO

SELECT s.qty, st.stor_name FROM sales s JOIN stores st

  ON s.stor_id=st.stor_id OPTION (LOOP JOIN)

GO

SET SHOWPLAN_TEXT OFF

GO

There are three variants of Nested-Loop join: