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
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.