UNION DISTINCT vs ALL
As many of you already know, ABAP supports the UNION command now. UNION is an SQL command to combine the results of two separate queries into one dataset.
Here is a sample dataset from the imaginary database table Z1:
ID
|
TEXT
|
001
|
One
|
002
|
Two
|
And here is a sample dataset from another imaginary database table Z2:
CODE
|
NAME
|
002
|
Two
|
016
|
Sixteen
|
019
|
Nineteen
|
Before the UNION command existed, we had to run two distinct queries to combine this data into a single internal table.
SELECT
id AS key,
text AS value
FROM z1
INTO TABLE @DATA(lt_itab).
SELECT
code AS key,
name AS value
FROM z2
APPENDING CORRESPONDING FIELDS OF @lt_itab.
Now, UNION allows us to merge those queries.
SELECT
id AS key,
text AS value
FROM z1
UNION
code AS key,
name AS value
FROM z2
INTO TABLE @DATA(lt_itab).
Here is the catch: If we execute the UNION query above, we get the following resultset:
KEY
|
VALUE
|
001
|
One
|
002
|
Two
|
016
|
Sixteen
|
019
|
Nineteen
|
Did you notice that record “002” appears only once? In spite of its existence in both of Z1 & Z2, the result set included a singular “002” entry.
That’s the catch with UNION queries. They can be executed with two logical approaches to handle duplicate records.
UNION DISTINCT is the default mode, and it will eliminate duplicate records from the second query. That’s similar to the logic of SELECT DISTINCT or FOR ALL ENTRIES. That’s why “002” from the second table was missing in the resultset.
UNION ALL needs to be specified explicitly, and it tolerates duplicates from the second query. So, let’s modify our sample query accordingly.
SELECT
id AS key,
text AS value
FROM z1
UNION ALL
code AS key,
name AS value
FROM z2
INTO TABLE @DATA(lt_itab).
This time, our recordset will include duplicate records from Z2.
KEY
|
VALUE
|
001
|
One
|
002
|
Two
|
002
|
Two
|
016
|
Sixteen
|
019
|
Nineteen
|
Each approach has its time and place. If I am looking for a unique list of values, such as the combined list of tax numbers of clients & vendors, I would go for UNION DISTINCT. However; if I am querying transactions, such as BSIS / BSAS, I would go for UNION ALL because I probably wouldn’t want to miss any line items.
This is a great explanation!
Thank you!
Michelle
Thanks for explaining UNION! I didn't know that this command has been released...
Do you have any business related example what it could be used for?
Only one I can think of is internal and external status which are stored in tables TJ02 and TJ30:
Problem: How to format the code with UNION??
btw: More ABAP like would have been:
UNION select 1
WITH select 2
WITH select 3...
Nice: It also works with JOINs (Example code: read status + text):
In this example I do not see a big advantage over separate selects using APPENDING TABLE.
So UNION IMHO only helps if there are overlapping data in different tables.
Again: Any ideas where you could need this in SAP related context?
Some links to UNION
Horst Kellers Blog:
https://blogs.sap.com/2015/11/09/abap-news-for-release-750-select-union/
ABAP-Documentation:
https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/index.htm?file=abapunion_clause.htm
As I used it today here is a real life example:
I want to have the dimension based value read out of different condition tables.. When there is one maintained with the organisation, this is more important than this one without.
UNION made it easy:-)
Late answer, but maybe you are still diggin' *haha*
Wouldn't "APPENDING CORRESPONDING FIELDS" fail in the first example? The field names are different in Z1 and Z2. CORRESPONDING doesn't seem to be necessary there at all...
Not sure about UNION as we're not there yet release-wise but I agree with Enno that simply adding data together seems to have little advantage over two SELECTs.