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.

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

Execution Plan, SQL Joins, SQL Operator, SQL Scripts, SQL Server, SQL Server Management Studio, SSMS
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
  • yvonne zhang
    April 22, 2021 9:41 pm

    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
Exit mobile version