 Hi there, this is Krish Raj from Bitescout and in this video, we will be going to learn about one of the T-SQL execution plan operator called NETRID loop. Well, as a developer or as a DBA, we all are familiar with the types of join that SQL server has provided, using which we can retrieve data from two or more tables based on logical relationship between them. SQL server employs four type of physical join operation to carry out the logical join operation and those are nested loop join, hash join, merge join and from SQL server 2017, we have a new join called adaptive join. So what is nested loop and how this physical operator works? Well, 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 this way, say if we have a table A and table B then what nested loop does is it loops through the outer table which is table A, picking up a record row by row and for each record in table A it loops through the inner table table B looking for a matching rows. So 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 smaller number of records and the inner input table has larger records but indexed records. Now let's see this join in action. All right, so for this demo, we will start off with a query that joins the cells order header table with cells order detail on cells order ID column. Now let's execute this query and looking at the execution plan in this tab. So 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 cells order header table and our inner selection is cells order detail table. Okay, so now when I whore over this operator, you can see that the actual number of rows is 8 which is coming out of this clustered index 6 and when I whore over this operator, you can see that here the actual number of rows is 10. So basically we are pulling just a few records from the cells order header table and then using the index on cells order detail table to find out the matching rows. Now if we go into the message tab here you can see that to retrieve the matching rows query optimizer scans the cells order detail table 8 times. So an index nested loop performs better than a merge join or has joined 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 a second set of data. All right, now in the next video we'll be going to learn about the next physical operator called merge join. So stay tuned with us.