Skip to main content

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.


Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error:    G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004  Insufficient result space to convert uniqueidentifier value to char.   Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID  column.
Post a Comment
Read more

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1:  Check if the server is available in the sys.servers table SELECT name from sys.servers Step2:  If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3:  Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.    
Post a Comment
Read more
Top 10 essential SQL skills for data analysts, data engineers, and data scientists Photo by  Carlos Muza  on  Unsplash It doesn’t matter how good you are at creating visualizations, extracting insights, or building complicated models; you need data in order to do those things. SQL enters the picture when it comes to extracting data from the database in order to work with it. Mastering the commonly used SQL concepts is essential to work effectively as a data analyst, data engineer, and data scientist in today's world.  The following are the top 10 skills for mastering SQL: 1. Knowing SQL Fundamentals:  Understanding the following keywords is essential to write common DML queries: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, WINDOW\RANK FUNCTIONS. 2. Understanding different real-world scenarios:  Analyzing and understanding multiple domains and how to use SQL, like writing complex queries, manipulating data, pulling reports, and identifying patterns and t...
Post a Comment
Read more