Referential join and Inner Join

[Former Member]

Hi HANA experts,

I am not able to understand Referential join and the difference it has with Inner join. When I read through various articles and posts, I think they both are the same. Please throw some light on the above concepts with an example.

Thanks in advance.

Regards,

Vivek

[Former Member · Accepted solution]

Hi Vivek,

In line with the above the difference provided by SAP is explained as follows:

Inner Join:

Returns rows when there is at least one match in both sides of the join. For example if an attribute view is created by joining 2 tables (Customer & State) with an inner join the following is returned:

Data Base Tables

STATE	SNAME
MI	MICHIGAN
AL	ALABAMA

C_ID	CNAME	STATE	AGE
1	WERNER	MI	10
2	MARK	MI	11
3	TOM	TX	12
4	BOB	TX	13

Resulting Inner Join:

*** Customer (3 & 4) is not returned due to no corresponding header record (TX) in the state table

C_ID	CNAME	STATE	AGE	SNAME
1	WERNER	MI	10	MICHIGAN
2	MARK	MI	11	MICHIGAN

Referencial Join:

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 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.

Kind regards,

Danielle

[Former Member · Accepted solution]

Hi,

Could anyone tell what is the syntax for Referential join?

I couldn't find it in the documentation.

Thanks,

Shachar

[former_member184768 · Accepted solution]

I don't think there is a special syntax for Referential join. It is a concept which forms the join condition between the parent and child table based on the keys defined.

Regards,

Ravi

[Former Member · Accepted solution]

When testing this out in the SQL editor there does not seem to be a HANA recognised syntax for referencial join. When using the generate visual SQL option on the data base table this too does not have a referencial join option. Within the HANA model views you DO have the option to make joins referencial but you cannot see the SQL behind but rather XML.

When I view a simple model join in XML I get syntax like this:

<joins>

      <join languageColumn="">

        <leftTable schemaName="SAPRET" columnObjectName="MARA"/>

        <rightTable schemaName="SAPAFS" columnObjectName="MARA"/>

        <leftColumns>

          <columnName>MATNR</columnName>

        </leftColumns>

        <rightColumns>

          <columnName>MATNR</columnName>

        </rightColumns>

        <properties cardinality="C1_1" joinOperator="Equal" joinType="referential"/>

      </join>

Based on thi smy guess is that HANA views are processed with XML first which can handel the logic of referencial join then converted to approriate SQL. EG remember that referencial join simplymeans it will either be executed as inner or left outer based on field selection.

Hope this helps clarify what the system is doing.

Kind regards,

Danielle

[Former Member · Accepted solution]

Hi Dniella,

Yes this helps in understanding.

Thanks,

Shachar

[Former Member · Accepted solution]

Hi Shachar,

Glad to have helped. Also if you are comfortable and happy with the responses above please feel free to acknowledge contribution by rewarding with points as it helps ensure continued participation and recongision for participants.

Kind regards,

Danielle

[former_member213277 · Accepted solution]

Hi Danielle,

As per your previous post, Referential Join will act as INNER JOIN when we access the fields from both joined tables.

It will act as LEFT OUTER JOIN if no fields from Right table are accessed and it will not check whether corresponding value is exist in Right table or not assuming referential Integrity is maintained

I have created a Attribute View with below mentioned tables which have one column Country

TAB1:                                                 TAB2:

Country                                                Country 

INDIA                                                    UK  

SRILANKA                                            US

NEPAL                                                 GERMANY

I have created a Attribute View with Referential Join and I am fetching field only from TAB1 (i.e.TAB1.Country) i.e. I have selected  TAB1.Country as output of the Attribute View.  Since there is no referential integrity maintained and only left table is queried hence Left Outer Join should be applied and all the records from Left table should be displayed.

But there are no records are fetched and displayed (its working as Inner Join even though only Left table is queried)

I am confused in understanding the Referential Join, Could you please explain me how does it work

Regards,

Nag

[former_member184768 · Accepted solution]

Replied to your another post.

Regards,

Ravi

[Former Member]

Hi Vivek,

It has been discussed before in the thread - http://scn.sap.com/thread/3195646

Basically referential join should be used only when the referential integrity between both tables are abolutely guaranteed. It does an inner join (more expensive) if fields form both table are selected. If fields from only the left table is chosen, it does a left outer join (faster) instead.

Referntial joins will not work if you have set a filter on the right join table.

Thanks,

Anooj