 Hello, welcome to SSUnitech, Sushil this site and this is continuation of SQL Server performance training. So today we are going to discuss about physical joints. So in SQL Server we have two types of joints. First is physical joint, second is logical joint. So in logical joint we will have inner joint, outer joint and cross joint. But in physical joint we have three types of joints. Nuster loop joint, merge joint and hash joint. So we will discuss about physical joints in this video. So what is physical joint? So how SQL Server can join two or more than two tables internally? So this is physical joints. So as I told you it has three types of joints. Nuster loop joint, merge joint and hash joint. So today we will discuss about nested loop joint. In next video we will discuss about merge joint. And in our upcoming video we will discuss about hash joint as well. So let's start with nested loop joint. So basically in SQL Server optimizer might choose nested loop joint when one of the joining table is small and other is large. Next condition is small table will be considered as outer table. And last table will be considered as inner table. Next inner table should have proper index on the joining column. This is the criteria for nested loop joint. So first we will have two tables. Second condition first table will be small table and second will be last table. Then last table will have the proper index on the joining column. So this is the condition. So how it will join? So all rows from the inner table will be compared one by one. If matched then include in the output else skip. So what it means? So go to on excel here as you can see this is our small table. And it has the product id and name. And it has last table which has the order id, product id and order quantity. So how join will work between these two? So this is a small table our first condition is satisfied and this is the last table. Second condition we will have the index on this product id column. So we have the index. Second how join will work? So it will pick the first row from the small table and we will try to compare with the last table. So as we can see three and three. So these two values will be matched. So it will be moved to our output. Second here we have four. So it will check for four. So four is matched. So this will also include in output. Then it has five and in a small table again we have five. So this five will be also moved over destination. Then six we have a small table six value. So these six will be moved over output. Then seven and then eight. So all values are available. So all the value will be moved over output. If we will have any value which is not available in the small table. So that value will be skipped. Like we have level and here we have product id 9 and quantity something 200. So this is not in the small table. So we will get only these values. So join will work like this. So here we have type of nested loop joins. First is neo nested loop join. Whole table scan or indexes scan will be happen. Then that would be called as neo nested loop join. Next is index nested loop join. So when searching will be used in existing index to perform the lookup with the table. If this condition will be used in the execution plan. Then that would be called as index nested loop join. Last is temp index nested loop join. So what is this? If optimizer creates temporary index as a part of query. After execution of the query it will automatically drop that index. So that would be called as temp index nested loop join. It has total three types of nested loop joins. So go to all SQL server management studio to see in practical. So here I am going to use adventure works 2k16 database. So let me create two tables. First from the work order table and second from the product table. So let me execute these two queries to create the temporary tables for the work order and product. So we can execute. So as we can see work order temporary table has been created successfully. Now I just want to insert first 100 rows from the product table to product temporary table. So let me execute and it has 100 rows. So here as we can see we just want to get data from these two temporary tables which we have created. And joining condition is on product id. Now let me enable the execution plan to check about the joins. So now we can execute. Go to on execution plan. And here as we can see has match. So it is not going to use as nested loop join. Because our last table does not have any index on the joining column. So let me execute this query to create the index. So as we can see index is created successfully on the product id column. Now let me execute this query and now we want to check about the join. So go to on execution plan. And here as we can see nested loop join. Because our last table has the proper index on the joining column and small table does not have any index that is fine. So this is all about nested loop join. In the next video we will discuss about the merge join. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. And please share to others. Thank you so much.