SELECT - JOIN - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Reads → SELECT clauses → SELECT - FROM →

SELECT - JOIN

Quick Reference

Syntax

... [(] { data_source [AS tabalias]}|join
{[INNER] JOIN}|{LEFT|RIGHT [OUTER] JOIN}|{CROSS JOIN}
{ data_source [AS tabalias]}|join [ON join_cond] [)] ... .

Addition:

... ON join_cond

Effect

Joins the columns of two or more data sources in a results 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)

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

The following applies to entries specified on the left side and on the right side:

data_source is a single data source. Database tables must be transparent. As when specified as a single source after FROM using AS, an alternative table name can be specified for the data source. A data source can exist more than once within a join expression, and can have various alternative names. Pooled and cluster tables cannot be joined using join expressions.
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 defined so that the SELECT statement can be executed on all supported database systems and is currently set to 50 . If known statically, more than 49 joins produce a syntax error. If not, 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 be enclosed in parentheses, ( ). This is optional. 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 influenced by parentheses ( ).

If several 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.

Results set for inner joins

In a single results set, an inner join joins the columns of the rows in the results set of the left side with the columns of the rows in the results set of the right side. This results set contains all combinations of rows whose columns meet the condition join_cond. If there are no rows in the results set of the left and right sides that meet join_cond, a row is not created in the resulting results set.

Results set for outer joins

The outer join creates the same results 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 results set, even if no rows on the other side meet the condition join_cond. The columns on the other side that do not meet the condition join_cond are filled with null values.

Results set for cross join

The cross join forms a cross product of the results set of the left side and the results set of the right side. The cross join joins the columns of the rows in the results set of the left side with the columns of the rows in the results set of the right side. This results set contains all possible combinations of rows. The number of rows in the results set of the cross join is the product of the number of rows of both joined results 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 clauses in question.
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.
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.
A cross join of two client-specific 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-specific, 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 SAP buffering. Therefore they should not be applied to buffered tables. Instead it may be a good idea to use the addition FOR ALL ENTRIES in these cases, 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 (in the case of null values) can depend on the database system.
The function coalesce can be used to replace zero 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 7.40, 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 (and not a data source) is on the right side of a join expression.

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 7.40, 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 strict mode for Release 7.51 in the following cases:

Uses CROSS JOIN.

Executable Example

Inner, outer, and cross joins

Addition

... ON join_cond

Effect

Join condition. A join condition must be specified for an inner or outer join. A join condition does not have to be specified for a cross join.

The syntax of the join conditions join_cond is the same as for the conditions sql_cond after the addition WHERE, but with the following differences:

After ON, at least one comparison must be specified.
SQL expressions cannot be used on the right side.
The expression [NOT] IN seltab cannot be used.
Subqueries cannot be used.
Path expressions cannot be used.
A dynamic condition (cond_syntax) can be specified only if the FROM clause is specified statically and is not specified dynamically as (source_syntax).
Comparisons between table columns that do not have the same data type and length in the database can behave differently on different database platforms. The differences in behavior can produce different results or in SQL errors on individual platforms. This is because the join condition is evaluated fully in the database and no ABAP type conversion takes place beforehand. The behavior displayed depends fully on the conversion rules of the database. However, databases generally offer fewer conversion options than ABAP.

Notes

It is strongly recommended that join conditions are used only between database columns with the same type and length.
If the pattern consists of precisely one "%" character in a comparison using LIKE, the same optimization takes place as when using 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 7.50, which handles the statement more strictly than the regular syntax check.
If

SQL expressions are used on the left side of the ON condition of any join or

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 7.50, which handles the statement more strictly than the regular syntax check.

Example

Join of the columns carrname, connid, and fldate of the database tables scarr, spfli, and sflight using two inner joins. This creates a list of flights from p_cityfr to p_cityto. An alias name is assigned to each table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,
            p_cityto TYPE spfli-cityto.

TYPES: BEGIN OF wa,
         fldate TYPE sflight-fldate,
         carrname TYPE scarr-carrname,
         connid   TYPE spfli-connid,
       END OF wa.

DATA itab TYPE SORTED TABLE OF wa
          WITH UNIQUE KEY fldate carrname connid.

SELECT c~carrname, p~connid, f~fldate
       FROM ( ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid
                              AND p~cityfrom = @p_cityfr
                              AND p~cityto   = @p_cityto )
         INNER JOIN sflight AS f ON f~carrid = p~carrid
                                AND f~connid = p~connid )
       INTO CORRESPONDING FIELDS OF TABLE @itab.

cl_demo_output=>display( itab ).

Example

Join the columns carrid, carrname, and connid of the database tables scarr and spfli using a left outer join. For all flights that do not depart from p_cityfr, the column connid is set to the null value that was transformed to the type-friendly initial value (when passed to the associated data object). All the airlines that do not fly from p_cityfr are displayed.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

TYPES: BEGIN OF wa,
         carrid   TYPE scarr-carrid,
         carrname TYPE scarr-carrname,
         connid   TYPE spfli-connid,
      END OF wa.
DATA  itab TYPE SORTED TABLE OF wa
                WITH NON-UNIQUE KEY carrid.

SELECT s~carrid, s~carrname, p~connid
       FROM scarr AS s
       LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid
                                  AND p~cityfrom = @p_cityfr
       INTO CORRESPONDING FIELDS OF TABLE @itab.

DELETE itab WHERE connid <> '0000'.

cl_demo_output=>display( itab ).

Example

Cross join of table T000 of all clients of an AS ABAP with the entries for the message classSABAPDEMOS in the table T100. Without the WHERE condition, the results set would be very large.

DATA BEGIN OF wa.
DATA mandt TYPE t000-mandt.
DATA mtext TYPE t000-mtext.
INCLUDE TYPE t100.
DATA END OF wa.
DATA itab LIKE STANDARD TABLE OF wa WITH EMPTY KEY.

SELECT t000~mandt, t000~mtext, t100~*
       FROM t000 CROSS JOIN t100
       WHERE t100~arbgb = 'SABAPDEMOS'
       ORDER BY t000~mandt, t100~sprsl, t100~msgnr
       INTO TABLE @itab.

cl_demo_output=>display( itab ).

Executable Example

Multiple Joins

Continue

Inner, Outer, and Cross Joins
Example

Multiple Joins