What is Referential Join

[former_member213277]

Hi Experts,

As per the posts/Blogs in SDN, 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

To test Referential Join 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_member184088]

Hello

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.

Regards,

Atul.

[former_member184768]

Hi Nagaraj,

Can you please delete the repeating threads. Sometimes, the send / submit button doesn't work properly and it posts the same message multiple times.

Regards,

Ravi