Simulating inner join with left join

Question

I know you can simulate an inner join using a left join like this:

select * from a inner join b on a.Id= b.Id

select * from a left join b on 1 = 1 where a.Id= b.Id

I'm wondering about this one. Is it also equivalent to an inner join and how does it compare performance-wise?

select * from a LEFT join b on  a.Id = b.Id where b.Id IS NOT NULL

You could check it yourself. And probably modern query optimizer will figure out that these queries are equivalent. **[Demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=36557e20706f0ab1ee6487a3cc0db274)** — Lukasz Szozda, Sep 14 '18 at 15:39
Possible duplicate of [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) — dustytrash, Sep 14 '18 at 15:40
@LukaszSzozda how is the case different? He's asking the difference between a left and an inner join — dustytrash, Sep 14 '18 at 15:41
@dustytrash Please read carefully the question. It is about performance. All his queries are logically equivalent so when he use `LEFT JOIN` and then `WHERE b.Id IS NOT NULL` it is the same as INNER JOIN. — Lukasz Szozda, Sep 14 '18 at 15:42
@LukaszSzozda The one I posted mentions performance. How about this one?: https://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server Or is it a different case because it's sql-server versus this question (sql in general)? — dustytrash, Sep 14 '18 at 15:43
+1 on the question from me. Stackoverflow should be more welcoming towards questions reviewing antipatterns and having negative statements — Alexander Petrov, Sep 14 '18 at 15:49

score 2 · Answer 1 · answered Sep 14 '18 at 15:44

This is equivalent to an inner join:

select *
from a left join
     b
     on a.Id = b.Id
where b.Id is not null;

Whether or not a compiler recognizes the equivalence depends on the particular database.

There is usually a difference in the potential join-paths for inner joins versus outer joins (inner joins offer the optimizer more options). So, the performance could be different for the left join version of the query.

UnhandledExcepSean · Answer 2 · 2018-09-14T15:50:13.783

I am using Microsoft SQL Server 2017 on my local machine. I have a 10,000,000 row table and self joined to test 5 times each:

SELECT count(*) FROM [dbo].[Test] t1
INNER JOIN [dbo].[Test] t2 ON t1.ID=t2.ID
--Avg execution time: 1563.4000


SELECT count(*) FROM [dbo].[Test] t1
LEFT JOIN [dbo].[Test] t2 ON t1.ID=t2.ID
WHERE t2.ID IS NOT NULL
--Avg execution time: 1547.6000

SELECT count(*) FROM [dbo].[Test] t1
LEFT JOIN [dbo].[Test] t2 ON 1=1
WHERE t1.ID=t2.ID
--Avg execution time: 1536.8000

So, in a simple query they seem to perform almost identically and when comparing the execution plans, the plans were found to be the same. I would not count on the optimizer always generating identical plans between these styles and it is a poor way to write SQL. If it is a guaranteed relationship, use an inner join.

Please compare actual execution plans instead of time. – Lukasz Szozda Sep 14 '18 at 15:50 — Lukasz Szozda, Sep 14 '18 at 15:50
@LukaszSzozda Edited my answer to address that – UnhandledExcepSean Sep 14 '18 at 15:54 — UnhandledExcepSean, Sep 14 '18 at 15:54

Simulating inner join with left join

2 Answers2