Join Hint in SQL - Guest Post by Joydeep Das

This is a small guest post on SQL Server submitted by one of my friends, Joydeep Das. In this post, he will guide you to understand the basics of Join hint and when to use that.

He will also guide you on various types of Join hints used in SQL Server with algorithm and code example. So, what are you waiting for? Read more to understand the concept from Joydeep and don’t forget to ask your SQL Queries in the comments section.

Know about the Guest Author

Mr. Joydeep Das is a Microsoft Certified Database Administrator (MCDB), currently working with database design and implementation as a Sr. Software Developer in an ERP development company. Connect with him in his blog: http://sqlservernet.blogspot.com for SQL Server related articles.

What is Join Hint and When to Use That?

Join hint specifies that the query optimizer enforced the join strategy before joining two tables. By default, SQL server takes the best execution plan before joining table’s objects, be careful using join hint in your SQL statement until you know them very well. The hint is using in join to increase the performance of execution of statement. But sometimes it gives you reverse action also.

It applies to SELECT, DELETE and UPDATE statements. Syntax is:

<join_hint> ::= 
{ LOOP | HASH | MERGE | REMOTE }

In this article, I try to explain about the different types of hints used in SQL server.

LOOP Join Hint

This is the simplest form of LOOP Join. Suppose you have Table1 Join with Table2 by LOOP join hint. The algorithm is mentioned below:

FOR  each row R1 in the outer table
FOR each row R2 in the inner table
IF R1 joins with R2
return (R1, R2)

Example:

SELECT a.*, b.*
FROM   Table1 a
INNER LOOP JOIN Table2 b ON a.roll=b.roll 
WHERE  a.roll<=12

HASH Join Hint

Hash joins parallelize and scale better than any other join and are great at maximizing throughput in data warehouses.

The hash join executes in two phases: build and probe. During the build phase, it reads all rows from the first input (often called the left or build input), hashes the rows on the equijoin keys, and creates an in-memory hash table. During the probe phase, it reads all rows from the second input (often called the right or probe input), hashes these rows on the same equijoin keys, and looks or probes for matching rows in the hash table. Since hash functions can lead to collisions (two different key values that hash to the same value), we typically must check each potential match to ensure that it really joins.

The algorithm is mentioned below:

FOR  each row R1 in the build table
BEGIN
calculate hash value on R1 join key(s)
insert R1 into the appropriate hash bucket
END
FOR each row R2 in the probe table
BEGIN
calculate hash value on R2 join key(s)
FOR each row R1 in the corresponding hash bucket
IF R1 joins with R2
return (R1, R2)
END

Example:

SELECT a.*, b.*
FROM   Table1 a
INNER HASH JOIN Table2 b ON a.roll=b.roll 
WHERE  a.roll<=12

MERGE Join Hint

The merge join works by simultaneously reading and comparing the two sorted inputs one row at a time. At each step, we compare the next row from each input. If the rows are equal, we output a joined row and continue. If the rows are not equal, we discard the lesser of the two inputs and continue. Since the inputs are sorted, we know that we are discarding a row that is less than any of the remaining rows in either input and, thus can never join.

The algorithm is motioned below:

GET first row R1 from input 1
GET first row R2 from input 2
WHILE  not at the end of either input
BEGIN
IF R1 joins with R2
BEGIN
return (R1, R2)
get next row R2 from input 2
END
ELSE IF R1 < R2
get next row R1 from input 1
ELSE
get next row R2 from input 2
END

Example:

SELECT a.*, b.*
FROM   Table1 a
INNER MERGE JOIN Table2 b ON a.roll=b.roll 
WHERE  a.roll<=12

REMOTE Join Hint

It is useful when the join left table is a local table and the right table is a remote table. It is used when the left table has fewer rows than the right table. REMOTE is only used with Inner join.

Example:

SELECT a.*, b.*
FROM   Table1 a
INNER REMOTE JOIN Table2 b ON a.roll=b.roll 
WHERE  a.roll<=12

That’s all about Join hint in SQL Server. If you have any queries, don’t hesitate to ask your queries in the comments section. Joydeep will try to help you with the answers as soon as possible. Please cheer up him to come up with more SQL Server articles and tips.

Reference: http://www.kunal-chowdhury.com
You may like to follow me on twitter @kunal2383 or may like the Facebook page of my blog http://www.facebook.com/blog.kunal2383.