Get Example source ABAP code based on a different SAP table
• JOIN ABAP_CDS_SELECT • INNER JOIN ABAP_CDS_SELECT • LEFT OUTER JOIN ABAP_CDS_SELECT • RIGHT OUTER JOIN ABAP_CDS_SELECT • EXACT ONE ABAP_CDS_SELECT_JOIN • MANY ABAP_CDS_SELECT_JOIN • ONE ABAP_CDS_SELECT_JOIN • TO EXACT ONE ABAP_CDS_SELECT_JOIN • TO ONE ABAP_CDS_SELECT_JOIN • TO MANY ABAP_CDS_SELECT_JOIN • CROSS JOIN ABAP_CDS_SELECT • ON ABAP_CDS_SELECT_JOIN
What does it do? Defines a join between two data sources of a CDS view entity. The code above is part of the syntax of a data source data_source and recursively contains the syntax of a second data source data_source. Two joined data sources represent a join expression. Every join expression for an inner or outer join must contain a join condition cds_cond after ON (for details, see Addition 1 below). A join expression for a cross join cannot contain any join condition. When joining two or more data sources, each element of the SELECT list must have the name of its data source as prefix. Example: source1.field Inner joins, outer joins, and cross joins are possible:
A join between two data sources using INNER JOIN or just JOIN selects all entries of the data sources whose fields match 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 match the ON condition have the same content as in the inner join. In entries that do not match the ON condition, the elements on the right or left side have the null value . When the CDS view entity is used in AB_SQL , null values are set to the type-dependent initial value.
When two data sources are joined using CROSS JOIN, the result is their cross product. All entries on the left side are combined with all entries on the right side. The number of rows in the result 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 affected 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.
Latest notes:
A WHERE condition for a SELECT statement with joins affects the result 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.
The function coalesce can be used to prevent null values in the result 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.
Cross joins should be used with 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 result 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-dependent 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 client-independent, 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 exactly where the ON conditions specify parentheses implicitly. NON_V5_HINTS
There is no limit on the number of join expressions in a SELECT statement of a CDS view entity in the DDL, but there is an ATC check that produces a message once a specific number of expressions is reached. ABAP_HINT_END
ABAP_EXAMPLE_VX5 The following CDS view entity DEMO_CDS_CLIENT_HANDLING defines different kinds of joins (left outer join, inner join, and right outer join) between the database table T000 and the database table DEMO_SALES_ORDER. DDLS DEMO_CDS_CLIENT_HANDLING ABAP_EXAMPLE_END
ABAP_EXAMPLE_VX5 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. ABAP_EXAMPLE_END
ABAP_EXAMPLE_VX5 The following view entity 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 class CL_DEMO_CDS_CROSS_JOIN_VE accesses the view entity. Without the WHERE condition, the result set would be very large. DDLS DEMO_CDS_CROSS_JOIN_VE ABAP_EXAMPLE_END
ABAP Addition
What does it do? Join condition. A join condition must be specified for an inner or outer join. A join condition must not be specified for a cross join. The syntax of the relational expressions of a join condition cds_cond is subject to the following restrictions:
All relational operators are allowed. That means all comparison operators are allowed as well as BETWEEN, LIKE, IS [NOT] NULL, and IS [NOT] INITIAL.
The Boolean operators NOT, AND, and OR are allowed.
lhs expects a field of one of the two data_sources of the join. If a comparison operator is used as operator, then lhs can also be a literal.
rhs expects a field of one of the two data_sources of the join, a literal, a parameter, a session variable, a built-in function, or a CDS scalar function. ABAP_EXCEPTION when using the operator LIKE, then rhs must be a character literal.
Path expressions are not allowed
Other CDS DDL expressions are not allowed.
ABAP Addition
What does it do? For an inner join or a left outer join, a cardinality can optionally be specified. A source and target cardinality can be specified, or only a target cardinality. The following cardinality specifications are possible:
EXACT ONE TO EXACT ONE
EXACT ONE TO MANY
EXACT ONE TO ONE
MANY TO EXACT ONE
MANY TO MANY
MANY TO ONE
ONE TO EXACT ONE
ONE TO MANY
ONE TO ONE
TO ONE
TO EXACT ONE
TO MANY The SQL Optimizer uses the cardinality specification for performance optimization. It attempts to suppress surplus joins. To avoid undefined behavior, the cardinality should always be defined to match the data in question.
ABAP_EXAMPLE_VX5 Incorrect use of TO ONE in CDS view entities. The data in the DDIC database tables SCARR and SPFLI do not have the cardinality TO ONE, but TO MANY . On a SAP HANA database, the result is dependent on the SELECT list. If the SELECT list contains columns from both the left and right side of the join expression, no optimization takes place. If the SELECT list does not contain any columns from the right side of the join expression, an optimization takes place. If the aggregate function COUNT(*) is used, an optimization takes place. When an optimization takes place, only that data is read that meets the specified cardinality. DDLS DEMO_CDS_WRONG_TO_ONE_1_VE DDLS DEMO_CDS_WRONG_TO_ONE_2_VE DDLS DEMO_CDS_WRONG_TO_ONE_3_VE The class CL_DEMO_CDS_WRONG_TO_ONE_VE accesses the CDS view entities and displays the results. ABAP_EXAMPLE_END
