Outer join performance

Question

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

Answer 1

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.

Answer 2

I had a problem with performance in my website and found a query taking 7 seconds to complete.

All the columns in this query were numbers and were indexed(SQL Server 2008 R2).

This full outer join included 3 tables with 150, 350 and 270 thousand records each.

I simply replaced the FULL OUTER JOIN for LEFT JOIN and execution time reduced to 0 seconds (miliseconds).

Before implementing the full outer join I had only tested it with a few records. However, I have learned that if tables have thousands or millions of records, the performance of a full outer join will not be good.

Answer 3

Some times left out join perform faster than inner joins. this will depends on the 2 results sets which supposed to be joined

Answer 4

Test it with real data. It may be that unless the outer join introduces additional rows, the performance is the same.

Answer 5

In general, it is because the db engine has to perform much more comparison operations to narrow down the result set.

Answer 6

Outer join will normally give you MANY more results (A*B instead of WHERE A=B). That'll take more time.