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:
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 the type-dependent initial value when the CDS view is used in Open SQL.
When two data sources are joined using CROSS JOIN, their cross product
is produced. All entries on the left side are combined with all entries on the right side. The number
of rows in the results set is the number of rows on the left side multiplied by the number of rows on the right side.
Nested join expressions are evaluated in the following order:
In the case of inner and outer joins, by the arrangement of the ON
conditions. From left to right, the most adjacent ON conditions are assigned
to each JOIN and this expression is parenthesized implicitly. These implicit parentheses can be made explicit using actual parentheses, (
). This is optional.
By default, cross joins are evaluated from left to right. The priority of the evaluation can be influenced by parentheses ( ).
If multiple cross joins are combined, the order of the evaluation is irrelevant. The result is always the same and the number of rows is the product of the number of rows of all involved data sources.
If cross joins are combined with inner and outer joins, the result can depend on the order of evaluation or the parentheses.
Notes
A WHERE condition for a SELECT statement with joins is applied to the results set created using the joins.
An inner join or a cross join between two individual data sources is commutative. If the left and right side are switched, the result remains the same.
Buffering is not recommended for a CDS view that contains an outer join. The results set can contain
null values, which means
that Open SQL reads can behave differently to direct database reads when the buffer is read, since null values in the buffer are transformed to initial values.
The function coalesce can be used to prevent null values in the results set.
A cross join behaves like an inner or outer join whose ON condition is
always true. A cross join with a WHERE condition has the same result as an
inner join with an identical ON condition. Unlike the inner join, in a cross
join all data is read first before the condition is evaluated. In an inner join only data that meets the ON condition is read.
A cross join should only be used with extreme caution. Since it is not possible to specify an
ON condition, all data of all involved data sources is read. In the case of very large datasets,
the results set (whose number of rows is always the product of the number of all rows of both data sources) can quickly become very large.
On the database, a cross join of two client-specific data sources is defined internally as an inner
join, whose ON condition checks whether the client columns of the left and right side are equal. If one side is not client-specific, the cross join is defined as specified.
In nested join expressions, parentheses are recommended for making the code easier to read. In the
case of inner and outer joins, the parentheses can be specified wherever the ON conditions specify parentheses implicitly.
There is no limit on the number of join expressions in a SELECT statement of a CDS view in the DDL, but there is an
ATC check that produces a message once a specific number of expressions is reached.
Example
The following CDS view works in exactly the same way as the classic
database viewDEMO_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.
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
More information can be found in the documentation of the current database system. The
SAP HANA database, for
example, supports the additions TO ONE and TO MANY and their description is part of the
HANA-specific SQL documentation.
To avoid undefined and platform-specific behavior, TO ONE or TO MANY can be specified only if the data being read meets the relevant prerequisites.
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.