DataFlair Team ·Published · Updated
650
Nimish KapoorNULLNULL1105CT5NULLCamera800
Hi Sai Krishna, Thanks for the reply, i understood the point that you are trying to specify but then the usual behavior of the full outer join is it retrieves the tuples from both the tables regardless of the matching elements, but then the result set that i am getting has put me in doldrums.
Earlier, we learned to create tables in SAP HANA, now, we will learn the different types of joins in SAP HANA.
Data tables are a crucial element in SAP HANA modeling and data designing. As we know, SAP HANA hosts many kinds of data sources and allows you to combine records for data modeling from multiple sources of data. If we fetch data from different tables or have more than one table in an information model or view, we will need to join these tables.
In this tutorial, we will start with understanding what are joins, their significance in SAP HANA and then learn the types of joins and script used to create them in SAP HANA.
What are Joins in SAP HANA?
Joins in SAP HANA are used for joining two or more tables having relevant data or records that creates a logical association between the tables. The joins are very important to bind the modeling object in a logical framework because these modeling objects are used for reporting and analytical purposes later. And, if tables in SAP HANA are not joined with an appropriate join type, proper associations will not be formed, and the report analysis will be flawed.
Types of SAP HANA Joins
SAP HANA supports a number of ways to join different data tables together. Before we start to learn the types of joins and how they work in SAP HANA, let us understand the concept of tables. Whenever we will talk about a join clause, we presume that the join is applied between two tables. We refer to the two tables as a left table and a right table. The two tables which we will use for explanatory purposes are given below:
Table 1: Customer_Info
Customer_IDCustomer_NameCT1Rajesh SharmaCT2Sameer KhannaCT3Neeti RanaCT4Nimish KapoorTable 2: Order_DetailsOrder_IDCustomer_IDProduct_NameTotal_Units1101CT1iPad3001102CT1MacBook2001103CT2Fridge5001104CT3LED TV6501105CT5Camera800You may refer to the SQL script given below to create your own table in SAP HANA. We are providing the script to create the two tables mentioned above. Here the name of our schema is SALES_2018. You can insert the name of your own schema under which you are creating the table.
Script:
1. Standard Database Joins
i. Inner Join
The inner joins in SAP HANA join the left and right tables with only matching records. That is, only that part is displayed in both tables that coincide. Let us see how inner join will work with the two tables we created.
Order_IDCustomer_IDCustomer_NameProduct_NameTotal_Units1101CT1Rajesh SharmaiPad3001102CT1Rajesh SharmaMacBook2001103CT2Sameer KhannaFridge5001104CT3Neeti RanaLED TV![Hana Outer Join Hana Outer Join](/uploads/1/2/5/0/125047738/900298292.jpg)
As you can see, data related to only the customers CT1, CT2, and CT3 were taken in the final table because only these are common between the left and right table. Inner joins are very useful while creating information models as you can use it to join tables in Attribute Views. You can also use it to join the master data tables with fact tables of Analytic Views when referential integrity of records is not assured.
The script to create inner join is shown below (here T1 is table 1 and T2 is table 2):
ii. Left Outer Join
The left outer joins combine the entire left table with only the matching records from the right table. Sometimes, if the engine can’t find any records matching for the left columns in the right table then it returns NULL at those places.
Checkout Latest SAP HANA Use Cases
In our case, all the rows and columns from the left table CUSTOMER_INFO will be fetched and joined with only the matching records in the right table ORDER_DETAILS.
Order_IDCustomer_IDCustomer_NameProduct_NameTotal_Units1101CT1Rajesh SharmaiPad3001102CT1Rajesh SharmaMacBook2001103CT2Sameer KhannaFridge5001104CT3Neeti RanaLED TV650NULLCTNimish KapoorNULLNULLThe script to create left outer join is shown below (here T1 is table 1 and T2 is table 2):
Explore the Unique Features of SAP HANA
iii. Right Outer Join
The right outer joins in SAP HANA combines the entire right table with the matching contents in the left table. The right outer join is very rarely used in real scenarios. It returns NULL in place of values which do not find an association with the records in the right table.
In our case, all the records from the right table ORDER_DETAILS will be fetched and only the matching or associated records from the left table CUSTOMER_INFO will be taken. The final table is shown below.
Order_IDCustomer_IDCustomer_NameProduct_NameTotal_Units1101CT1Rajesh SharmaiPad3001102CT1Rajesh SharmaMacBook2001103CT2Sameer KhannaFridge5001104CT3Neeti RanaLED TV6501105CT5NULLCamera800The script to create a right outer join is shown below (here T1 is table 1 and T2 is table 2):
Must learn:SAP HANA Studio Overview
iv. Full Outer Join/Union
A full outer joins in SAP HANA combines all the records from both left and right tables irrespective of the matching conditions. The cells which do not find any association with the other table are left NULL. It is also known as a union.
The tables CUSTOMER_INFO and ORDER_DETAILS will have all their records in the final table as shown below.
Order_IDCustomer_IDCustomer_NameProduct_NameTotal_Units1101CT1Rajesh SharmaiPad3001102CT1Rajesh SharmaMacBook2001103CT2Sameer KhannaFridge5001104CT3Neeti RanaLED TV650NULLCT4![Hana outer join youtube Hana outer join youtube](http://teachmehana.com/wp-content/uploads/2016/07/image005-5.png)
The script to create full outer join is shown below (here T1 is table 1 and T2 is table 2):
Learn to work on SAP HANA Administration Console
2. Specific Joins
These join types are SAP HANA SQL script-specific joins:
i. Referential Join
Referential join is the default join type in SAP HANA modeling. A referential join is similar to an inner join. The only difference between the two is referential integrity must be ensured in the case of referential join otherwise it cannot be formed. So, before we move forward with understanding referential integrity. Let us learn the basic concept of a referential join. It is formed between a fact table (transaction data or Analytic View) and a master data table( Attribute View). Every master data table has a primary key column which acts as a foreign key in the fact table.
Referential joins in SAP HANA are used whenever there is a primary key and foreign key association between two tables. And, referential integrity is when for every value in the foreign key column, there is a reference value in the primary key column of the master data table. From a performance point of view, referential joins are better than inner joins. Referential joins are recommended for star schemas as long as referential integrity is maintained.
Customer_IDCustomer_NameCity_CodeRegionCT1Rajesh SharmaMUMEastCT2Sameer KhannaIDRCentralCT3Neeti RanaHYDSouthCT4Nimish KapoorCHNSouth Order_No.Customer_IDProduct_NameTotal_UnitsPRICE1101987CT1iPad30040,0001102568CT1MacBook20080,5001103282CT2Fridge500 95,0001104229CT3LED TV6501,20,0001105554CT4Camera80028,000Understand the concept of Data Warehousing in SAP HANA
ii. Text Join
Text joins in SAP HANA provides a description of text records in the language specific to the user. If user A has selected the language as German, then all the details regarding the table and columns will be displayed to the user in German. We use text joins to join a text table with a master data table. The text table must have a primary key column linked to the other data table and a language key column which contains user language preferences. Text joins are also used with SAP tables having SPRAS session language columns.
Summary
This concludes our tutorial on Joins in SAP HANA. We hope now you understand what join types to use in which condition during information modeling.
If you have any queries and suggestions related to SAP HANA Joins Tutorial, drop your comments in the comment box below.
Time to play SAP HANA Online Quiz!