Joins in HANA SQL
Joins are frequently used in database in SELECT statements to retrieve data from two or more tables.
Create below two tables in HANA database
Customer master table
| CustomerID | CustomerName | CustomerPhone | CustomerEmail |
| C1 | Keerti | 89721 | keerti@gmail.com |
| C2 | Emily | 45192 | emily2email@yahoo.com |
| C3 | Senthil | 78301 | senthil@hotmail.com |
| C4 | Pradeep | 56720 | pradeep_rastogi@gmail.com |
| C5 | Harsh | 78921 | harshavardhan@rediffmail.com |
| C6 | Preeti | 78401 | preeti@gmail.com |
Sales Table
| OrderID | OrderDate | CustomerID | Product | Units_Sold |
| 2345 | 14/4/2021 | C1 | P1 | 10 |
| 2316 | 16/9/2020 | C4 | P3 | 20 |
| 2347 | 17/4/2021 | C2 | P2 | 2 |
| 1314 | 01/03/2019 | C3 | P1 | 12 |
| 1315 | 01/04/2019 | C5 | P1 | 200 |
| 1617 | 20/12/2019 | C2 | P4 | 40 |
| 1213 | 01/03/2018 | C7 | P1 | 30 |
| 1890 | 21/03/2019 | C8 | P2 | 5 |
Product Master table
| ProductID | ProductName | ProductPrice | ProductColor |
| P1 | Pen | 10 | Black |
| P2 | Ink | 20 | Blue |
| P3 | Pencil | 5 | Red |
| P4 | Notepad | 15 | NA |
| P5 | Plain Paper A4 Size | 2 | NA |
You can create the table using the wizard or using plain SQL command window
Go through the video to understand these options.
Once the tables are created, you can analyze the data based on join conditions.
Joins can be created using SQL or using ‘Views’ option in hana studio.
Verify this with your results-
Inner join between customer and sales table will give 6 records (common data is returned)
Inner join between sales and product table will give 8 records (common data is returned)
Left Outer Join between Customer and Sales table with Customer as Left table will return 7 records (first it will return inner join records and then whatever extra data in Left table is also returned in the result set)
Right outer join between Customer and Sales table with Customer as Right table will return 8 records (first it will return inner join records and then whatever extra data in Right table is also returned in the result set)
Full Outer Join between Sales and Product table will give 9 records ( first it will return inner join records and then whatever extra data in Left table and extra in Right table are also returned in the result set)
Note: For above joins, there should be one common field between the tables on which join is applied
Union gives all the data from two table one after the other irrespective of common column is present or not.
The only criteria to apply union is that the structure of tables must be similar (same number of columns)
The result of union is all the records from both the tables one after the other.
UNION ALL is better performance wise as system does’nt remove duplicate data records here.
In UNION duplicate data records are removed and hence it takes more time to display the result set.
Apart from these we have some more joins in HANA which we will see in HANA modeling (star, referential, text, temporal etc.)
