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.

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://darkslategrey-bat-805937.hostingersite.com)