SQL Join Types in SAP
Hello one and all. Welcome to the next edition of our SAP HANA Tutorial series. This is another one of our checkpoint courses where we understand one of the basic concepts of database design – SQL Join Types in SAP. Since this is a common tutorial, all of these types may not be applicable to ABAP, BW and Enterprise HANA. Each of them supports some of these which we will understand in each of those courses individually. But for now, this tutorial is to understand what joins mean.
Most of the times, one table does not contain all the data needed for analyzing the problem. You might have to look into other tables to find the other fields that you need. The primary way to achieve this is via joins. But there are different types of joins. Let’s look at each of them with an example.
Fruits make everything simple..even SQL Join Types
Five students in a class are asked to input their favorite fruit and vegetable into two database tables. They are free to not enter any fruit or vegetable if they don’t like any.
Table 1: The Fruit table
| Student | Fruit |
| Shyam | Mango |
| John | Banana |
| David | Orange |
| Maria | Apple |
Table 2: The Vege-Table
| Student | Vegetable |
| Shyam | Potato |
| David | Carrot |
| Maria | Peas |
| Lia | Radish |
After the data entry is complete, we can see that Lia doesn’t like any fruit and John doesn’t like any vegetable apparently.
SAP SQL Join Types #1 : Inner Join
Now, let’s say our first analytical requirement is to get a singular view of all the students with fields (Student, Fruit, Vegetable) who like at least one fruit and at least one vegetable. This is a classic example of an INNER JOIN.
| Student | Fruit | Vegetable |
| Shyam | Mango | Potato |
| David | Orange | Carrot |
| Maria | Apple | Peas |
An INNER JOIN returns a view with result set that is common between all involved tables.
SAP SQL Join Types #2: Left Outer Join
The next requirement we have is to get a singular view of all the students with fields (Student, Fruit, Vegetable) who like at least one fruit even if they don’t like any vegetables. This type of join is a LEFT OUTER JOIN.
| Student | Fruit | Vegetables |
| Shyam | Mango | Potato |
| John | Banana | ? |
| David | Orange | Carrot |
| Maria | Apple | Peas |
A LEFT OUTER JOIN returns all the entries in the left table but only matching entries in the right
Note: The ‘?’ in the data is a NULL entry. As discussed earlier, NULL denotes the existence of nothing. NULL is not blank or zero. It’s just nothing. Since John likes no vegetables, a null value is placed there. In SAP BW and ABAP output, NULL maybe represented by ‘#’ whereas in Enterprise HANA, ‘?’ is the default display of NULL values.
SAP SQL Join Types #3: Right Outer Join
Next, we now require a singular view of all the students with fields (Student, Fruit, Vegetable) who like at least one vegetable even if they don’t like any fruits. This is exactly the converse of our previous requirement. This type of join is a RIGHT OUTER JOIN.
| Student | Vegetables | Fruit |
| Shyam | Potato | Mango |
| David | Carrot | Orange |
| Maria | Peas | Apple |
| Lia | Radish | ? |
A RIGHT OUTER JOIN returns all the entries in the right table but only matching entries in the left
Note: This is kind of a redundant type of join as the position of these tables can be reversed and a LEFT OUTER JOIN can be applied to achieve the same results. For this same reason, RIGHT OUTER JOIN is rarely used and is also considered a bad practice in terms of performance of SAP HANA views. So try to avoid using it.
SAP SQL Join Types #4: Full Outer Join
Next, we require the data of all the students with fields (Student, Fruit, Vegetable) who like at least one fruit or at least one vegetable. This is a classic example of an FULL OUTER JOIN.
| Student | Fruit | Vegetables |
| Shyam | Mango | Potato |
| John | Banana | ? |
| David | Orange | Carrot |
| Maria | Apple | Peas |
| Lia | ? | Radish |
A FULL OUTER JOIN returns all the data from all involved tables regardless of whether they have matching values or not on the join condition.
This one is rarely used as we never usually need all the key values from both tables. As seen from the example, this results in a lot of nulls. Nevertheless, it is a rare need.
The main type of JOINS actually used in real time scenarios are INNER JOINS and LEFT OUTER JOINS. LEFT OUTER JOINS are the preferred join type in terms of performance as they require only scanning of one table to complete the join.
SAP HANA SQL Script Specific Join Types
There are two other join types available in enterprise HANA called REFERENTIAL JOIN and TEXT JOIN. A REFERENTIAL JOIN is the same as an INNER JOIN but better in performance but should only be selected when it is sure that the referential integrity is maintained which in regular human English translates to mean that all values for the join condition fields in the left table exist in the right table for their corresponding fields as well. This is very difficult to guarantee and hence due to this involved risk, a REFERENTIAL JOIN is best left untouched.
A TEXT JOIN is used for tables containing descriptions of fields. The text tables/descriptive tables are always kept on the right hand side and their language column needs to be specified.
A TEXT JOIN usually looks like the below.
Key Table:
| Student | Vegetable code |
| Shyam | PO |
| David | CA |
Text Table:
| Vegetable Code | Description | Language |
| PO | Potato | EN |
| PO | Kartoffel | DE |
| CA | Carrot | EN |
| CA | Karotte | DE |
which join to become the below table with descriptions of the same vegetable in two different languages – English and German
| Student | Vegetable Code | Description | Language |
| Shyam | PO | Potato | EN |
| Shyam | PO | Kartoffel | DE |
| David | CA | Carrot | EN |
| David | CA | Karotte |
DE |
Note: As you might have already noticed, I have referred to the resultant output of joining tables as “Views”. Views is the actual technical term for this result set. A view is just a virtual output of a join and is not persisted/stored on the disk like a table. The result set of a view only exists during run-time.
Thank you for reading this tutorial on joins and if you liked it, please show your support by sharing this document across social media by pressing the share buttons below and also don’t forget to subscribe to our newsletter for alerts on new tutorials that are added regularly.
Happy Learning!