Skip to content

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

SELECT - JOIN

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... [(] {data_source [AS tabalias]}|join 

          {[INNER] JOIN}|{LEFT|RIGHT [OUTER] JOIN} 

             {data_source [AS tabalias]}|join ON join_cond [)] ...  .

Addition

... ON join_cond

Effect

Joins the columns of two or more database tables in a results set in a join expression. A join expression joins a left side with a right side, using either [INNER] JOIN or LEFT|RIGHT [OUTER] JOIN. A join expression can be an inner join (INNER) or an outer join (LEFT OUTER) or RIGHT OUTER) join. Every join expression must contain a join condition join_cond after ON (see below). The following applies to entries specified on the left side and on the right side:

  • data_source is an individual transparent database table or a view, for which an alternative name can be specified after FROM with AS (as is the case when specifying individually). A database table or a view 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 maximum number of individual joins in a join expression is 24. A maximum of 25 transparent database tables or views can be joined using these joins.
  • A join expression must exclusively consist of database tables and classic views or exclusively consist of CDS entities. A CDS view can only be used together with database tables and classic views, if it is addressed using its CDS database view.

Every single join expression can be enclosed in parentheses ( ) (optional), to influence the priority of the evaluation. If no parentheses are specified, consecutive join expressions

... side1 JOIN side2 JOIN side3 JOIN side4 ...

are parenthesized implicitly from left to right as follows:

... ( ( ( side1 JOIN side2 ) JOIN side3 ) JOIN side4 ) ...

If parenthesized implicitly, the right side of a join expression is always a database table or a view.


Example

See Multiple Joins.

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, then a row is not created in the resulting results set.


Example

See Inner and Outer Joins.

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 fulfill the condition join_cond. The columns on the other side that do not meet the condition join_cond are filled with null values.


Notes

  • A WHERE condition for a SELECT statement with joins is applied to the results set created using the joins.
  • Inner joins between two individual database tables are commutative.
  • If the same column name appears in multiple database tables of a single join expression, these tables 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 listed as LEFT OUTER JOIN or columns from the left side are listed as RIGHT OUTER JOIN, after 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 SP05 in the following cases:

  • Not all comparisons of an ON condition contain a column from a database table or view specified on the right side as an operand.

  • Multiple consecutive joins are explicitly parenthesized so that a join expression (and not a database table or view) 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.

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


Example

See Inner and Outer Joins.

Addition

... ON join_cond

Effect

Join condition. 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.
  • Subqueries cannot be used.
  • If the pattern consists of precisely one "%" character in comparisons using LIKE, no optimization is performed (unlike in WHERE) and the condition is passed to the database instead.
  • 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 different behavior can result in 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.
  • There is no Open SQL-specific optimization in comparisons using LIKE in join conditions, which means that col LIKE '%' is not true (unlike in WHERE) if the column col contains null values.


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 initial value for its type (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 ).

Continue

Inner and Outer Joins - Example

Multiple Joins - Example