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