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.

Create Schema - 1.JPG

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

Screen shots of table from excel - 2.JPG

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

Creating Employee Table - 3.JPG

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

Inserting into Employee table - 4.JPG

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

Employee Table open content - 5.JPG

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

Creating Booking table - 6.JPG

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

Content of Booking table - 7.JPG

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

Inner Join Diagram - 8.JPG

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

Inner Join HANA - 9.JPG

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

Left Outer Join Diagram - 10.JPG

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”


Left Outer Join SQL - 11.JPG


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.


Right Outer Join Diagram - 12.JPG


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”


Right Outer Join SQL - 13.JPG


FULL OUTER JOIN –


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


Full Outer Join Diagram - 14.JPG


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”;


Full Outer Join SQL - 15.JPG


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

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Christian Rommel

    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

    (0) 
    1. Lars Breddemann

      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

      (0) 
  2. Jim Yan

    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”

    (0) 
  3. prem kumar

    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?

    (0) 

Leave a Reply