Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  UNION →  UNION Examples 

SELECT, Union of Multiple Tables

This example demonstrates how a union is created across multiple tables.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

    SELECT a AS c1, b AS c2, c AS c3, d AS c4
           FROM demo_join1
    UNION DISTINCT
    SELECT d AS c1, e AS c2, f AS c3, g AS c4
          FROM demo_join2
    UNION DISTINCT
    SELECT i AS c1, j AS c2, k AS c3, l AS c4
           FROM demo_join3
           INTO TABLE @DATA(result_distinct).
    out->write( result_distinct ).

    SELECT a AS c1, b AS c2, c AS c3, d AS c4
           FROM demo_join1
    UNION ALL
    SELECT d AS c1, e AS c2, f AS c3, g AS c4
          FROM demo_join2
    UNION ALL
    SELECT i AS c1, j AS c2, k AS c3, l AS c4
           FROM demo_join3
           INTO TABLE @DATA(result_all).
    out->write( result_all ).

    SELECT a AS c1, b AS c2, c AS c3, d AS c4
           FROM demo_join1
    UNION ALL
    SELECT d AS c1, e AS c2, f AS c3, g AS c4
          FROM demo_join2
    UNION DISTINCT
    SELECT i AS c1, j AS c2, k AS c3, l AS c4
           FROM demo_join3
           INTO TABLE @DATA(result_all_distinct1).
    out->write( result_all_distinct1 ).

    SELECT a AS c1, b AS c2, c AS c3, d AS c4
           FROM demo_join1
    UNION ALL
    ( SELECT d AS c1, e AS c2, f AS c3, g AS c4
             FROM demo_join2
      UNION DISTINCT
      SELECT i AS c1, j AS c2, k AS c3, l AS c4
            FROM demo_join3 )
            INTO TABLE @DATA(result_all_distinct2).
    out->write( result_all_distinct2 ).

    SELECT a AS c1, b AS c2, c AS c3, d AS c4
           FROM demo_join1
    UNION DISTINCT
    SELECT d AS c1, e AS c2, f AS c3, g AS c4
          FROM demo_join2
    UNION ALL
    SELECT i AS c1, j AS c2, k AS c3, l AS c4
           FROM demo_join3
           INTO TABLE @DATA(result_distinct_all1).
    out->write( result_distinct_all1 ).

    SELECT a AS c1, b AS c2, c AS c3, d AS c4
           FROM demo_join1
    UNION DISTINCT
    ( SELECT d AS c1, e AS c2, f AS c3, g AS c4
            FROM demo_join2
    UNION ALL
    SELECT i AS c1, j AS c2, k AS c3, l AS c4
          FROM demo_join3 )
          INTO TABLE @DATA(result_distinct_all2).
    out->write( result_distinct_all2 ).

    out->display( ).

Description

Four columns of the same type from result sets from three database tables DEMO_JOIN1, DEMO_JOIN2 and DEMO_JOIN3 are combined to make multiple SELECT statements using UNION. The database tables are filled in the static constructor.

  • The first statement shows the default behavior with addition DISTINCT. No rows are inserted from database table DEMO_JOIN2 and one row is not inserted from database table DEMO_JOIN3 because these rows already exist.
  • The second statement shows the behavior with addition ALL. All the rows from the three result sets are combined into one result set without removing any rows.
  • The third statement contains addition ALL in the first UNION and contains DISTINCT in the second union. Addition DISTINCT deletes all duplicate rows, including the rows created using addition ALL. Therefore the result is the same as in the first statement.
  • The fourth statement is the same as the third - except that parentheses have been inserted here. First, the parentheses are evaluated. Addition DISTINCT now takes effect in the parentheses and removes the first row from DEMO_JOIN3. Afterwards the result set of the parentheses is completely inserted into the result set of DEMO_JOIN1.
  • The fifth statement contains addition DISTINCT in the first UNION and contains ALL in the second union. In the first union, no rows are taken from DEMO_JOIN2 because all the rows already exist. Next, all the rows are inserted from DEMO_JOIN3.
  • The sixth statement is the same as the fifth - except that parentheses have been inserted here. Once the parentheses have been evaluated, the corresponding result set contains all rows from DEMO_JOIN2 and DEMO_JOIN3. In the union with rows from DEMO_JOIN1, all duplicate rows are removed using DISTINCT; the result is the same as with the first statement.