Skip to content

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

UNION - query_clauses

Other versions: 7.31 | 7.40 | 7.54

Syntax


... { FROM source 

      FIELDS select_clause } 

  | { select_clause 

      FROM source } 

      [WHERE sql_cond] 

      [
GROUP BY group] [HAVING group_cond] 

      [
ORDER BY [UP TO n ROWS [OFFSET o]]] 

      [db_hints]  ...

Effect

Possible clauses and additions of queries, whose results sets are joined using UNION. The results set of SELECT statements joined using UNION is regarded as having multiple rows.

The following special conditions apply to clauses and additions of the SELECT statements joined using UNION:

  • The SELECT liste must consist of a list of specified columns col_spec as SQL expressions. * and data_source~* cannot be specified.
  • All SELECT lists of SELECT statements grouped using UNION must have the same number of elements.
  • The columns assigned to each other must have the same type attributes with respect to built-in data type, length, and number of digits after the decimal point, with the following exceptions:
  • Numeric types INT1, INT2, INT4 and INT8 can create a column. The resulting column has the data type with the greatest value range.
  • Numeric types DEC can have different lengths, but must have the same number of digits after the decimal point. The resulting column has the data type with the greatest length. The corresponding special types CURR and QUAN are handled here like DEC.
  • The numeric types DF16_DEC and DF34_DEC are handled like the numbers of type DEC (as they are saved) and the rule above applies with respect to lengths and decimal places.
  • Character-like types CHAR can have different lengths. The resulting column has the data type with the greatest length. The corresponding special types CLNT, LANG, CUKY, and UNIT are handled here like CHAR.
All other types must be exactly the same. This applies specifically to NUMC and RAW, where the lengths must match. The different categories of strings cannot be combined either.
  • The addition FOR ALL ENTRIES is not allowed in the WHERE conditions of the SELECT statements in question.
  • An ORDER BY clause cannot be specified for individual results sets. Instead ORDER BY can be applied to the joined results set of main queries.

The number of SELECT statements that can be joined using UNION is not limited by a fixed value.


Notes

  • Each of the associated SELECT statements has its own client handling. An addition USING or or the obsolete addition CLIENT SPECIFIED in the FROM clause applies only to the SELECT statement for which it is specified.
  • If required, the column names of the individual results sets can be modified using alias names after AS.
  • If the column names of the results sets are not identical, the column names are used from the results set of the SELECT statement on the left of UNION. In this type of case, the names are usually not visible (except for subqueries in the WITH statement).
  • The numeric types DF16_DEC and DF34_DEC can only be used together in a column if their decimal places match.
  • SQL expressions, host expressions, and host variables can be used to synchronize the columns of the results set of the SELECT statements in question.


Example

Creates the union set of three aggregate expressions applied to the same database table in a subquery of a WITH statement. The union set is selected in a common table expression, which makes it possible to use a single WHERE condition for the entire results set in the main query of the WITH statement. If the union set were created in a main query, every single SINGLE statement would have to contain the same WHERE condition or HAVING condition.

WITH +aggregates AS ( 
  SELECT FROM sflight 
         FIELDS carrid, 
                connid, 
                'MAX' AS function, 
                MAX( CAST( seatsocc AS DEC( 31,2 ) ) ) AS agg 
         GROUP BY carrid, connid 
    UNION 
      SELECT FROM sflight 
             FIELDS carrid, 
                    connid, 
                    'MIN' AS function, 
                    MIN( CAST( seatsocc AS DEC( 31,2 ) ) ) AS agg 
             GROUP BY carrid, connid 
        UNION 
          SELECT FROM sflight 
                 FIELDS carrid, 
                 connid, 'AVG' AS function, 
                 AVG( seatsocc AS DEC( 31,2 ) ) AS agg 
                 GROUP BY carrid, connid ) 
  SELECT * 
         FROM +aggregates 
         WHERE carrid = 'LH' AND connid = '0400' 
         INTO TABLE @DATA(result).

Executable Examples