Skip to Content
Author's profile photo Kerem Koseoglu
Kerem Koseoglu
October 30, 2017 1 minute read

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.

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michelle Crapo
      Michelle Crapo

      This is a great explanation!

      Thank you!

      Michelle

      Author's profile photo Enno Wulff
      Enno Wulff

      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:

       SELECT tj02~istat AS stat from tj02 
     where nodis = @space
  UNION
    SELECT tj30~estat AS stat FROM tj30 
     WHERE tj30~stsma = '00000003'
  INTO TABLE @data(status).

       

      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):

          SELECT tj02~istat  AS stat,
           tj02t~txt30 AS text
      FROM tj02 INNER JOIN tj02t ON tj02~istat = tj02t~istat
     WHERE tj02~nodis  = @space
       AND tj02t~spras = @sy-langu
  UNION
    SELECT tj30~estat  AS stat,
           tj30t~txt30 AS text
      FROM tj30 INNER JOIN tj30t ON tj30~estat = tj30t~estat
                                AND tj30~stsma = tj30t~stsma
     WHERE tj30~stsma  = '00000003'
       AND tj30t~spras = @sy-langu
  INTO TABLE @data(status).

      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

       

      Author's profile photo Florian Henninger
      Florian Henninger

      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*

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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.