Difference between inner join and reference join.

[Former Member]

Hi,

Please can some one explain the difference between inner join and reference join.

Thanks,

Shakthi.

[Former Member · Accepted solution]

Hi,

A inner join gives data only when it exists in both left and right table. An inner join is always executed.

A referential join is also an inner join but it assumes that the referential integrity is maintained. This means if the columns from the right table, say, are not asked in the query then the join will not be executed and it gives all the rows from the left. Thus, it is an ommisible join.

Thus, a referential join is an optimized join but must be used only if it is sure that the referential integrity will be maintained every time.

[former_member226419 · Accepted solution]

Hi,

I have a small question.

Suppose no fields are requested from right table, then if we select either inner or referential join, it is giving me same result. Am i missing any concept?

BR

Sumeet

[sreehari_vpillai · Accepted solution]

Referential join comes into picture when there is referential integrity maintained between two tables. When you are not fetching any data from right table, this relation becomes irrelevant.

Sree

[Former Member · Accepted solution]

Inner Join : It will get join satisfied data (Common data) from both data tables.

Referential Join :

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

(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 this clarifies your question.

Thanks and Regards,

Mahesh

[former_member226419 · Accepted solution]

Hi Sree,

I have gone through many posts in SCN and  I have observed below 2 points about the same.

a.) if we do not select any fields from right table, then it will act as a left outer join , agreed, but i also got the same result when i use inner join ( when I don't select any fields from right table)...then whats the diff in both the approaches.

b.) Referential join will not give proper result if we filter data on right side of the table,but i just checked both inner and referential joins gave me same results when i put filter on right side table.

BR

Sumeet

[upamanyu_mukherjee · Accepted solution]

Hi Sumeet,

I think referential join works in the context of analytical views.

Can you please use the attribute view inside an AV and then execute the query on top of that AN.

Also you can add columns from the right table in the attribute view , but while testing run the query once with column from right hand table and once without.

Please let me know if it works!

Regards,

Upamanyu

[Phu_DANG]

This message was moderated.

[Former Member]

Referential Join cannot be used if a filter is set on a field in the RIGHT table. It is semantically an inner join that assume that referential integrity is given which means that the left table always have a corresponding entry on the RIGHT table.

Referential join in Attribute View:

Referential join will work as inner join even if we don’t select columns to the output from right table.

Referential join in Analytic View:

Referential join will work as inner join if we have columns from the right table in the output.

Join will not execute if we don’t have columns from the right table in the output.

[Former Member]

Referential Join In SAP HANA - SAP Student

Hope this helps for the correct understanding of referential join in Attribute view and analytical view.

[Former Member]

REFERENCE JOIN is used to enable referential integrity and contraints from base tables and will facilitate a simpler SELECT INNER JOIN statement as it is not necessary or required to expose base tables columns.  There is more to it, hope this will get you started.