SAP HANA SQL script concepts- SQL JOIN, UNION, 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. We also learn the concept of combining data sets using the SQL UNION whilst understanding the difference between UNION and UNION ALL. 

SQL JOIN

Before we get started on this section, please read the article on SQL JOIN explaining joins and join types in SAP HANA.

Joins are created when the information we need is split across different tables. 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.

The basic template for an SQL JOIN is:

SELECT <field_names> from

<left_table>  <left_alias>

<right_table> <right_alias>

ON

<join_condition>

Note: You can add WHERE, GROUP BY, HAVING and ORDER BY clause as per the requirement just like a regular SELECT condition.

As 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. 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. The opposite is true for a RIGHT OUTER JOIN.

When you have two or more tables, using aliases are important for both readability and coding. Each time you write a field name from a table, use it with the alias name as <alias>.<field_name> so that SAP HANA knows which table it comes from.

After the ON keyword, specify the join condition which would be the relationship between the fields that connect these two tables.

So the first thing to do is to decide the type of SQL join you wish to use. Most times, a join would be LEFT OUTER JOINs or INNER JOINS. 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. Even if we do a LEFT OUTER or an INNER JOIN, both would return the same values in this case. Now, the decision boils down to performance. Which one of these joins would be faster? The answer is a LEFT OUTER JOIN. 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. An INNER JOIN on the other hand scans both sides.

Using this logic, we draft the below code. 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). We know that EMP_ID exists in both tables. As a thumb rule, try to always pick up common fields from the left table. After the ON keyword, we specify the join condition as ename.EMP_ID = emast.EMP_ID which means that the connecting fields of these two tables is the EMP_ID.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

On execution, we see the output as if all this data was coming in from a single table.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

As explained earlier, you can add more conditions to this statement like any other SELECT statement that we learnt till now. Below, we see an example where we display only the earlier statement with values for which age is more than or equal to 25 and then we order it by employee ID in descending format.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

The output filters and sorts as below.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

If you are starting to find this complicated, please try this on your system and  try changing the clauses, play around using your creativity to modify these select statements and their outputs to get a better feel of how things work. Don’t worry with some practice, you will find these really easy.

Now, in practical real time project scenarios, there are often more than two tables involved in joins. For example, if we had a requirement to enhance the above join with the salary field from EMP_SALARY table, we would need to add another join condition below the first one but before the WHERE clause.

After the first join executes, the result executes as the left side of the next join. In this way you can add as many joins as required. All their WHERE conditions should come in at the end. This new draft would look like the below. Leave a comment in the comment section below if you feel you have questions on this topic.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

The output now looks as the below.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

You can add more formulas to this statement like the BONUS and FULL_NAME calculations we did earlier.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

We have the new fields as shown below, You can change the position of these columns by simply moving it to the position you like in the SELECT statement.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

UNIONS

DIFFERENCE BETWEEN UNION AND UNION ALL IN SAP HANA

Another important operation when in data modelling is UNION. When you have two or more separate lists of data having the same or similar fields, you might want to combine them both vertically into a unified single list. For example, let’s say you need to pick the first five entries of employee IDs from EMP_SALARY table where country code is in the list IN, CA or US. Also you need a similar list for country codes DE, FR or CH. When you have these lists, it is required to combine these five from each result set into one single set of 10. In such a case, we write the below code.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

Result is as shown below. We have the first 5 records from each of the two result sets.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

Now we change the code slightly and also include IN as a filter in the second select statement. This now means that IN records may occur in both result sets. Let’s see the execution results.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

As seen below, five and five don’t always equal ten. There were some records that were common between the first five and the next five and their duplicates got removed/aggregated. That is how a UNION works.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

Now what if you don’t want this aggregation to happen. What do you do?

The answer comes in the form of UNION ALL. It does the same work as UNION whilst not removing any duplicates/aggregation operation. Let’s now see the same example with UNION ALL. Try executing it.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

In the result we see the two entries that have duplicates. Row 1 and 2 are the same as row 6 and 7. These were aggregated during the UNION operation earlier but remain in the output in case of UNION ALL.

SAP HANA SQL SCRIPT SQL JOIN UNION ALL LEFT OUTER JOIN INNER JOIN

Now you might be thinking as to why would someone preserve duplicates. What’s the point of doing so. The answer is that usually we wouldn’t want to have duplicates in result set and should be using UNION. But whenever you are sure that the two datasets involved in the UNION will have distinct datasets and have no duplicates, always do UNION ALL since it doesn’t waste time and system resources to remove duplicates and hence is faster in terms of performance.

I hope this was easy to understand and follow. Stay tuned for the next set of HANA SQL Tutorials.

Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.

