7.31 | 7.40 | 7.54
UNION in Main QueriesUNION in subqueries
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:
WITH, or of an
In all syntax forms, it is possible to specify the same
clauses and additions
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
WITH statement and after
OPEN CURSOR, the
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:
- The addition
ORDER BY PRIMARY KEYis not allowed.
- Columns of the union results set specified after
ORDER BY must occur with the same name in all relevant
SELECTstatements. The names must be specified directly and cannot be specified with the column selector
~after a column name.
- If the addition
CORRESPONDINGor an inline declaration
@DATA(...)is used in the
INTOclause, the column names of all results sets defined in the
query_clausesfrom left to right must match.
- The union result set is always multirow. If an assignment is made to a non-table-like target are
SELECTstatement without the addition
INTO|APPENDING ... TABLE), a loop closed using ENDSELECT or
ENDWITHis always opened.
Restricting the Results Set
- The additions
OFFSETare not currently allowed with
Priorities using parentheses are particularly applicable when handling duplicate rows using
UNIONis 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.
UNIONis 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_optionscan be specified at the end of the full
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 ).
DISTINCT specify how duplicate rows are handled.
DISTINCT is the default here:
If the addition
ALLis specified, all rows from the results set of the right
SELECTstatement are inserted into the existing results set.
If the addition
DISTINCTis specified or if neither of the two additions is specified, the rows of the results set of the right
SELECTstatement are inserted into the existing results set. All duplicate rows are then deleted except for one (including all columns of the results set).
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
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.