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

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

Table 2: Order_Details

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.

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

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.

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.

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.

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.

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.

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

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.

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.