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!

SQL SERVER - Change Join Type for Query jointypes-800x419

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.

SQL SERVER - Change Join Type for Query querymerge

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.

SQL SERVER - Change Join Type for Query queryhash

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.

SQL SERVER - Change Join Type for Query queryloop

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.

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

, , , , , ,
Previous Post
Install Python3 on Linux
Next Post
Android Greenbox Around Icon – Unable to Scroll

Related Posts

3 Comments. Leave new

  • Anurag Taneja
    April 22, 2021 8:26 am

    Hello Pinal Sir,

    In which case SQL server will use bad join or when it’s possible that the performance will be degraded?

    Reply
  • Thank you, Sir!
    I am a rookie to the execution plan. I’d like to know which join you list here is more efficient? If you can point me to your previous articles that would be great!

    Thanks again!
    Yvonne

    Reply
  • Hi Pinal,
    do you think OPTION will “force” specific execution plan till option is removed from query?
    Thank you
    Alex

    Reply

Leave a Reply

Menu