AS ABAP Release 758, ©Copyright 2024 SAP SE. All rights reserved.
ABAP - Keyword Documentation → ABAP - Programming Language → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Read Access →
UNION, INTERSECT, EXCEPT
Syntax Forms
UNION, INTERSECT, and EXCEPT in Main Queries
1. [WITH ...]
SELECT query_clauses
{UNION [ALL|DISTINCT]}
| {INTERSECT [DISTINCT]}
| {EXCEPT [DISTINCT]}
[(] SELECT query_clauses [)]
[{UNION [ALL|DISTINCT]}
| {INTERSECT [DISTINCT]}
| {EXCEPT [DISTINCT]}
...]
[ORDER BY sort_key]
INTO|APPENDING target
[abap_options].
...
[
ENDSELECT|ENDWITH.]
2. OPEN CURSOR ... FOR
[WITH ...]
SELECT query_clauses
{UNION [ALL|DISTINCT]}
| {INTERSECT [DISTINCT]}
| {EXCEPT [DISTINCT]}
[(] SELECT query_clauses [)]
[{UNION [ALL|DISTINCT]}
| {INTERSECT [DISTINCT]}
| {EXCEPT [DISTINCT]}
...]
[ORDER BY sort_key]
[abap_options].
UNION, INTERSECT, and EXCEPT in Subqueries
3. ... ( SELECT query_clauses
{UNION[ALL|DISTINCT]}
| {INTERSECT [DISTINCT]}
| {EXCEPT [DISTINCT]}
[(] SELECT
query_clauses [)]
[{UNION [ALL|DISTINCT]}
| {INTERSECT [DISTINCT]}
| {EXCEPT [DISTINCT]}
...] ) ...
1. ... UNION ...
2. ... INTERSECT ...
3. ... EXCEPT ...
Effect
The set operators UNION, INTERSECT, and EXCEPT merge the result sets of multiple queries into a single result set. The syntax forms show where UNION, INTERSECT, and EXCEPT can be used:
In all syntax forms, it is possible to specify the same clauses and additions, specifically query_clauses, for SELECT statements of queries in front of and after UNION, INTERSECT, or EXCEPT for defining result sets. A query on the right side of UNION, INTERSECT, or EXCEPT can be enclosed in parentheses ( ). A pair of parentheses can include multiple unions, intersections, or differences. The queries joined with UNION, INTERSECT, or EXCEPT 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 merged result set. In the case of standalone statements, the INTO clause is to be listed as the last clause and in front of the additions abap_options. The following special features apply to standalone statements with set operators:
Hints
... UNION ...
Effect
The ABAP SQL set operator UNION merges 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 in front of UNION.
Hint
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
must be specified at the end of the entire SELECT statement.
Example
Creates the union of one row from the DDIC database table SCARR with multiple rows from the DDIC 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 @FINAL(result).
cl_demo_output=>display( result ).
Executable Examples
... INTERSECT ...
Effect
The ABAP SQL set operator INTERSECT returns all distinct rows of the result set of the query in front of INTERSECT that are also present in the result set of the query after INTERSECT.
Hint
When INTERSECT 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
must be specified at the end of the entire SELECT statement.
Example
Creates the intersect of multiple rows from a joined table with multiple rows from the DDIC database table SCARR.
SELECT spfli~carrid, scarr~carrname
FROM spfli
INNER JOIN scarr ON scarr~carrid = spfli~carrid
INTERSECT
SELECT carrid, carrname
FROM scarr
ORDER BY carrid ASCENDING, carrname
INTO TABLE @FINAL(result).
Executable Examples
... EXCEPT ...
Effect
The ABAP SQL set operator EXCEPT returns all distinct rows of the result set of the query in front of EXCEPT that are not present in the result set of the query after EXCEPT.
Hints
Example
Selects all airline codes and airline names from the DDIC database table SCARR that do not exist in the DDIC database table SPFLI.
SELECT carrid, carrname
FROM scarr
EXCEPT
SELECT spfli~carrid, scarr~carrname
FROM spfli
INNER JOIN scarr ON scarr~carrid = spfli~carrid
ORDER BY carrid ASCENDING, carrname
INTO TABLE @FINAL(result).
Executable Examples
... ALL|DISTINCT
Effect
The additions ALL and DISTINCT control how duplicate rows are handled. DISTINCT is the default here:
Hints
Example
The DDIC database table DEMO_EXPRESSIONS is filled with one row and the union of the table with itself is created. Using the addition DISTINCT, the result 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 @FINAL(result1).
SELECT id, num1
FROM demo_expressions
UNION ALL
SELECT id, num1
FROM demo_expressions
INTO TABLE @FINAL(result2).
ASSERT lines( result1 ) = 1.
ASSERT lines( result2 ) = 2.