Inner Join vs Left Outer Join

[Former Member]

Hello experts,

I have a short question: I have two ADSOs, where the first one has a higher granularity and the second one goes into more detail.

So all the data from the left one exist in the right one with lower granularity.

Although in theory Inner Join is faster than Left Outer Join, in this scenario it seems that Left Outer Join is faster after my performance analysis in both RSRT and Visualize plan in HANA Studio.

Could you please share your expertise regarding the use cases of Inner vs Left Outer Join? Is it by default Inner Join faster or does it depend on the case?

[former_member212236]

Hello Giorgos,

If referential integrity is ensured then Inner Join is much faster that Outer Join where the right table is not

checked if no field from the right table is requested.

That means that the Referential Joins will be only executed, when fields from both tables are requested. Therefore, if a field is selected from the right table it will act similar to inner join, and if no fields from the right table is selected it will act similar to a left outer join.

From performance perspective, the Left Outer Join are almost equally fast as Referential Join, while the Inner Join is usually slower due to the fact, that the

join is always executed.

Below link has got more details on the joins,

https://blogs.saphana.com/2011/10/17/sql-join-union-what-you-ever-wanted-to-know1/

Regards,

Vinoth V

[arun_kumar52]

Hi Giorgos,

You theoretical view on Inner Join is faster than Left Outer Join is not correct.

From performance point of view, the Left outer join is always faster than Inner join. Inner join is usually slower due to the fact that the join is always executed.

Regards,

Arun.M.D