Hi!
Why the outer joins are in general slower than inner joins? I mean, independently from the database vendor. I suppose it's a matter of implementation or the access plan used, but I wasn't able to convince a colleague of mine who thinks performance should be the same.
Thanks in advance Lluis
Outer join will normally give you MANY more results (A*B instead of WHERE A=B). That'll take more time.
In general, it is because the db engine has to perform much more comparison operations to narrow down the result set.
An inner join will eliminate rows while an outer join doesn't. This results in a lot more rows to process.
Take a look at this article that visually describes joins. Notice how much smaller the result set is for the inner join vs the left and full outer join. These pictures don't represent every scenario but they give you an idea of what is going on.
Test it with real data. It may be that unless the outer join introduces additional rows, the performance is the same.
