What is a Nested Loop Join in SQL Server: Understanding the Execution Plan

Nested Loop Join in SQL Server Execution Plan

🧩 What Is a Nested Loop Join?

A Nested Loop Join is a physical join operator used by SQL Server to combine rows from two input tables based on a specified join condition. It is best suited when:

• The outer input (typically the smaller dataset) is small.

• The inner input is indexed on the join key.

This operator iteratively compares each row from the outer table with matching rows from the inner table, evaluating the join condition each time.

---

⚙️ How It Works – Execution Flow

1. Read the first row from the outer input (top table).

2. Use the join key from this row to search (seek/scan) the inner input (bottom table) for matching rows.

3. If matches are found, combine the outer and inner rows and return them as part of the result.

4. Repeat this process for each row in the outer input.

---

🧱 Understanding Outer and Inner Inputs

 

Outer Table	The input table processed first. SQL Server loops through each row in this table.
Inner Table	For each outer row, SQL Server scans or seeks this table for matching records.
Result Set	The output of the join — it includes columns from both tables for only those rows               where        the join condition is met.

---

🧪 Example: Nested Loop Join in Action

Let’s consider a simple join between an Employees table and a Department table, based on a matching DeptID.

Tables:

dbo.Employees (Outer Table)

dbo.Department (Inner Table)

 

Join Condition:

sql
ON Employees.DeptID = Department.DeptID

---

🔄 Step-by-Step Join Process (Nested Loop)

1. Row 1 from Employees: (DeptID = 1, FirstName = Reneesh)
   → Compare with all rows in Department.
   → Match found: (DeptID = 1, DeptName = AI)
   → Add to result: (1,Reneesh, AI)

2. Row 2 from Employees: (DeptID = 2, FirstName = Sandhya)
   → Compare with all rows in Department.
   → Match found: (DeptID = 2, DeptName = DW)
   → Add to result: (2,Sandhya, DW)

3. Row 3 from Employees: (DeptID = 1, FirstName = Rashmi)
   → Match found again: (DeptID = 1, DeptName = AI)
   → Add to result: (1,Rashmi, AI)

---

📌 Summary

• Nested Loop Join is efficient for small outer inputs and indexed inner inputs.

• It is commonly used in key lookups, small lookups, and OLTP workloads.

• For large datasets, consider other join types like Merge Join or Hash Match.