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!