 Hello, welcome to SSUnited, so see this side and this is continuation of SQL Server Performance Training. So today we are going to discuss about merge joins, which is also part of physical join. If you haven't watched last video of this video series, so I would strongly recommend to watch that video where you can understand about the physical joins and nested loop joins. So let's start with merge join. So what is merge join? And what is the condition to use the merge join in the physical joins? So it needs both the input should be sorted on the joining key or it should have the cluster index on that column. So this is the first condition. Second condition, it requires at least one equal join. Like in our on clause, we should have used at least one equals to condition. Which is both tables should be contained the last data. So if we have only one table has last data and second table has small data then that would be going to perform as nested loop join as we have discussed in the last video. So here both tables should have proper index on that joining column or sorted in the physical order. So how join will work go to on excel to understand about the logic then we will see in practical. So as we can see we have the first table and here we have the second table. So again it will going to compare one by one. So as we can see it has one and here we have one. If any duplicate values will be there then that value will be complete and create a work table and that duplicate value will be there. Then joining condition will be applicable not from the second table. It will be going to perform internally by the work table. So as we can see one, so one going to match with one. So in output it will be going to return as one. Then again one will be going to match with the one. So it will be going to return as one. Now here we have one and now here we don't have any duplicate values. So this work table will be dropped and moved over actual table. Not this time pointer will be going to point this two and it will be going to again move to two. Now we have two and two. So here we don't have any duplicate values. So two will be compared with two and in output it will be going to return as two. Now it's fine. Now pointer will move to three. So here we have pointer in three but in second table we have duplicate three. So that three will be copied in the work table. And now lookup will be performed with the work table instead of second table. So three is available over here and three will match with first three. Then pointer will move to second three. So it will be going to match. So it will return as three. Now it doesn't have any three as well. So it will be going to drop this and pointer will move to point next value. So here we don't have any value. Now pointer will move to on this. So we don't have any value. So output will be going to return as this value. So one, one, two, three and three. So this is all about the merge how merge will be going to work. So now go to on SQL Server to see in practical. So as I am using SQL 217 you may have another version of SQL Server. Again I am going to use AdventureWorks 2K16 database. So let me create this work order temporary table from this work order. So we can execute this query and we can say that 72,591 rows have been inserted in the work order tmp table. So as I told you we should have the proper index on the joining column. So now we need to execute this create cluster index on this work order temp table with work order id. Because in work order table we have cluster index with the work order id. So we can execute and now index is created successfully on this work order tmp table and in our work order table we need to check about the index. So we can check and go to down. And here in the index as we can see this is the work order cluster unique index. So that is fine. Both table has the index with the order id column and second one equation should be there. So we have the equation. Now we want to apply join between work order table and work order tmp table which is created at yet. Now we can execute and we just want to check the join. So let me enable the execution plan and execute the query. Go to on execution plan and here we can see merge join. So it will be going to written as merge join because both tables are last table and both tables are also having the cluster index on the joining column which is work order id column. So I hope you have understand about the nested loop joins and merge join in this video. In our next video we will discuss about the hash 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. Thank you so much.