SQL UNION and SQL UNION ALL in SAP 

Welcome again to this new tutorial on SQL UNION in SAP where we discuss the UNION of data sets and try to understand the difference between UNION and UNION ALL. Unions happen in SAP BW as well as enterprise HANA graphical views and in SQL Script UNION statements. This one would be a short and simple tutorial with an example as always.

The SQL UNION in SAP

Consider a data set for North America division of a company

Product
Shoes
Bags
Gloves

Now assume that we have another data set for the South American division of the same company

Product
Shoes
Caps


1

To get the entire product portfolio of the company in the American region, the operation required between these two data sets would be a UNION. This is because a UNION vertically combines data sets like piling one stack of potatoes. The result would be as shown in the below table.

Product
Shoes
Bags
Gloves
Caps

Quite simple, isn’t it? But there is another type of union operator that we can use.

The SQL UNION ALL in SAP

For the same set of tables, if we do a UNION ALL, the result would be:

Product
Shoes
Bags
Gloves
Shoes
Caps

Do you notice the difference here? Take a good hard look.

Difference between UNION and UNION ALL



You might have noticed this by now that UNION ALL did not remove the duplicates and ‘Shoes’ was repeated twice in the data set which in this case wasn’t a desirable result.

But why would one still use something like a UNION ALL? Excellent question.

In cases where there is absolutely no doubt that the merging data sets have distinct values, it’s better to use a UNION ALL so that there is no time wasted by the system in sorting and deleting duplicates from this final data set. For performance reasons, UNION ALL is a true gem of an option and should be used wherever possible.  

Thank you for reading this tutorial on UNIONs 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!

<<Previous Tutorial                                                                                            Next Tutorial>>

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

3 Comments

  1. Hi,Very good explanation.Thanks for great job.
    Can you explain how to create scd1 , scd2 …

    Thanks and Regards
    Manoj
    7829043431

    • Certain SAP tables would contain “DATE_TO” and “DATE_FROM” fields. For example, if an employee joined on 01.2010 , got transferred on 12.2014 and again on 01.2016, there would be 3 entries in table for the same employee’s location. DateFrom 01.2010 – Dateto 12.2014, then one for (12.2014-01.2016) and then (01.2016-12.9999). When the data is joined in HANA views with these tables, the historical data can also be then viewed at the granular level.

Leave a Reply

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