ABAP - Keyword Documentation →  ABAP - Dictionary →  ABAP CDS in ABAP Dictionary →  ABAP CDS - Data Definitions →  ABAP CDS - DDL for Data Definitions →  ABAP CDS - DEFINE VIEW →  ABAP CDS - SELECT →  ABAP CDS - SELECT, data_source → 

ABAP CDS - SELECT, JOIN

Syntax

... { [INNER] JOIN }|{ LEFT|RIGHT OUTER [TO ONE|MANY] JOIN }|{ CROSS JOIN }
      data_source [ON cond_expr] ...


Addition:

... TO ONE|MANY

Effect

Defines a join between two data sources of a CDS view. The code above is part of the syntax of a data source data_source and contains the recursive syntax of a data source data_source. Two data sources joined using JOIN create a join expression.

In a join expression using INNER and OUTER, a join condition cond_expr must be specified after ON. Special rules apply when this is specified. A join condition cannot be specified for a join expression using CROSS.

Inner joins, outer joins, and cross joins are all possible:

Nested join expressions are evaluated in the following order:

Notes

Example

The following CDS view works in exactly the same way as the classic database view DEMO_SCARR_SPFLI. The program DEMO_CDS_JOIN uses SELECT to access the view. Unlike when the classic database view DEMO_SCARR_SPFLI is accessed, no client column is returned when the CDS entity DEMO_CDS_SCARR_SPFLI is accessed. The CDS database view DEMO_CDS_JOIN returns the client column too.

@AbapCatalog.sqlViewName: 'DEMO_CDS_JOIN'
@AccessControl.authorizationCheck: #NOT_ALLOWED
define view demo_cds_scarr_spfli(
    id,
    carrier,
    flight,
    departure,
    destination
  )
  as select from
           spfli
      join scarr on
        scarr.carrid = spfli.carrid
    {
      key spfli.carrid,
      key scarr.carrname,
      key spfli.connid,
          spfli.cityfrom,
          spfli.cityto
    }

Example

The following non-parenthesized chaining of join expressions

... from tab1
      join
        tab2
          join
            tab3 on tab2.id = tab3.id
                 on tab1.id = tab2.id ...

is parenthesized implicitly as follows:

... from tab1
      join
        ( tab2
            join
              tab3 on tab2.id = tab3.id ) on tab1.id = tab2.id ...

No elements from tab1 can be specified in the inner ON condition.

Example

The following view contains a cross join of table T000 of all clients of an AS ABAP with the entries for the message class SABAPDEMOS in the table T100. The program DEMO_CDS_CROSS_JOIN accesses the view. The results set would be very large without this WHERE condition.

@AbapCatalog.sqlViewName: 'DEMO_CDS_CRSJN'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view demo_cds_cross_join
  as select from
                 t000
      cross join t100
    {
      t000.mandt,
      t000.mtext,
      t100.sprsl,
      t100.arbgb,
      t100.msgnr,
      t100.text
    }
    where
      t100.arbgb = 'SABAPDEMOS'    


Addition

... TO ONE|MANY

Effect

Specifies the cardinality of a left outer join. This addition is positioned after LEFT OUTER, but is not possible after RIGHT OUTER. Only certain specific database systems apply this addition.

If the addition TO ONE or TO MANY is specified, any databases that support this addition assume that the results set defined by the left outer join matches this cardinality and SQL Optimizer attempts to suppress any surplus joins. If the results set does not match the cardinality, the result is undefined and may be dependent on the entries in the SELECT list.

Notes

Example

Incorrect use of TO ONE in CDS views. The data in the database tables SCARR and SPFLI do not have the cardinality TO ONE and have the cardinality TO MANY instead. On a SAP HANA database, for example, the result is dependent on the SELECT list. If the left and right side are specified here, no optimization takes place. If no columns are specified on the right side (and the aggregate function COUNT(*) is used), an optimization takes place. Here, only that data is read that meets the prerequisite cardinality.

@AbapCatalog.sqlViewName: 'DEMOCDSWTO1'
define view demo_cds_wrong_to_one_1
  as select from
                             scarr as c
      left outer to one join spfli as p on
        c.carrid = p.carrid
    {
      c.carrid   as carrid,
      c.carrname as carrname,
      p.connid   as connid
    }
@AbapCatalog.sqlViewName: 'DEMOCDSWTO2'
define view demo_cds_wrong_to_one_2
  as select from
                             scarr as c
      left outer to one join spfli as p on
        c.carrid = p.carrid
    {
      c.carrid   as carrid,
      c.carrname as carrname
    }
@AbapCatalog.sqlViewName: 'DEMOCDSWTO3'
define view demo_cds_wrong_to_one_3
  as select from
                             scarr as c
      left outer to one join spfli as p on
        c.carrid = p.carrid
    {
      count(*) as cnt
    }

The program DEMO_CDS_WRONG_TO_ONE accesses the CDS views and represents the results.