HANA - SQL Script - Basics
1. There are two objects in HANA Table and Table Type can any body explain the difference between them
2. Also the difference between Inner Join and Referential Join in the context of attribute views and analytical views.
Neha Singla replied
I am not able to share the link as I am having the PDF version provided by SAP. So, I am pasting the stuff for you for better understanding.
Is semantically a inner join that assume that referential integrity is given which means that the left table always have an corresponding entry on the right table. It could be used in e.g. data foundation for header-item relations where it can be assumed that for each item a header exists. It can be seen as an optimized or faster inner 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.
Referential joins should be used with caution since it assumes that referential integrity is ensured. The only valid scenario for the Referential Join is that (a) it is 110% guaranteed that for each row in one table, there is at least one join partner in the other table, and (b) that holds true in both directions (c) at all times. If that is not the case then referential joins have the possibility to give incorrect calculations if the referential integrity is not met – meaning if a delivery header is created but the items is not processed until a later stage then any calculations that use referential joins will be incorrect.
*** Referential Join cannot be used if a filter is set on a field in the right table.
Hope it clear your doubt.