SAP SELECT JOIN ABAP Statements Get Example source ABAP code based on a different SAP tableABAP Statement
• JOIN SELECT FROM
• INNER JOIN SELECT FROM
• LEFT OUTER JOIN SELECT FROM
• RIGHT OUTER JOIN SELECT FROM
• CROSS JOIN SELECT FROM
• AS SELECT JOIN
• ON SELECT JOIN
• ONE TO ONE SELECT JOIN
• ONE TO EXACT ONE SELECT JOIN
• ONE TO MANY SELECT JOIN
• EXACT ONE TO EXACT ONE SELECT JOIN
• EXACT ONE TO ONE SELECT JOIN
• EXACT ONE TO MANY SELECT JOIN
• MANY TO EXACT ONE SELECT JOIN
• MANY TO ONE SELECT JOIN
• MANY TO MANY SELECT JOIN
SELECT, FROM JOIN Short Reference ABAP_SYNTAX ... $[($] ${data_source $[AS tabalias$]$}$|join data_source $[AS tabalias$]$}$|join $[ON sql_cond$] $[)$] ... . ABAP Addition ... ON sql_cond ... cardinality What does it do? data sources in a result set of a query in a join expression. A join expression joins a left side with a right side, using $[INNER$] JOIN (inner join) LEFT$|RIGHT $[OUTER$] JOIN (outer join ) CROSS JOIN (cross join) sql_cond after ON (see below). A join expression for a cross join cannot contain any join conditions. data_source is a single data source. DDIC database tables must be transparent. As with the individual specification after FROM using AS, an alias name can be specified for the data source. A data source can exist more than once within a join expression and must then be given different names. A join expression can be specified for join on both sides. A join expression can therefore be nested recursively. The number of data sources linked to each other is limited. The maximum number is set to allow the SELECT statement to be executed on all supported database systems and is currently 50. More than 49 joins, if known statically, produce a syntax error. If they are not known statically, they produce a runtime error. For inner and outer joins, the priority is determined by the position of the ON conditions. From left to right, each ON condition is assigned to the directly preceding JOIN and creates a join expression. Join expressions of this type can optionally be enclosed in parentheses, ( ). Explicitly specified parentheses must match the parentheses specified implicitly by the ON conditions. 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. Result set for inner joins sql_cond is jointly true. If there are no rows in the result set of the left and right sides that meet sql_cond, no row is created in the resulting result set. Result set for outer joins LEFT OUTER JOIN or on the right side as RIGHT OUTER JOIN, at least one row is created in the result set, even if no rows on the other side meet the condition sql_cond. The columns on the other side that do not meet the condition sql_cond are filled with null values. Result set for cross join Latest notes: Certain restrictions apply to other clauses in the current SELECT statement when join expressions are used. For example, a join expression cannot be used together with the addition ORDER BY PRIMARY KEY. These restrictions are documented in the corresponding clauses. 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. 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 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. A cross join of two client-dependent data sources is converted internally to 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-dependent, the cross join is executed completely. If the same column name appears in multiple data sources of a single join expression, these sources must be identified in all other additions of the SELECT statement using the column selector ~. Join expressions bypass table buffering , which is why they should not be applied to buffered tables. Instead, in these cases, the addition FOR ALL ENTRIES should be used, which can access the table buffer. If columns from the right side are specified as LEFT OUTER JOIN or columns from the left side are specified as RIGHT OUTER JOIN, after the addition ORDER BY, the sort order can depend on the database system in the case of null values. The function coalesce can be used to replace null values created due to an external join with other values or the result of expressions. The syntax check is performed in ABAP_STRICT_740_SP05 strict mode for ABAP release ABAP_740_SP05 / in the following cases: Not all comparisons of an ON condition contain a column from a data source specified on the right side as an operand. Multiple consecutive joins are explicitly parenthesized so that a join expression is on the right side of a join expression and not a data source. RIGHT OUTER JOIN is used. In LEFT OUTER JOIN, fields from the right side of the WHERE condition of the current SELECT statement are specified. In RIGHT OUTER JOIN, fields from the left side are specified. The syntax check is performed in ABAP_STRICT_740_SP08 strict mode for ABAP release ABAP_740_SP08 / in the following cases: Use of the additions LIKE, IN, and NOT plus the operators OR or NOT in an ON condition. Outer join without a comparison between columns on the left and right sides. The syntax check is performed in ABAP_STRICT_765 strict mode for ABAP_RELEASE ABAP_765 / in the following cases: Use of CROSS JOIN. ABAP_EXAMPLE_VX5 CARRNAME, CONNID, and FLDATE of the DDIC database tables SCARR, SPFLI, and SFLIGHT using two inner joins. This creates a list of flights from CITYFROM to CITYTO. Alternative names are used for each table. ABAP_EXAMPLE_VX5 SCARR and SPFLI using a left outer join. For all flights not departing from CITYFROM, the value of the column CONNID is the null value. Due to the WHERE condition, all airlines that do not fly from CITYFROM are output. ABAP_EXAMPLE_VX5 T000 of all clients of an AS ABAP with the entries for the message class SABAPDEMOS in the table T100. Without the WHERE condition, the result set would be very large. ABAP Addition What does it do? relational expressions of a join condition sql_cond is subject to the following restrictions: After ON, at least one comparison must be specified. The expression $[NOT$] IN range_tab cannot be used. Subqueries cannot be used. Path expressions cannot be used. Within a subquery, only columns of the data sources of this subquery can be specified. A dynamic condition (cond_syntax) can be specified only if the FROM clause is specified statically and is not specified dynamically as (source_syntax). sql_cond is true. Implicit AB_SQL client handling applies. In joins between client-dependent tables, a comparison for equality between the client columns is added to the ON condition implicitly. The client column of a client-dependent data source cannot be used as an operand in the ON condition. Latest notes: Comparisons between database columns or SQL expressions and database columns are performed on the database, which means it is essential that appropriate join conditions are formulated only between operands of the same type and the same length. This avoids the need for platform-dependent conversions. If the pattern consists of exactly one % character in a comparison using LIKE , the same optimization takes place as for WHERE. The condition col LIKE '%' is always true, even if the column col contains null values. LIKE '%' has not the same results as platform specific SQL that follows the SQL standard. If one of the following two conditions apply: SQL expressions are used on the left side of the ON condition of any join. The expression IS $[NOT$] NULL is used in the ON condition of an outer join. The client column of a client-dependent data source can still be used in the ON condition if implicit client handling is disabled using the obsolete addition CLIENT SPECIFIED . If a host expression occurs on the right side of the ON condition, the syntax check is performed in a ABAP_STRICT_760 strict mode from ABAP_RELEASE ABAP_760 / , which handles the statement more strictly than the regular syntax check. The syntax check is performed in a ABAP_STRICT_761 strict mode from ABAP_RELEASE ABAP_761 / , which handles the statement more strictly than the regular syntax check. ABAP_EXAMPLES_ABEXA Inner, outer, and cross joins Multiple joins ABAP Addition What does it do? cardinality cardinality of an inner, left outer, or right outer join. It has an effect only on certain specific database systems. ONE TO ONE ONE TO MANY ONE TO EXACT ONE EXACT ONE TO ONE EXACT ONE TO MANY EXACT ONE TO EXACT ONE MANY TO ONE MANY TO MANY MANY TO EXACT ONE SQL Optimizer for performance optimizations by suppressing surplus joins on any databases that support this addition. It is important that the cardinality specification matches the data in question. Otherwise, the result is undefined and can depend on the entries in the SELECT list. many-to-many. Latest notes: For more information, see the documentation of the current database system. The SAP HANA database, for example, supports the cardinality specification and its description is part of the documentation of HANA-specific SQL. To avoid undefined and platform-dependent behavior, a cardinality should be specified only if the data to be read meets the relevant prerequisites. If a cardinality is specified, the syntax check is executed in ABAP_STRICT_791 strict mode as of release ABAP_791 / . ABAP_EXAMPLE_VX5 MANY TO ONE. The data in the DDIC database tables SCARR and SPFLI do not have the cardinality MANY TO ONE, but the reverse cardinality. On an SAP HANA database, for example, the result depends 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 as an aggregate expression, an optimization takes place. Here, only that data is read that meets the prerequisite cardinality. Return to menu
