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
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-sap-hana-and-supported-reporting-tools
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-data
Just thought of mentioning in interest of forum ℹ
If difficulty in viewing screen shot, download as PDF and refer.
Very Informative..
First of all – joins extremely slowing HANA executions )
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
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
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
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”
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?
Please have a look at this video aswell.
https://youtu.be/6g_G53U9CYA
https://youtu.be/-4gQEvh_Ndo
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