SQL SERVER – Change Join Type for Query

“Can I Change Join Type for Query?” I was recently asked this during  Comprehensive Database Performance Health Check. Of course Yes!

Let us run first the following query. The query is written for the sample database AdventureWorks. When you run the query, make sure that you have enabled the execution plan.

SELECT *
FROM [Person].[Person] p
INNER JOIN [Person].[PersonPhone] ph ON p.BusinessEntityID = ph.BusinessEntityID

When you look at the execution plan for the query, you will notice that the join this query uses is Merge Join.

Now run the same query with a different hint of Hash Join.

SELECT *
FROM [Person].[Person] p
INNER JOIN [Person].[PersonPhone] ph ON p.BusinessEntityID = ph.BusinessEntityID
OPTION (HASH JOIN)

In the execution plan for this query, you will notice the join type used for the query is Hash Join.

Now run the same query with a different hint of Loop Join.

SELECT *
FROM [Person].[Person] p
INNER JOIN [Person].[PersonPhone] ph ON p.BusinessEntityID = ph.BusinessEntityID
OPTION (LOOP JOIN)

In the execution plan for this query, you will notice the join type used for the query is Loop Join.

It is also possible to give multiple options as a hint as well. Here is the query, in which you can see that I have given an example of multiple join hints. However, SQL Server will use ultimately the join which is the most efficient.

SELECT *
FROM [Person].[Person] p
INNER JOIN [Person].[PersonPhone] ph ON p.BusinessEntityID = ph.BusinessEntityID
OPTION (HASH JOIN, LOOP JOIN)

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

Reference: Pinal Dave (http://blog.SQLAuthority.com)