What is Join & Association in CDS View SAP ABAP-Part 2

We will check out details about Join & Association in CDS view with examples. Hope you have understood the basic of CDS view, if not, please check out the Part 1 in this part, Let’s check out Join in CDS view first

Join is nothing but a conditional linkage between two data sources it may be a table or another in CDS view. There are four types of join exist in the CDS view

• Inner Join
• Left outer Join
• Right outer Join
• Cross Join

Let’s check one by one with a simple set of data sources that is employee and manager table as a data source

Inner Join: Inner Join gives the matching value on both the table based on the join condition

while creating join in CDS view, you can make use of the template as Join

@AbapCatalog.sqlViewName: 'ZINNER_JOIN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Inner Join in CDS view'
define view zcds_inner_join
  as select from zmanagers
    inner join   zemployee on zmanagers.employee_id = zemployee.employee_id
{
  key zmanagers.employee_id,
      zemployee.employee_fname,
      zemployee.employee_lname,
      zemployee.employee_mail,
      zemployee.zemployee_address,
      zmanagers.unit
}

Left Outer Join: Left outer join gives the complete records of the Left data source and its corresponding targeted data source(if found), if not then null entries

@AbapCatalog.sqlViewName: 'ZLEFTJOIN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS view with Left outer Join'
define view zcds_left_outerjoin as select from zemployee
left outer join zmanagers
    on zemployee.employee_id = zmanagers.employee_id {
      key zemployee.employee_id,
      zemployee.employee_fname,
      zemployee.employee_lname,
      zemployee.employee_mail,
      zemployee.zemployee_address,
      zmanagers.unit
}

Right outer Join: It gives the complete records of the Right data source and its corresponding left data source(if found), if not then null entries

@AbapCatalog.sqlViewName: 'ZRIGHTJOIN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Right Outer Join'
define view ZCDS_right_outerjoin as select from zemployee
right outer join zmanagers
    on zemployee.employee_id = zmanagers.employee_id {
      key zemployee.employee_id,
      zemployee.employee_fname,
      zemployee.employee_lname,
      zemployee.employee_mail,
      zemployee.zemployee_address,
      zmanagers.unit
} 

Cross Join: As the name suggest this is the combination of all records.

@AbapCatalog.sqlViewName: 'ZCROSSJOIN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Cross Join in CDS view'
define view ZCDS_CROSS_JOIN as select from zemployee
cross join zmanagers
     {
      key zemployee.employee_id,
      zemployee.employee_fname,
      zemployee.employee_lname,
      zemployee.employee_mail,
      zemployee.zemployee_address,
      zmanagers.unit
}

Now let’s move to the Association: Association is nothing but the relationship between two entities, well the definition is the same as Join, underlining the association is nothing but a join but a bit on optimize way and having with some added feature.

Association makes a relationship from source to target with cardinality. Cardinality tells that the possible number of data records mapped with the single-source record. Cardinality always gives meaning to Target data sources.

Syntax : <Source> association [<cardinality>] to <Target> as <Association Name>
        on $Projection.<Field_Name> = <Association Name>.<Field Name>

Let’s check out one example.

Points to be noted that it is advisable to use the association name preceding with _. If you place the association name in the output then prerequisite to include the join condition fields in the output field list(in the above case it is employee_id). If you execute the CDS view then you get the result of zemployee table only. You get the zmanagers table data on demand. That’s why it is called Join on Demand(which is always an outer join). Let’s check out the same.

IF you check the result of the above CDS view -> Then you notice that during the execution of CDS view it only fetch the data from the zemployee table, not from the zmanagers table. It fetchs data from zmanagers table on demand.

If it is a join then during the execution of CDS view it hits the zmanagers even though records are not selected from that table.

Let’s see how we can consume the CDS view in ABAP select statement. The associations are consumed in the ABAP SQL statement by using path expression. And this feature is supported ABAP 7.5 onwards

REPORT ztest_cds_execution.

SELECT FROM zcds_association
  FIELDS employee_id,
         employee_fname,
         employee_lname,
         employee_age,
        \_manager_association-unit  "Path expression
  INTO TABLE @DATA(lt_Data).
IF sy-subrc EQ 0.
  cl_demo_output=>display_data(
    EXPORTING
      value = lt_data
      name  = 'CDS view Output'
  ).
ENDIF.

Cardinality: Cardinality tells how the Target data are mapping with Source data. Below are different cardinality

Check out our OData series OData – ABAP Skill

Check out ABAP Blogs ABAP – ABAP Skill

Previous Part : What is CDS view in SAP ABAP? – Part 1 – ABAP Skill

SAP Document: Link