SAP ABAP CDS Joins and Unions

SAP ABAP CDS Joins and Unions For “Read Access” to multiple database tables / views using CDS Joins and Unions, the following options are available:

Combination of several tables (JOIN)

•  INNER JOIN vs. OUTER JOIN

 Combination of results from several sub-queries (UNION)

•  UNION ALL vs. UNION

See More: CDS Conversion Functions (CAST, Unit Conversion, Currency Conversion)

INNER JOIN

One row of the left table and one row of the right table are always joined to a common result row – provided that
the JOIN condition is fulfilled.

• JOIN Condition: L.X = R.Y

OUTER JOIN

The OUTER JOIN has three sub types:

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

LEFT OUTER JOIN

• One row of a table and one row of another table are always connected to a common result row – provided that
  the JOIN condition is fulfilled.
• In addition, rows of the left table without matching row in the right table are copied to the query result. The
  missing values (from the right table) are filled with NULL values.

RIGHT OUTER JOIN

• One row of a table and one row of another table are always connected to a common result row – provided that
  the JOIN condition is fulfilled.
• In addition, rows of the right table without matching row in the left table are copied to the query result. The
  missing values (from the left table) are filled with NULL values.

See More: SAP ABAP CDS SQL Expressions CASE expressions ( Simple CASE, Searched CASE ; Nested CASE ), COALESCE,

FULL OUTER JOIN

• One row of a table and one row of another table are always connected to a common result row – provided that
  the JOIN condition is fulfilled.
• In addition, rows of both tables without matching records are copied to the query result. The missing
  values (from the other table) are filled with NULL values

Supported Joins and Unions in CDS views

Supported Join Types:
1. Inner Join
2. Left Outer Join
3. Right Outer Join

4. Union/Union All

Inner Join: Output list of customer and booking details for booking id = 2406

@AbapCatalog.sqlViewName:'S4HCDS_JOIN_V01'

define view s4hcds_join_v1 as

select from sbook as b

inner join spfli as p on p.carrid = b.carrid

and p.connid = b.connid

inner join scustom as c on c.id = b.customid

{

b.customid, c.name,

b.fldate, p.cityfrom, p.cityto

}

where b.bookid = '00002406'

Left Outer Join: Output list of all customer details with booking id = 2406.

@AbapCatalog.sqlViewName: S4HCDS_LJOIN_01

define view s4hcds_ljoin_demo1 as

select from scustom as c

left outer join sbook as b on c.id = b.customid

{

c.name as Customer_Name,

c.city as Customer_city

}

where b.bookid = '00002406'

UNION [ALL] , UNION

You can combine the result tables of multiple queries using UNION [ALL].

• The individual results tables must have the same number of columns.
• The corresponding result columns must have compatible data types.
• If no name list specified, the column names of the result sets must match

• UNION – Eliminate duplicates but it is slower

• UNION ALL – Includes duplicates

Example:
=> Select carrier ids of flights flying from city = Frankfurt and have counter at Frankfurt airport

@AbapCatalog.sqlViewName: 'S4HCDS_UNION_01'

define view s4hcds_union_demo1 as

select distinct from spfli

{

carrid as CarrierID

} where cityfrom ='FRANKFURT'

union all

select distinct from scounter

{

carrid as CarrierID

} where airport = 'FRA'