SAP HANA Join Types

In the article SAP HANA Modeling Introduction and SAP HANA Calculation View we explained the basics of SAP HANA data modeling. 
In this article we will learn about different types of Joins in HANA.

A Join clause combines records from two or more tables /view in a database. 
SAP HANA supports following types of join.

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Referential Join
• Text Join

In this article you will learn the details of all these type of joins. 

We are going to use 2 tables to explain the different types of Join. CUSTOMER and SALES_ORDER. 

SQL Script to create the above mentioned tables:

-- REPLACE <Schema_Name> WITH YOUR SCHEMA

CREATE COLUMN TABLE <Schema_Name>."CUSTOMER" (
        "CustomerID" nvarchar(10) primary key,
        "CustomerName" nvarchar(50)
);
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C1', 'Alfred');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C2', 'John');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C3', 'Maria');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C4', 'Harry');

CREATE COLUMN TABLE <Schema_Name>."SALES_ORDER" (
        "OrderID" integer primary key,
        "CustomerID" nvarchar(10),
        "Product" nvarchar(20),
        "Total_Units" integer
);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (101, 'C1','Camera',300);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (102, 'C1','Mobile',200);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (103, 'C2','iPod',500);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (104, 'C3','Television',400);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (105, 'C5','Laptop',800);

Inner Join:

The INNER JOIN selects the set of records that match in both the Tables. 

Syntax 

SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         INNER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Inner Join will be like this: 

Where to Use?

Inner join should be used if referential integrity is ensured.
Inner Join is much faster that Outer Join thus is the preferred solution if possible from semantical perspective
Attribute View: Inner Joins can be used to join different master data tables to a joint dimension Analytical Views: Inner Joins can be used if referential integrity cannot be ensured

Left Outer Join:

The Left Outer Join selects the complete set of records from first table (CUSTOMER), with the matching records (where available) in second table (SALES_ORDER). If there is no match, the right side will contain null.

Syntax 

SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         LEFT OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Left Outer Join will be like this: 

Right Outer Join:

The Right Outer Join selects the complete set of records from second table (SALES_ORDER), with the matching records (where available) in first table (CUSTOMER). If there is no match, the left side will contain null.

Syntax 

SELECT T2."OrderID", T2."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         RIGHT OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Right Outer Join will be like this: 

Full Outer Join:

The FULL OUTER JOIN keyword selects all rows from the left table (table1) and from the right table (table2). 
The FULL OUTER JOIN keyword combines the result of both LEFT OUTER and RIGHT OUTER joins. 

Syntax 

SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         FULL OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Full Outer Join will be like this: 

All the 4 types of joins explained above are standard database join types. 
SAP HANA also supports 2 new type of joins. Referential Join and Text Join. These 2 type of joins can only be used in modeling views. 
Let's take a look into these 2 new type of joins. 

SAP HANA Referential Join:

Referential Join is semantically an inner join that assume that referential integrity is given. 

Note: Referential integrity is the property of database which ensures that each foreign key value in a table exists as a primary key in the referenced table. 

Referential join is performance wise better than inner join, but only be used when you are sure that referential integrity is maintained. 

SAP HANA Text Join:

Text Join is used in order to get language-specific data. 

You have a product table that contains product IDs without descriptions and you have a text table for products that contains language-specific descriptions for each product. You can create a text join between the two tables to get the language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column.