TSQL Execution Plan Operator - Nested Loop

SQL Server employs four types of physical join operations to carry out the logical join operations. Nested Loop join is one of them. So as the name implies it’s a nested loop which means It’s a loop inside a loop that is used to join records from 2 tables.

A nested loop join uses one join input as the outer input table and the other as the inner input table. Nested loop joins are highly effective if the outer input table has a smaller no of records and the inner input table is larger but indexed. In this video, we’ll be going to learn about this physical operator.

In this session, we’ll be going to learn about one of the T-SQL execution plan operators called Nested Loop. As a developer or as a DBA, you all are familiar with the types of join that SQL server has provided. By using which we can retrieve data from two or more tables based on logical relationships between them. SQL server employs four types of physical join operation to carry out the logical join operation. Those are Nested Loop Join, Hash Join, Merge Join and from SQL Server 2017, we have a new join called Adaptive Join.

What is a Nested Loop and how this physical operator works? As the name implies, it is a Nested Loop, which means it’s a loop inside a loop, which is used to join records from two tables. A Nested Loop join uses one join input as the outer input table and the other as the inner input table. So think of it this way, if we have a table A and table B, then what Nested Loop do? It loops through the (outer table) table A, picking up a record row by row. For each record in table A, it loops through (inner table) table B looking for matching rows. Nested Loop supports almost all types of join except Right Join and Full Outer Join. Nested Loop joins are highly effective if the outer input table has a smaller number of records and the inner input table has larger records but index records.

Programming Essentials C# Nested Loop

For this demo, we will start off with a query that joins the sales order header table with sales order detail on the sales order ID column. Now, execute the query

– Nested Loop Operator SELECT header.customerID, header.OrderDate, detail.ProductID, detail.UnitPrice, detail.CarrierTrackingNumber FROM sales.SalesOrderHeader AS header INNER JOIN sales.SalesOrderDetail detail ON header.SalesOrderID = detail.SalesOrderID WHERE header.CustomerID = 29565 GO

and looking at the Execution Plan tab. Here we can see that our query is using a Nested Loop Join. Now in terms of selection of the Nested Loop outer versus inner, our outer selection is the sales order header table and our inner selection is the sales order detail table.

When I hover over this (Index Seek, Nonclustered) operator, you can see the actual number of rows is 8. When I hover over this (Clustered Index Seek) operator, you can see the actual number of rows is 10. Basically, we are pulling just a few records from the sales order header table. Then using the index on the sales order detail table to find out the matching rows.

Programming Essentials C Sharp Nested Loop

If we go into the messages tab, you can see that to retrieve the matching rows, the query optimizer scans the sales order detail table 8 times. An index Nested Loop performs better than a Merge Join or Hash Join if a less number of records are involved. A Nested Loop Join can be fast because it uses to take a small set of data and compare it quickly to the second set of data.

For more videos and articles visit:

Useful Links:

TSQL Execution Plan Operator - Nested Loop

For more videos and articles visit:

Useful Links:

👉 GET YOUR FREE API KEY for PDF.co

✅ ON-PREMISE SDK FREE TRIAL:

🔎 SDK DOCUMENTATION:

🔑 WEB API DOCUMENTATION :