Referential joins

•  Referential joins have been introduced in HANA to improve performance.
• Referential joins are fundamentally inner joins.

Referential joins pre- requisites:

1. Referential integrity is maintained by the central table.

2.The Cardinality is 1-N, Dimension(1) --> Transactions(N).\

 

Full Outer Join

 

--------------------------------------------------------------------------------------

/* The table 1 has 78 rows

   the Business partner table has 45 rows*/

Select * from "<Drop and Drop table 1 from HDI>" as A

Full outer join "<Drop and drop table 2 from HDI>" as B

On A."<Foreign_key name of table A>" = B. "<Foreign_key name of table B>"

----------------------------------------------------------------------------------------

Right Outer join

 

--------------------------------------------------------------------------------------

/* The table 1 has 78 rows

   the Business partner table has 45 rows*/

Select * from "<Drop and Drop table 1 from HDI>" as A                                   

Right outer join "<Drop and drop table 2 from HDI>" as B                                

On A."<Foreign_key name of table A>" = B. "<Foreign_key name of table B>"

----------------------------------------------------------------------------------------

Left Outer join

..................................................................................................................................................................

/* The table 1 has 78 rows

   the Business partner table has 45 rows*/

Select * from "<Drop and Drop table 1 from HDI>" as A

Left outer join "<Drop and drop table 2 from HDI>" as B

On A."<Foreign_key name of table A> = B. "<Foreign_key name of table B>"

..................................................................................................................................................................... 

Inner join syntax

 

......................................................................................................................................................................

/* The table 1 has XX rows

   the table B has YY rows*/

Select * from "<Drop and Drop table 1 from HDI>" as A

Inner join "<Drop and drop table 2 from HDI>" as B

On A."<Foreign_key name of table A>" = B. "<Foreign_key name of table B>"

....................................................................................................................................................