Ansicht
Dokumentation

ABAPSELECT_JOIN - SELECT JOIN

ABAPSELECT_JOIN - SELECT JOIN

Fill RESBD Structure from EBP Component Structure   BAL_S_LOG - Application Log: Log header data  
This documentation is copyright by SAP AG.
SAP E-Book

SELECT, FROM JOIN

Short Reference



... $[($] ${data_source $[AS tabalias$]$}$|join
          ${$[INNER$] JOIN$}$|${LEFT$|RIGHT $[OUTER $[MANY TO ONE$]$] JOIN$}$|${CROSS JOIN$}
             ${ data_source $[AS tabalias$]$}$|join $[ON sql_cond$] $[)$] ... .


Additions

1. ... ON sql_cond

2. ... MANY TO ONE

Effect

Joins the columns of two or more 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

Every join expression for an inner or outer join must contain a join condition sql_cond after ON (see below). A join expression for a cross join cannot contain any join conditions.

The following applies to possible specifications on the left side and on the right side:

  • 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.

The priority in which nested join expressions are evaluated is specified as follows:

  • 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

An inner join joins the columns of the rows in the result set of the left side with the columns of the rows in the result set of the right side into a single result set. This result set contains all combinations of rows for whose columns the join condition 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

The outer join creates the same result set as the inner join. The difference is that, for each selected row on the left side as 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

The cross join forms a cross product of the result set of the left side and the result set of the right side. The cross join joins the columns of the rows in the result set of the left side with the columns of the rows in the result set of the right side. This result set contains all possible combinations of rows. The number of rows in the result set of the cross join is the product of the number of rows of both joined result sets.

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 it may be a good idea in these cases to use the addition FOR ALL ENTRIES, 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 strict mode for Release 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 strict mode for Release 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 strict mode for Release in the following cases:
  • Use of CROSS JOIN.

Example

Join of the columns 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.

Example

Join of the database tables 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.

Example

Cross join of the DDIC database table 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.

Addition 1

... ON sql_cond

Effect

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 sql_cond is subject to the following restrictions:

  • After ON, at least one comparison must 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).

A join condition is met if the logical expression sql_cond is true.

Implicit 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.

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.
  • If a host expression occurs on the right side of the ON condition, the syntax check is performed in a strict mode from Release , which handles the statement more strictly than the regular syntax check.
  • 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 syntax check is performed in a strict mode from Release , which handles the statement more strictly than the regular syntax check.

Addition 2

MANY TO ONE

Effect

Specifies the cardinality of a left outer join. This addition is possible after LEFT OUTER, but is not possible after RIGHT OUTER. It has an effect only on certain specific database systems.

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

Notes

  • For more information, see the documentation of the current database system. The SAP HANA database, for example, supports the addition MANY TO ONE and its description is part of the documentation of HANA-specific SQL.
  • To avoid undefined and platform-dependent behavior, MANY TO ONE can be specified only if the data to be read meets the relevant prerequisites.
  • If MANY TO ONE is specified, the syntax check is executed in strict mode as of release .

Example

Incorrect use of MANY TO ONE. The data in the DDIC database tables SCARR and SPFLI do not have the cardinality MANY TO ONE and have the reverse cardinality instead. 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.






ABAP Short Reference   RFUMSV00 - Advance Return for Tax on Sales/Purchases  
This documentation is copyright by SAP AG.

Length: 19530 Date: 20240329 Time: 055128     sap01-206 ( 297 ms )