Joins and Unions in SAP HANA

Data means information. In the database, the data is stored in the form of tables. To fetch, insert, and display the data we are using many computer languages.  In the same way in SAP HANA we are using variant types of Joins and unions to insert, fetch, and display the data from the database. 

In this blog, we gave insightful information about Joins and Unions in SAP HANA. 

Let's get started!

What are Joins in SAP HANA

• Joins are used for joining more than two tables which are having relevant records or data that create an analytical association between the tables. The joins play a prominent role to bind the modeling objects in an analytical framework because these objects are utilized for analytical and reporting purposes. 
• Using appropriate join type to the table, it will associate with the tables to create an accurate report analysis without any flaws. So now we’re going to discuss various types of SAP HANA joins.

Types of SAP HANA Joins

Before having useful insights about different types of joins in SAP HANA let us know about the tables. In the database, data is stored in the form of tables. So the database consists of a large number of tables. To get the data from variant tables we need to apply joins in between the tables. The following instance will help you to understand how the data is stored in the form of tables in the database. 

Instance: Customer details and Order details are stored in two tables. 

Customer Details: 

Order Details:

 In this way, you will store the data in tables. And also you can use a script in SAP HANA to store the data in tables in the following way. 

CREATE COLUMN TABLE SALES_2020.

"CUSTOMER_INFO" ( 

"Customer_ID" nvarchar(6) primary key, 

"Customer_Name" nvarchar(20) 

); 

INSERT INTO SALES_2020."CUSTOMER_INFO" VALUES ('Cst1', 'Suneel'); 

INSERT INTO SALES_2020."CUSTOMER_INFO" VALUES ('Cst2', 'Vinod'); 

INSERT INTO SALES_2020."CUSTOMER_INFO" VALUES ('Cst3', 'Rahul Kumar'); 

INSERT INTO SALES_2020."CUSTOMER_INFO" VALUES ('Cst4', 'Bindu');

INSERT INTO SALES_2020."CUSTOMER_INFO" VALUES ('Cst5', 'Rajji');

CREATE COLUMN TABLE SALES_2020."ORDER_DETAILS" ( 

"Order_ID" integer primary key, 

"Customer_ID" nvarchar(6), 

"Product_Name" nvarchar(15), 

"Total_Units" integer 

); 

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1101, 'Cst1','LED TV',400); 

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1102, 'Cst3','Fridge'300); 

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1103, 'Cst1','Cooler',200); 

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1104, 'Cst2','AC',650); 

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1105, 'Cst4','Fridge',450);

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1106, 'Cst5','Camera',500);

INSERT INTO SALES_2020."ORDER_DETAILS" VALUES (1107, 'Cst5','Fridge',700);

The following are the two types of joins with insightful information. 

• Standard Database Joins

• Specific Joins

Inclined to build a profession as SAP HANA Developer? Then here is the blog post on, explore SAP HANA Training

Standard Database Joins

Inner Join: 

Inner join returns all the data that matched from the left and right tables. When you need to display or to get the data from two tables then the inner join is used. Let us consider the above tables how the inner join works on the above two tables. 

If you observe the above table, you can see that only Cst1, Cst2, Cst3 & Cst4 customer details have been displayed, i.e., inner join collects the common data from two tables and gives it as an output. Inner joins are utilized to create data models, and they help to join the tables in attribute view. Inner joins are also used to join the fact tables with the master data tables for creating Analytical views. 

The following script will help you to create the inner join; T1 is table 1 and T2 is table2: 

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";

Left Outer Join: 

A left outer join returns all records from the left table and matching records from the right table. If left table records do not match with the right table records then it returns a null value. 

If you consider the above tables, all columns and rows in Customer details will be fetched and joined with the matched records of the Order details table. 

The following script will help you to create the left outer join in SAP HANA: 

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";

Right Outer Join: 

A right outer join returns all right table records and left table matched records. This join is used very rarely in real scenarios. In the same left outer join, here also if the left table data does not match with the right table data then it returns Null value.

If you consider the above tables, here, in this case, the right table will be the Order details, and the left table will be the Customer details. All right table records will be fetched and associated records in the left table. 

The following script will help you to create the right outer join in SAP HANA: 

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";

SAP HANA Union: 

SAP HANA Union returns all records from the left and right tables irrespective of matching the records in either of the tables. It returns a Null value if it does not find any associated records with another table. It is also called Full Outer Join in SAP HANA. 

In this case, all the records of Customer details and Order details tables will return in the following table. 

The following script will help you to create SAP HANA Union: 

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";

Specific Joins

There are two types of Specific SAP HANA Joins:

Referential Join: 

Referential join is the default join type in the SAP HANA model. A referential join is like an inner join. The main distinction between the two is referential honesty must be guaranteed on account of referential join else it can't be framed. In this way, before we push ahead with understanding referential honesty. Let us get familiar with the fundamental idea of a referential join. It is framed between a reality table (exchange information or Analytic View) and an ace information table( Attribute View). Each ace information table has an essential key section which goes about as an unfamiliar key in the real table. 

Referential joins in SAP HANA are utilized at whatever point there are an essential key and unfamiliar key relationship between two tables. Also, referential integrity is when for each incentive in the foreign key section, there is a reference to an incentive in the primary key column of the master table information. From a presentation perspective, referential joins are superior to internal joins. Referential joins are suggested for star schemas as long as referential integrity is sustained.

Text Join:

Text Join in SAP HANA gives a portrayal of text records in the language explicit to the client. If client A has chosen a language like German, at that point all the insights about the table and segments will be shown to the client in German. We use text joins to join a book table with a master table information. The content table must have a primary key column connected to the next information table and a language key column that contains client language inclinations. Text joins are additionally utilized with SAP tables having SPRAS language columns.

Conclusion

Here we’re concluding our blog on Join and Unions in SAP HANA. We hope you understand about joins and variant types of joins in SAP HANA. If you’ve any queries and suggestions please comment below and our experts will reach out.  

For in-depth knowledge on SAP HANA, click on below

• Performance Tuning in SAP HANA
• Security in SAP HANA
• Organizing system Landscapes in SAP Hana
• Analytical Privileges in SAP HANA
• SAP HANA Views