SQL SERVER – INNER JOIN using LEFT JOIN statement – Performance Analysis

Just a day ago, while I was working with JOINs I find one interesting observation, which has prompted me to create following example. Before we continue further let me make very clear that INNER JOIN should be used where it can not be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN it should be done with priority.

Run following two script and observe the resultset. Resultset will be identical.

USE AdventureWorks
GO
/ Example of INNER JOIN /
SELECT p.ProductID, piy.ProductID
FROM Production.Product p
INNER JOIN Production.ProductInventory piy ON piy.ProductID = p.ProductID
GO
/ Example of LEFT JOIN simulating INNER JOIN /
SELECT p.ProductID, piy.ProductID
FROM Production.Product p
LEFT JOIN Production.ProductInventory piy ON 1 = 1
WHERE piy.ProductID = p.ProductID
GO

After looking at identical result the first thing came in to my mind was what is the behind the scene plan. When looking at the actual execution plan of the query it is very clear that even LEFT JOIN is used SQL Server Query Optimizer converts it to INNER JOIN as it determined that there is no need of OUTER LEFT JOIN and INNER JOIN will give better performance.

SQL SERVER - INNER JOIN using LEFT JOIN statement - Performance Analysis leftinner

Looking at above scenario it makes us think how smart Query Optimizer Engine is and how it might be saving lots of performance related issue for sub-optimal queries.

Now let us understand why LEFT JOIN acts as  INNER JOIN. When 1= 1 is used in ON clause it is always true and converts LEFT JOIN to CROSS JOIN. However, when WHERE condition’s effect is applied to above CROSS JOIN it produces the result similar to INNER JOIN in our case. SQL Server Query Optimizer interprets this earlier and uses INNER JOIN right away.

I think this is good interview questions to ask. Interview question which can be asked is “How to write OUTER JOIN which will give you exact same result, execution plan and performance as INNER JOIN?”

If there is any other explanation or if you know if there is any similar example please let me know and I will post on this blog.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – TOP Downloads – Bookmark
Next Post
SQLAuthority News – Download SQL Server 2005 Service Pack 3 – CTP

Related Posts

15 Comments. Leave new

  • You can also use the same join clause and have your where be piy.ProductID IS NOT NULL

    I’d be interested in knowing what the plan looks like for your SQL statement. Also, try doing the same JOINs on fields that aren’t indexes (like, say, product name, or product price) and see if you can get different results.

    In more complicated situations, I’ve had to do similar things and had less success with the optimizer “figuring out” that it could do a cross join with inner join, so I’d be interested in hearing how the above tests play out in your test environment.

    Cheers
    -eli

    Reply
  • While this is an interesting quirk of Sql Server I cannot imagine how it would be a useful question for an interview. What does it demonstrate other than a knowledge of useless sql constructs?

    Reply
  • Agree with Wes – why would you possibly want to simulate an inner join with an outer join?

    Reply
  • I often use, “WHERE 1 = 1” just for convenience in adding or subtracting ANDs in a where clause.

    That said, I also see no reason for esoteric questions like this in an interview.

    Furthermore, while I agree that the Query Optimizer Engine helps to make less optimal queries run more optimally, it also enables bad coding practices. What happens if someone tries to port the same bad code to, heaven forbid, Oracle?

    Sloppy coders also tend not to look at query plans.

    I’m just sayin’ … *tc

    Reply
  • Hi Pinal,

    Your blog regarding SQL server is Excellent.
    I have a samll Issue, i need to migrate data from
    Server to client system, Whether it may be a single table data or mulitple tables.
    Please look into this and do favour

    Regards
    Prem

    Reply
  • Imran Mohammed
    May 22, 2009 10:35 am

    @ Prem,

    What do you mean by client system ?

    In which format do you need to send data to client system.

    If you want to provide data into excel or csv file, then try doing bcp, osql, dts or openquery. All these techniques are used to get data out of sql server in a desired form as you want.

    It really does not matter if you want data from one table or multiple table, as long as you have script/query to populate data out of your sql database.

    If this post does not answer your question, Please provide more info and please state your question clearly.

    ~ IM.

    Reply
  • Ashish Rajbanshi
    August 26, 2009 5:16 am

    does anyone know how to do the reverse? i.e. simulate left join using inner join?

    Reply
  • how to select columns from two tables after inner joining?

    Reply
  • Good one. Thanks

    Reply
  • “INNER JOIN should be used where it can not be used” – what does that mean?

    Reply
  • can any one explain in simple language … what does inner join exactly do and how does it work ?? it will be great helpful if it will be with small example..

    Reply
  • nice one……………………..

    Reply

Leave a Reply

Menu