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://blog.sqlauthority.com)
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
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?
Agree with Wes – why would you possibly want to simulate an inner join with an outer join?
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
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
@ 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.
does anyone know how to do the reverse? i.e. simulate left join using inner join?
Why do you want to do this? It is complicated than expected
how to select columns from two tables after inner joining?
select t1.col, t2.col,……………
where t1 and t2 are alias of table1 and table2
Good one. Thanks
“INNER JOIN should be used where it can not be used” – what does that mean?
I think it should be
“INNER JOIN should be used where it is not used”
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..
nice one……………………..