All about Joins using SQL in HANA

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

Source: scn.sap.com