[SOLVED] Outer join performance

Issue

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

Solution

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.

Answered By – Abe Miessler

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *