Skip to content

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

UNION

Other versions: 7.31 | 7.40 | 7.54

Syntax Forms

UNION in Main QueriesUNION in subqueries

Addition

... ALL|DISTINCT

Effect

The ABAP SQL language element UNION joins the result sets of two queries. The rows of the result set of the query after UNION are inserted into the result set of the query before UNION. The syntax forms show where UNION can be used:

  • For creating the result set of the main query of a standalone statement, which is introduced using WITH or SELECT.

  • For creating the result set of the main query after OPEN CURSOR.

  • For creating the results set of a parenthesized subquery in a relational expression, of the definition of a common table expression after WITH, or of an INSERT statement.

    • In all syntax forms, it is possible to specify the same clauses and additions query_clauses for SELECT statements of queries before and after UNION for defining result sets. A query on the right side of UNION can be enclosed in parentheses ( ). A pair of parentheses can include multiple unions. The queries joined with UNION are evaluated from left to right. Specific statements can be prioritized using parentheses.

    In the case of a standalone SELECT or WITH statement and after OPEN CURSOR, the ORDER BY and the abap_options after the last query or after the position of the last closing bracket are listed and affect the union result set. In the case of standalone statements, the INTO clause is to be listed as the last clause and before the additions abap_options. The following special features apply here:

    • ORDER BY clause
    • Columns of the union results set specified after ORDER BY must occur with the same name in all relevant SELECT statements. The names must be specified directly and cannot be specified with the column selector ~ after a column name.
    • INTO clause
    • If the addition CORRESPONDING or an inline declaration @DATA(...) is used in the INTO clause, the column names of all results sets defined in the query_clauses from left to right must match.
    • The union result set is always multirow. If an assignment is made to a non-table-like target are (meaning a SELECT statement without the addition INTO|APPENDING ... TABLE), a loop closed using ENDSELECT or ENDWITH is always opened.
    • Restricting the Results Set


    Notes

    • Priorities using parentheses are particularly applicable when handling duplicate rows using DISTINCT.
    • When UNION is used, ABAP SQL bypasses table buffering.
    • The maximum number of different SELECT statements that can be joined using UNION depends on the database system. If this number is exceeded, an exception is raised when the program is executed.
    • When UNION is used, the syntax check is performed in a strict mode, which handles the statement more strictly than the regular syntax check. More specifically, the INTO clause and the additions abap_options can be specified at the end of the full SELECT statement.


    Example

    Creates the union of one row from the database table SCARR with multiple rows from the database table SPFLI. Those columns that do not exist in the other table are replaced by literals. Here, a CAST is required for the column CONNID.

    SELECT FROM scarr 
       FIELDS carrname, 
             CAST( '-' AS CHAR( 4 ) ) AS connid, 
              '-' AS cityfrom, 
              '-' AS cityto 
       WHERE carrid = 'LH' 
  UNION 
    SELECT FROM spfli 
           FIELDS '-' AS carrname, 
                  CAST( connid AS CHAR( 4 ) ) AS connid, 
                  cityfrom, 
                  cityto 
           WHERE carrid = 'LH' 
   ORDER BY carrname DESCENDING, connid, cityfrom, cityto 
   INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).

    Executable Examples

    UNION - Examples

    Addition

    ... ALL|DISTINCT

    Effect

    The additions ALL and DISTINCT specify how duplicate rows are handled. DISTINCT is the default here:

    • If the addition ALL is specified, all rows from the results set of the right SELECT statement are inserted into the existing results set.
    • If the addition DISTINCT is specified or if neither of the two additions is specified, the rows of the results set of the right SELECT statement are inserted into the existing results set. All duplicate rows are then deleted except for one (including all columns of the results set).


    Note

    The default behavior or the addition DISTINCT are always applied to the full existing results set. The addition DISTINCT also removes any duplicate rows produced by the addition ALL of preceding UNION additions.


    Example

    Fills the database table DEMO_EXPRESSIONS with one row and creates the union of the table with itself. Using the addition DISTINCT, the results set contains one row and using the addition ALL it contains two rows.

    DELETE FROM demo_expressions. 
INSERT demo_expressions FROM @( VALUE #( id = 'X' num1 = 111 ) ). 

SELECT id, num1 
       FROM demo_expressions 
  UNION DISTINCT 
      SELECT id, num1 
      FROM demo_expressions 
  INTO TABLE @DATA(result1). 

SELECT id, num1 
       FROM demo_expressions 
  UNION ALL 
      SELECT id, num1 
      FROM demo_expressions 
  INTO TABLE @DATA(result2). 

ASSERT lines( result1 ) = 1. 
ASSERT lines( result2 ) = 2.

    Continue

    UNION - query_clauses

    UNION Examples