Three types of SQL Join

1. Hash Match Join

Hash operations (i.e. hash algorithms) and hash tables.

Hash is a programming technique used to convert data into symbolic forms so that data can be retrieved more quickly and easily. For example, a row of data in a table can be converted to a unique value, which represents the contents of this row of data. This is similar to data encryption, where a hash value can also be converted back to the original data.

A hash table is a data structure that divides all elements into equal-sized "classes" or "blocks" that allow quick access to these elements. The hash function determines which "block" the element should enter. For example, you can take a row of data from the table, convert it to a hash value, and then put the hash value into the hash table.

When the hash Match Join occurs, SQL Server joins a small table of two tables, hashes the data rows in the small table, inserts the generated hash value into the hash table, and then iterates through the data in the table, each time it takes out a row to look for a matching row in the hash table. The hash of the small table is to reduce the size of the hash table, the hash value can be quickly compared. If both tables are large, then the hash match jion is very inefficient compared to other types of joins.

A Hash Match join is very efficient for large datasets, especially one that is much smaller than the other. The hash match join is also a very effective way to do this if the table is not sorted by the join column or there are no indexes available in the table.

The presence of a Hash Match join may also mean that there is a more efficient connection (Nested loop or merge), which may be due to the following reasons:
1. Index missing or incorrect index
2.where statement missing
A 3.where statement has a calculation or conversion of an indexed column that invalidates the index
In these cases, the optimizer considers the hash Match join to be the most efficient way to connect two tables, but it is possible to get more efficient queries by increasing the index, or by increasing the where statement to reduce the amount of data.

2. Nested Loop Join

As the name implies, the Nested Loop JOIN operation uses nested double loops, the result of this (17%) operator below the outer layer in our plan. Because two datasets are small, this is a very efficient operation. The Nested Loop join is a very efficient connection mechanism, as long as the inner dataset is small and the outer data set (small and unimportant) has an index. Unless the dataset is large, this connection should be the way you want to see it the most.

3. Merge Join

The two tables connected using the Merge join must be pre-sorted according to the connection column, in which case the merge join is an efficient way to connect. If the connected tables are not pre-ordered by the join column, the query optimizer either sorts and then executes the merge join, or performs a slightly less efficient hash Match join.

Three types of SQL Join