Menu
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
889
You can observe the lack of performance because SQL inner join is slower. Outer joins especially left outer joins, are faster and better performance in most cases. The satisfaction of the inner join condition is mandatory. There are no conditions that we have to meet in the outer join query necessarily.
Right outer join is used to show all the data from the right table even there is no matching or common data from the left table. You can view the highlighted parts in Table 1 and Table 2 that are included in the right join. SELECT Table1.*, Table2.Quantity FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.Product = Table2.Product
Test it with real data. It may be that unless the outer join introduces additional rows, the performance is the same. Some times left out join perform faster than inner joins. this will depends on the 2 results sets which supposed to be joined Outer join will normally give you MANY more results ( A*B instead of WHERE A=B ). That'll take more time.
In SQL Full Outer Join, all rows from both the tables are included. If there are any unmatched rows, it shows NULL values for them. We can understand efficiently using examples. Let’s create a sample table and insert data into it.
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.
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