<<Previous Tutorial                                                                                                              Next Tutorial>>

Tagged , , , , , , , , , , , , , , , , , , , , , , .

71 Comments

  1. 天冷就回来_散文精选_心情随笔  所谓的难事都是相对于不同的人而言,只要坚持努力,困难总会解决。 http://www.ourengagementstory.com/blog/the-other-4-cs-of-engagement-ring-shopping/#comment-510516   我想出去闯闯,看看外面的世界,他对母亲说。母亲端详地看着他,只说了一句:天冷就回来 https://healthydebate.ca/2015/02/topic/politics-of-health-care/philanthropy#comment-3226239 。  当年的秋天,他乘坐火车从贵阳去到武汉,历经近17个小时的车程,从熟悉的城市到达一座从未踏足的陌生的城市,落脚于一个便宜的出租房。出去走一走,看看这个大世界,并为之奋斗一份事业,是他埋藏心里二十余年的梦。如今出来了,就意味着踏出了最艰难的一步,接下来就是另一步。他想,世界那么大,有的人可能为了不同的美食而走出去;有的人为了体验不同的生活而走出去;有的人为了结识不同的朋友而走出去;有的人为了认识另一个自己而走出 https://myeuc.net/2015/02/12/custom-av/#comment-9278

  2. 昆曲《孔子之入卫铭》_散文精选_心情随笔  早在一月前就获悉北方昆曲剧院将于唐山大剧院上演新编历史剧 https://www.jgspiers.com/pvs-reverse-image-with-vmware-vcenter-converter/#comment-86178 《孔子之入卫铭》,凡心所向,素履皆往,唐山作为评剧之乡,昆曲的演出机会是不多的,于是呼朋引伴,邀了两个同行者,心中不胜欢忭。  在高中时期爱上了“养在 http://www.nj-bridge.com/ad/Review.asp?NewsID=492 深闺人未识”的小众艺术——昆曲,单是看字字珠玑的戏词,就齿颊留香。昆曲不同于京剧大锣大鼓的喧闹,声音清丽婉转,宜静听。晚清之前戏子的地位卑微,甚至不如妓女,随着地位的提高,如今被尊称为“艺术家”,才能有资格演绎先贤圣哲孔子的形象。  音乐和服装都是本次的亮点。为了更好营造宫廷音乐的氛围,配乐使用吹管、弹拨与打击乐器,舍弃了当时尚未出现的乐器,除了 http://www.zhuitian3.com/forum.php?mod=viewthread&tid=224290

  3. Howdy! I could have sworn I’ve visited this site
    before but after looking at a few of the articles I
    realized it’s new to me. Anyhow, I’m certainly delighted
    I came across it and I’ll be bookmarking it and checking back regularly!
    cheap flights 3gqLYTc

  4. Hi there, i read your blog from time to time and
    i own a similar one and i was just curious if you get a lot of spam comments?
    If so how do you stop it, any plugin or anything you can advise?
    I get so much lately it’s driving me crazy so any support is very much appreciated.
    cheap flights 32hvAj4

  5. Hi there this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding know-how so I wanted to get advice from someone with experience. Any help would be enormously appreciated!

  6. Hi I am so excited I found your blog, I really found you by
    mistake, while I was looking on Bing for something else,
    Anyways I am here now and would just like to say cheers for a
    fantastic post and a all round entertaining blog (I also love the theme/design), I don’t have time
    to go through it all at the moment but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the fantastic b.

  7. You actually make it seem so easy with your presentation but I find this
    topic to be actually something which I think I would never understand.
    It seems too complex and extremely broad for me.
    I am looking forward for your next post, I will try to get the
    hang of it!

  8. Thank you for the auspicious writeup. It actually was once
    a amusement account it. Glance advanced to far introduced agreeable
    from you! However, how could we keep up a correspondence?

  9. Woah! I’m really enjoying the template/theme of this blog.

    It’s simple, yet effective. A lot of times it’s difficult to get
    that “perfect balance” between usability and appearance. I must say you’ve done a very good job with this.
    In addition, the blog loads super quick for me on Opera.
    Exceptional Blog!

  10. Howdy! I could have sworn I’ve been to this web site before but after going through a few of
    the articles I realized it’s new to me. Anyways, I’m definitely
    pleased I found it and I’ll be bookmarking it and checking back regularly!

  11. I’m really loving the theme/design of your website.

    Do you ever run into any internet browser compatibility issues?
    A few of my blog audience have complained about my website not operating correctly in Explorer
    but looks great in Firefox. Do you have any advice to help fix this problem?

Leave a Reply

Your email address will not be published. Required fields are marked *