Joins in SAP HANA – Create Standard Database & Specific Joins in 7 Mins

FREE Online Courses: Transform Your Career – Enroll for Free!

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_ID

Customer_Name
CT1Rajesh Sharma
CT2Sameer Khanna
CT3

Neeti Rana

CT4

Nimish Kapoor

Table 2: Order_Details

Order_IDCustomer_IDProduct_NameTotal_Units

1101

CT1iPad300

1102

CT1MacBook200
1103CT2Fridge

500

1104CT3LED TV

650

1105CT5Camera

800

You 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:

CREATE COLUMN TABLE SALES_2018.
"CUSTOMER_INFO" ( 
"Customer_ID" nvarchar(10) primary key, 
"Customer_Name" nvarchar(50) 
); 
INSERT INTO SALES_2018."CUSTOMER_INFO" VALUES ('CT1', 'Rajesh Sharma'); 
INSERT INTO SALES_2018."CUSTOMER_INFO" VALUES ('CT2', 'Sameer Khanna'); 
INSERT INTO SALES_2018."CUSTOMER_INFO" VALUES ('CT3', 'Neeti Rana'); 
INSERT INTO SALES_2018."CUSTOMER_INFO" VALUES ('CT4', 'Nimish Kapoor'); 
CREATE COLUMN TABLE SALES_2018."ORDER_DETAILS" ( 
"Order_ID" integer primary key, 
"Customer_ID" nvarchar(10), 
"Product_Name" nvarchar(20), 
"Total_Units" integer 
); 
INSERT INTO SALES_2018."ORDER_DETAILS" VALUES (1101, 'CT1','iPad',300); 
INSERT INTO SALES_2018."ORDER_DETAILS" VALUES (1102, 'CT1','MacBook',200); 
INSERT INTO SALES_2018."ORDER_DETAILS" VALUES (1103, 'CT2','Fridge',500); 
INSERT INTO SALES_2018."ORDER_DETAILS" VALUES (1104, 'CT3','LED TV',650); 
INSERT INTO SALES_2018."ORDER_DETAILS" VALUES (1105, 'CT5','Camera',800);

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_ID

Customer_IDCustomer_NameProduct_NameTotal_Units

1101

CT1Rajesh SharmaiPad300

1102

CT1Rajesh SharmaMacBook

200

1103CT2Sameer KhannaFridge

500

1104CT3Neeti RanaLED TV

650

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.

Joins in SAP HANA - Inner Join

The script to create inner join is shown below (here T1 is table 1 and T2 is table 2):

SELECT T2."Order_ID", T1."Customer_ID", T1."Customer_Name", T2."Product_Name", T2."Total_Units" 
from "CUSTOMER_INFO" AS T1 
INNER JOIN 
"ORDER_DETAILS" AS T2 
ON T1."Customer_ID" = T2."Customer_ID";
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.

Joins in SAP HANA - Left Outer Join

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_Name

Total_Units

1101CT1Rajesh SharmaiPad300

1102

CT1Rajesh SharmaMacBook200
1103CT2Sameer KhannaFridge

500

1104CT3Neeti RanaLED TV

650

NULLCTNimish KapoorNULL

NULL

The script to create left outer join is shown below (here T1 is table 1 and T2 is table 2):

SELECT T2."Order_ID", T1."Customer_ID", T1."Customer_Name", T2."Product_Name", T2."Total_Units" 
from "CUSTOMER_INFO" AS T1 
LEFT OUTER JOIN 
"ORDER_DETAILS" AS T2 
ON T1."Customer_ID" = T2."Customer_ID";

 

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.

Joins in SAP HANA - Right Outer Join

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_Name

Total_Units

1101CT1Rajesh SharmaiPad300

1102

CT1Rajesh SharmaMacBook200

1103

CT2Sameer KhannaFridge

500

1104CT3Neeti RanaLED TV

650

1105CT5NULLCamera

800

The script to create a right outer join is shown below (here T1 is table 1 and T2 is table 2):

SELECT T2."Order_ID", T1."Customer_ID", T1."Customer_Name", T2."Product_Name", T2."Total_Units" 
from "CUSTOMER_INFO" AS T1 
RIGHT OUTER JOIN 
"ORDER_DETAILS" AS T2 
ON T1."Customer_ID" = T2."Customer_ID";
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.

Full Outer Join

The tables CUSTOMER_INFO and ORDER_DETAILS will have all their records in the final table as shown below.

Order_ID

Customer_IDCustomer_NameProduct_NameTotal_Units

1101

CT1Rajesh SharmaiPad300

1102

CT1Rajesh SharmaMacBook200

1103

CT2Sameer KhannaFridge500
1104CT3Neeti RanaLED TV

650

NULLCT4Nimish KapoorNULL

NULL

1105CT5NULLCamera

800

The script to create full outer join is shown below (here T1 is table 1 and T2 is table 2):

SELECT T2."Order_ID", T1."Customer_ID", T1."Customer_Name", T2."Product_Name", T2."Total_Units" 
from "CUSTOMER_INFO" AS T1 
FULL OUTER JOIN 
"ORDER_DETAILS" AS T2 
ON T1."Customer_ID" = T2."Customer_ID";

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_ID

Customer_NameCity_CodeRegion

CT1

Rajesh SharmaMUMEast
CT2Sameer KhannaIDR

Central

CT3Neeti RanaHYD

South

CT4Nimish KapoorCHN

South

Referential Join

Order_No.Customer_IDProduct_NameTotal_UnitsPRICE

1101987

CT1iPad30040,000

1102568

CT1MacBook20080,500
1103282CT2Fridge500

95,000

1104229CT3LED TV650

1,20,000

1105554CT4Camera800

28,000

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.

Did we exceed your expectations?
If Yes, share your valuable feedback on Google

follow dataflair on YouTube

2 Responses

  1. Thymios Nakas says:

    Hello,
    I am new to HANA Studio and trying to explore new ways of manipulating my data. I am using the inner join for my analysis but my data consists of multiple entries for the primary key (i.e. Order_ID in the above example), so what i have in mind is grouping my results by the primary key and seeing the aggregate figures for each row. I only have measures in my columns and no attributes so I guess there should be a way to do it. I tried adding simply a group by statement on the last row but it didn’t worked. Any idea how can I do it?

    Thanks

  2. Rahul says:

    the question is not clear , from what I understand use sum() then join

Leave a Reply

Your email address will not be published. Required fields are marked *