 Hi there, this is Krish Raj from Bitescout and in this video, we will be going to learn about the next physical operator of the T-SQL execution plan called a hash join. A hash join is generally used when your input tables have some good amount of rows and no proper indexes exist on them. So basically a hash join builds a hash table by computing a hash value for each row from its build input. Now let's see how it works. So the hash join performs its operation in two phases, the build phase and the pro phase. The general form of hash join is the in-memory hash join in which the entire outer build table is scanned and computed the hash key for each row and then a hash table is built in memory and then for each row from the outer input is inserted into a hash bucket depending on the hash value computed for the hash key. And then this build phase is followed by a pro phase. So in the pro phase, the entire probe input is scanned and computed one row at a time and for each probe row a hash key value is computed and the corresponding hash bucket is scanned for the hash key value from the probe input and then the matches are produced. A hash function requires a significant amount of CPUs to generate hashes and memory resources to store the hash table. Okay, now enough from the theory and let's see this join in action. As I earlier said, a hash join uses the two join input which is build input and probe input. Now, before we execute this query, let's check the count in this each table. So let's run this query. So here we see that the product table have 504 rows and product category table has 4 rows. Usually the smaller of the two inputs serves as the build input. That means the product category table will work as a build input table and product table which has 504 rows is work as a probe input. Now in this query, I'm fetching product name and category name by joining this two table on product category ID. Now let's execute this query. Now let's go to the execution plant tab and here we see that our query is using a hash match join between the two table. So the build input table is represented by the top outer table in the execution plan. In our case, it is product category table and the probe input is the bottom table in the execution plan which is product table. Now let's open the query which we have used in the nested loop operator demo. So I have copied this query from the top one. Now in this query, if I try to forcefully use a hash join between these two table by adding a hash keyword over here and then when I run this three query together and check the execution plan here we can see that the overall cost relative to the batch of the hash join is quite high which is 55% in comparison with the merge join which is 44%. SQL optimizer does a really, really a great job in deciding which join operator to use in which condition. This is just for our knowledge. It is not recommended to use a join hints to force SQL server to use a specific join operator. Alright now in the next video we will learn about the next physical operator which is adaptive join.