Join Hint in SQL - Guest Post by Joydeep Das

This is a small guest post on SQL Server submitted by one of my friend 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 hint used in SQL Server with algorithm and code example. So, why 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 hint used in SQL server.

LOOP Join Hint

This is the simplest form of LOOP Join. Suppose you have Tabe1 Join with Table2 by LOOP join hint. The algorithm is motioned 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)

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 motioned 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

NED

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 is fewer rows then 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 here in the comments section. Joydeep will try to help you with the answer as soon as possible. Please cheer up him to come up with more SQL Server articles and tips.