SAP HANA: Achieving Set operations using Joins

Hi Guys,

Jody Hesch has written an excellent writeup Thinking in HANA – Part 1: Set Operators on how to achieve SET Operations using Calculation Views with UNION node. In this document i intended to show the set operations using JOINS. Thanks Jody for a wonderful document, will be waiting for your Part -2

Now we can have multiple discussions. As we know that achieving with UNION is better than JOINS. But again it depends on the volumes of the data and the place at which you are applying it.

As per our experiences, we also know that if we can achieve with the help of Attribute /Analytic Views it is better than doing the same with Calculation View Graphical / SQL. In this document i have tried to achieve with Attribute views as much as possible and while doing this i came to know about the Full Outer Join option through graphical models is not possible anymore.

These are 7 questions on which Jody has discussed,

Here are our Tables:

Table 1:

Table 2:

Revision: 68

1) What are all the elements in both data sets?

SQL Solution: (Similar to UNION)

SELECT
COALESCE(T1."ID",T2."ID") AS "ID",
COALESCE(T1."NAME",T2."NAME") AS "NAME",
COALESCE(T1."LAST_NAME",T2."LAST_NAME") AS "LAST_NAME",
COALESCE(T1."ROLE_ID",T2."ROLE_ID")AS "ROLE_ID"
FROM "EMPLOYEE" T1 FULL OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID ;

Result:

Note: Full outer join option not coming for any of the Graphical HANA models.

2) Which elements are exclusive to SET 1?

SQL Solution: (MINUS/EXCEPT)

SELECT
T1."ID",
T1."NAME",
T1."LAST_NAME",
T1."ROLE_ID"
from "EMPLOYEE" T1 LEFT OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL;

HANA Models:

1) Create a Attribute view with Left outer join on both the tables

2) Consume the Attribute view in the Graphical calculation view to apply filter “T2.ID” is NULL after the joining takes place. ( You cannot apply filter after joining using attribute View).

Result:

3) Which elements are exclusive to SET 2?

Similar to above mentioned solution, you need to select the fields from Table 2 and filter on T1.ID IS NULL.

4) Which elements are in both sets?

SQL Solution: (INTERSECTION)

SELECT
T1."ID",
T1."NAME",
T1."LAST_NAME",
T1."ROLE_ID"
FROM "EMPLOYEE" T1 INNER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID;

Attribute View Solution:

Result:

5) Which elements are in SET 1 (non-exclusive)?

SQL Solution:

SELECT
T1."ID",
T1."NAME",
T1."LAST_NAME",
T1."ROLE_ID"
FROM "EMPLOYEE" T1 LEFT OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID;

Attribute View Solution:

Result:

6) Which elements are in SET 2 (non-exclusive)?

SQL Solution:

SELECT
T2."ID",
T2."NAME",
T2."LAST_NAME",
T2."ROLE_ID"
FROM "EMPLOYEE" T1 RIGHT OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID;

Attribute View Solution:

Result:

7) Which elements are in SET 1 or SET 2?

SQL Solution: (Similar to UNION Without duplicates)

SELECT
COALESCE(T1."ID",T2."ID") AS "ID",
COALESCE(T1."NAME",T2."NAME") AS "NAME",
COALESCE(T1."LAST_NAME",T2."LAST_NAME") AS "LAST_NAME",
COALESCE(T1."ROLE_ID",T2."ROLE_ID")AS "ROLE_ID"
FROM "EMPLOYEE" T1 FULL OUTER JOIN "NEW_EMPLOYEE" T2 ON T1.ID = T2.ID
WHERE T1.ID IS NULL OR T2.ID IS NULL ;

Result:

Note: Full outer join option not coming for any of the Graphical HANA models.

As i have just shown the alternate ways to achieve it, i’d assume there must be other ways too and decision is left to you on when to choose which kind of approach based on performance.

Hoping you guys help in colloborating with this document to understand the best approach w.r.t performance. And correct me in tuning this approach.

Yours

Krishna Tangudu