Skip to Content

All about Joins using SQL in HANA

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

12 Comments
You must be Logged on to comment or reply to a post.
  • 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?

  • 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