All about Joins using SQL in HANA

[Former Member]

Hi All,

We all know about Joins and concept .

Lets do it in HANA studio and cover below points :

• Overview of HANA studio
• Joins concept
• Creating Schema
• Creating Tables
• Insert Values into Tables
• Using SQL to understand below Joins in HANA Studio

             - Inner Join

             - Left Outer Join

             - Right Outer Join

             - Full Outer Join

First lets create Schema in which tables will be created for Analysis

We are writing code to create Schema - Joins_Demo, refer below screen shot for same.

We are going to create below two tables in Schema which will be used for explaining the Joins

Refer to below SQL code which is used for creating table "Employee_Table" in Schema which we created earlier

Refer to below screen shot which is used for inserting values into table which we created just now

Right Click on table and select open content, you will see the entries into table just now

Now lets create second table "Booking_Details", refer to below code for creating table and inserting values

Right Click on the table just created and select open content, this will display entries which we inserted in previous step

Lets start with Joins now as we have Schema and two tables created

Inner Join -

Inner Join selects the set of records that match in both the tables

SQL Code

SELECT A2."Booking_ID", A1."Employee_ID", A1."Employee_Name", A2."Product_ID", A2."Total_Units"

FROM "Employee_Table" AS A1

INNER JOIN "Booking_Details" AS A2

Left Outer Join -

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

SQL Code -

SELECT A2."Booking_ID", A1."Employee_ID", A1."Employee_Name", A2."Product_ID", A2."Total_Units"

FROM "Employee_Table" AS A1

LEFT OUTER JOIN "Booking_Details" AS A2

ON A1."Employee_ID" = A2."Employee_ID"

Right Outer Join -

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

SQL Code-

SELECT A2."Booking_ID", A1."Employee_ID", A1."Employee_Name", A2."Product_ID", A2."Total_Units"

FROM "Employee_Table" AS A1

RIGHT OUTER JOIN "Booking_Details" AS A2

ON A1."Employee_ID" = A2."Employee_ID"

FULL OUTER JOIN -

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

SQL Code -

SELECT A2."Booking_ID", A1."Employee_ID", A1."Employee_Name", A2."Product_ID", A2."Total_Units"

FROM "Employee_Table" AS A1

FULL OUTER JOIN "Booking_Details" AS A2

ON A1."Employee_ID" = A2."Employee_ID";

See You Soon with more Blogs and Documents. Please provide feedback

[Former Member]

Few more blogs on HANA which might interest the audience :

Analysis of Project Data using HANA :

http://scn.sap.com/community/hana-in-memory/blog/2014/05/03/analysis-of-shift-data-of-project-using-...

Part 1 of Text Analysis - Twitter Data :

http://scn.sap.com/community/hana-in-memory/blog/2014/05/16/text-analysis-of-ipl-match-using-twitter...

Just thought of mentioning in interest of forum ℹ

[Former Member]

If difficulty in viewing screen shot, download as PDF and refer.

[Former Member]

Very Informative..

[Former Member]

First of all - joins extremely slowing HANA executions )

[Former Member]

These Joins are same across all the Database's.

it would be more informative, if you can add Refrential and Text Joins which are exclusive to HANA

[Christian_R_]

Hey, thanks for the great post. I was just trying an inner join and realized that I get syntax errors if I don't declare an attribute the join should be placed on.

E.g.:

SELECT Member."LASTNAME", Member."FIRSTNAME", Member."GITUSER", Membership."FUNCTION"

FROM "OSS"."sap.devs.apps.oss.data::tableDeclaration.Member" AS Member

INNER JOIN "OSS"."sap.devs.apps.oss.data::tableDeclaration.Membership" AS Membership

DIDN'T Work...I had to add

ON Member.MID = Membership.MID

to make it work. If this is the case for all querys in the new SPS I'd be glad to know.

Thanks, Christian

[lbreddemann]

Hmm.. reading the SQL 2003 foundation standard I don't find anything in there that says:

if you specify an INNER JOIN and don't specify join conditions than this or that should happen.

Although a common resolution of many DBMSes is it to silently perform a CROSS JOIN in this case, this is not exactly obvious.

For the example given, where "Member" is has a relationship to "Membership" not providing the join condition is simply a mistake on the side of the author of the SQL statement.

Depending on your needs, you could still try and resort to a NATURAL JOIN, but as this kind of hides the actual join columns I would discourage that.

- Lars

[jimmycat]

Just some syntax errors ... for example:

SELECT

     A2."Booking_ID",

     A1."Employee_ID",

     A1."Employee_Name",

     A2."Product_ID",

     A2."Total_Units"

FROM "JOIN_DEMO"."Employee_Table" A1

INNER JOIN "JOIN_DEMO"."Booking_Details" A2 ON A1."Employee_ID" = A2."Employee_ID"

[former_member182337]

Thanks. It's much useful. One doubt, what if multiple joins are used? Which one takes the precedence? Like

Select TABLE1.A , TABLE2.B, TABLE3.C

from TABLE1 left outer join TABLE2 on TABLE1.A = TABLE2.A

inner join TABLE3 on TABLE2.B = TABLE3.B

If the inner join fails would there still be an output?

[Former Member]

Hello There,
This is one awesome blog. Much thanks again. Fantastic.
We are busying testing an upgrade to <a href="https://asha24.com/blog/sap-hana-studio-download-and-installation-guide">SAP9.3 PL02</a>and it failed on the 2 schemas we are testing on. This in a test environment.
I want to drop the schemas and try on a more recent backup as I did not get an error code reason or note for the failed upgrade.
However, when I try to drop those schema's I get an error:
[129]: transaction rolled back by an internal error: Allocation failed; $size$=648; $name$=libhdbrskernal.so; $type$=pool; $inuse_count$=632593; $allocated_size$=530359301
I get the same error by trying to right click on the schema and delete(CASCADE) or if i use the drop schema <SCHEMANAME> cascade;
Does anyone know how to resolve this issue?

Anyways great write up, your efforts are much appreciated.
Kind Regards,
Kevin