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:
And here is a sample dataset from another imaginary database table Z2:
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:
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.
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.
