Nested Loops operator in SQL Server execution plan

 A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical joins between two sets of data. The SQL Server Optimizer may choose a different algorithm for different scenarios based on the requested query, available indexes, statistics and number of estimated rows in each data set.

Joins two tables by fetching the result from one table and querying the other table for each row from the first. SQL Server also uses the nested loops operation to retrieve table data after an index access. This operator is typically chosen when there are smaller result sets, or when one input is significantly smaller than the other. It’s a relatively simple and efficient join mechanism for small datasets or highly selective queries.

How Nested Loops Work

In a Nested Loops Join, SQL Server takes each row from the outer (top) input and matches it with rows from the inner (bottom) input. For each row in the outer input, it performs a lookup on the inner input to find matching rows.

Ø  Efficient for Smaller Data Sets: Nested Loops are ideal for scenarios where one input is small and/or both inputs are indexed.

Ø  Not Optimal for Large Tables without Indexes: On large datasets without indexing, a Nested Loop may become inefficient due to repeated scanning or seeking on the inner table.

Icon of Nested Loop Operator  

See the example

Run this query

SELECT OH.orderdate,        OD.orderqty,        OD.productid,        OD.unitprice FROM   sales.salesorderheader AS OH        JOIN sales.salesorderdetail AS OD          ON OH.salesorderid = OD.salesorderid WHERE  OH.orderdate BETWEEN '2010-07-01' AND '2011-07-31'

See the result.

See the execution plan

The above query return the small result set due to the Nested Loops is generated by execution plan. If it returns large dataset then it will generate merge join. See below we are running the same query but in the filter just we have increase the date due to that we are getting large dataset.

SELECT OH.orderdate,        OD.orderqty,        OD.productid,        OD.unitprice FROM   sales.salesorderheader AS OH        JOIN sales.salesorderdetail AS OD          ON OH.salesorderid = OD.salesorderid WHERE  OH.orderdate BETWEEN '2010-07-01' AND '2016-07-31'

See the result set

See the execution plan

Nested Loops (Left outer join)

If in the above query instead of join if we are using left join then execution plan generate Nested Loops (Left Outer Join), see below.

SELECT OH.orderdate,        OD.orderqty,        OD.productid,        OD.unitprice FROM   sales.salesorderheader AS OH      Left  JOIN sales.salesorderdetail AS OD          ON OH.salesorderid = OD.salesorderid WHERE  OH.orderdate BETWEEN '2010-07-01' AND '2011-07-31'

See the execution plan