If you place keyword UNION between two select statements, you can create the union of rows in the two result sets. Completely identical entries are removed from the result set.

A union result sets can itself be the left side of a further solution.

The properties of the union results set are defined as follows:

• The element names are taken either from an explicitly specified name list or, if no list is specified, the matching names from the SELECT lists of all SELECT statements are used.
• The definition of the key elements is taken from the SELECT list of the first SELECT statement.
• The data type of each element is a data type from the ABAP Dictionary whose value range covers the data types of the associated columns of all result sets involved.

The CDS view in this example reads flight customers from table SCUSTOM and travel agencies from table STRAVELAG. The two result sets are merged into one by using keyword UNION.

A field with name TYPE is filled with literal “Customer” or “Agency” to make the rows in the result set distinguishable. The field is a key field of the resulting view because it is labeled with “KEY” in the first select.

Difference between UNION and UNION ALL

If the additional ALL is not specified, all duplicate entries are removed from the result set. They are not removed if ALL is specified.

The CDS view in this example read customer ids from table SCUSTOM and from table SBOOK. In the first data definition keyword UNION ALL is used. The second CDS views read the same data but use UNION without additional ALL.