SQL Server speed: left outer join vs inner join

Question

In theory, why would inner join work remarkably faster then left outer join given the fact that both queries return same result set. I had a query which would take long time to describe, but this is what I saw changing single join: left outer join - 6 sec, inner join - 0 sec (the rest of the query is the same). Result set: the same

Answer 1

Actually depending on the data, left outer join and inner join would not return the same results..most likely left outer join will have more result and again depends on the data..

Answer 2

I'd be worried if I changed a left join to an inner join and the results were not different. I would suspect that you have a condition on the left side of the table in the where clause effectively (and probably incorrectly) turning it into an inner join.

Something like:

select * 
from table1 t1
left join table2 t2 on t1.myid = t2.myid
where t2.somefield = 'something'

Which is not the same thing as

select * 
from table1 t1
left join table2 t2 
on t1.myid = t2.myid and t2.somefield = 'something'

So first I would be worried that my query was incorrect to begin with, then I would worry about performance. An inner join is NOT a performance enhancement for a Left Join, they mean two different things and should return different results unless you have a table where there will always be a match for every record. In this case you change to an inner join because the other is incorrect not to improve performance.

My best guess as to the reason the left join takes longer is that it is joining to many more rows that then get filtered out by the where clause. But that is just a wild guess. To know you need to look at the Execution plans.