SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing
+
-
Predictive Analysis

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.
SAP HANA Join Types

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.
SAP HANA Inner Join
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:
SAP HANA Inner Join

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.
SAP HANA Left Outer Join
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:
SAP HANA Left Outer Join

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.
SAP HANA Right Outer Join
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:
SAP HANA Right Outer Join

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.
SAP HANA Full Outer Join
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:
SAP HANA Full Outer Join

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.


Support us by sharing this article.



Explore More
Close X
Close X

12 thoughts on “SAP HANA Join Types

  1. Hari Kumar says:

    Is Full Outer Join is same as Inner Join as per the definition?

    • Diganto G says:

      NO.full outer joins both tables w.r.t the key, get the cross product even though a match does not exist.

      INNER will pull out data from both tables only when a key match exists

  2. Vijay D says:

    Simply saying Inner join is the intersection of both the tables, whereas full outer join is a union of the two tables. Hope this helps !

  3. Vignan says:

    Can you please explain, how database referential integrity on tables. And how can we ensure that.

  4. freddy jesus estanga says:

    Thanks for the tips, but I have a question, what if I want to condition the inner join?

    That is to say if the material does not have description in the table of text X1, that looks for in the table x2

    • Admin says:

      Hi Freddy,
      Currently the conditions are not allowed in Inner join in modeling view.
      You may use scripted calculation view.

      Regards,
      Admin

  5. Ganesh says:

    Hi

    Can some one confirm, can we use referential join in calculation views?

    Thanks.

    Regards,
    Ganesh

  6. Harik says:

    Hi,
    In the above the definition for FULL outer join speaks about inner join “The INNER JOIN selects the set of records that match in both the Tables”

  7. Oliver says:

    I know this is not a support forum, but please let me know if it can be achieved using a JOIN.

    I have two tables, both have the same fields:
    1) calendar week
    2) revenue

    Table 2015 has all 52 weeks with revenue, table 2016 only has 11 rows.

    If I do a LEFT OUTER JOIN (SUM ((2015 + 2016) /2)), I will get only “?” as revenue for 2017 after 12th row.
    Can the non existant values simply be ignored?

Leave a Reply

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

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy