ABAP Keyword Documentation → ABAP Dictionary → ABAP CDS in ABAP Dictionary → ABAP CDS - Views → ABAP CDS - DDL Statements → ABAP CDS - DEFINE VIEW → ABAP CDS - SELECT → ABAP CDS - SELECT, data_source

ABAP CDS - SELECT, JOIN

Other versions: ~~7.31~~ | 7.40 | 7.54

Syntax


... { [INNER] JOIN }|{ LEFT|RIGHT OUTER JOIN }
data_source ON cond_expr ...

Effect

Defines a join between two data sources of a CDS view in ABAP CDS. 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 every join expression, a join condition cond_expr must be specified after ON. When specified, special rules apply to this condition.

Both inner and outer joins are possible:

A join between two data sources using INNER JOIN or just JOIN selects all entries of the data sources whose fields meet the ON condition.

A join between two data sources using LEFT OUTER JOIN selects all entries on the left side. A join between two data sources using RIGHT OUTER JOIN selects all entries on the right side. Entries that meet the ON condition have the same content as in the inner join. In entries that do not meet the ON condition, the elements on the right or left side have the null value that is set to a type-compliant initial value when the CDS view is used in Open SQL.

Join expressions can be enclosed in parentheses, ( ). This is optional. In recursive join expressions, the parentheses determine the order in which they are evaluated. If no parentheses are used, the most adjacent ON condition is assigned to each join from left to right and this expression is parenthesized implicitly.

Note

A join condition must always be specified.

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.

@AbapCatalog.sqlViewName: 'DEMO_CDS_JOIN'
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.