ABAP - Keyword Documentation →  ABAP - Programming Language →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Read Access → 
Mail Feedback

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]}
          ...] ) ...


Variants:

1. ... UNION ...

2. ... INTERSECT ...

3. ... EXCEPT ...

Addition:

... ALL|DISTINCT

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:

  1. For creating the result set of the main query of a standalone statement, which is introduced using WITH or SELECT.
  2. For creating the result set of the main query after OPEN CURSOR.
  3. For creating the result set of a parenthesized subquery in a relational expression, the definition of a common table expression after WITH, or an INSERT statement.

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



Variant 1  

... 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

UNION - Examples



Variant 2  

... 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

INTERSECT - Examples



Variant 3  

... 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

EXCEPT - Examples



Addition  

... 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.




Continue
UNION, INTERSECT, EXCEPT, query_clauses
UNION, Examples
INTERSECT, Examples
EXCEPT, Examples