As a thumb rule, try to always pick up common fields from the left table. We know that EMP_ID exists in both tables. As you can see below, I give aliases to the two tables involved as ename and emast (you can call them anything but it should look relevant to your table name). Using this logic, we draft the below code. An INNER JOIN on the other hand scans both sides. As discussed in our SQL JOIN document, LEFT OUTER JOIN only has to scan the key values on the left side table and picks up the corresponding entries on the right table. Which one of these joins would be faster? The answer is a LEFT OUTER JOIN. Now, the decision boils down to performance. Even if we do a LEFT OUTER or an INNER JOIN, both would return the same values in this case.
In our case, both tables contain the same key entries – 50 values of employee ID so it doesn’t matter which one we keep on the left or right. Most times, a join would be LEFT OUTER JOINs or INNER JOINS. So the first thing to do is to decide the type of SQL join you wish to use. so that SAP HANA knows which table it comes from.Īfter the ON keyword, specify the join condition which would be the relationship between the fields that connect these two tables. Each time you write a field name from a table, use it with the alias name as. When you have two or more tables, using aliases are important for both readability and coding. The opposite is true for a RIGHT OUTER JOIN. The LEFT table dictates the list of key field values for which corresponding values must be picked up from the table on the right side in a LEFT OUTER JOIN. Note: You can add WHERE, GROUP BY, HAVING and ORDER BY clause as per the requirement just like a regular SELECT condition.Īs you might have read in my document on JOINS, the order of tables matters a lot in case of LEFT and RIGHT OUTER JOINS but not in INNER JOIN. For example, if there is a requirement to provide the employee ID, first name, email and age of an employee, we know that the data exists but exists in two separate tables, namely EMP_NAMES and EMP_MASTER. Joins are created when the information we need is split across different tables. SQL JOINīefore we get started on this section, please read the article on SQL JOIN explaining joins and join types in SAP HANA. We also learn the concept of combining data sets using the SQL UNION whilst understanding the difference between UNION and UNION ALL.
Welcome to the final installment of this SAP HANA SQL Scripts core concepts section where we learn how to pick up related data from different tables using SQL JOIN. SAP HANA SQL script concepts- SQL JOIN, UNION, UNION ALL