SQL SERVER – Simulate 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.

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 (http://blog.SQLAuthority.com)

About these ads

15 thoughts on “SQL SERVER – Simulate INNER JOIN using LEFT JOIN statement – Performance Analysis

  1. 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

    Like

  2. 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?

    Like

  3. 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

    Like

  4. 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

    Like

  5. @ 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.

    Like

  6. 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..

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s