Skip to content

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

SELECT - join

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... [(] {dbtab_left [AS tabalias_left]} | join 
          {[INNER] JOIN}|{LEFT [OUTER] JOIN} 

            {dbtab_right [AS tabalias_right] ON join_cond} [)] ...  .

Effect

Joins the columns of two or more database tables in a results set.

The syntax of join is a recursively nestable join expression. A join expression consists of a left side and a right side, joined using either [INNER] JOIN or LEFT [OUTER] JOIN. A join expression can be an inner join (INNER) or an outer join (LEFT OUTER). You can enclose every join expression in parentheses. When you use a join expression, the SELECT statement is not subject to SAP buffering.

On the left side, you can specify either a transparent database table, a view dbtab_left, or another join expression join. On the right side, you must specify a single transparent database table or a view dbtab_right and join conditions join_cond after ON. In this way, you can specify a maximum of 24 join expressions after FROM; these expressions join 25 transparent database tables or views together. Pooled and cluster tables cannot be joined using join expressions.

For each of the specified database tables, or for each view, you can use AS to specify an alternative table name tabalias. A database table or a view can exist more than once within a join expression, and can have various alternative names.

Results set for inner joins

In a single results set, an inner join links 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 Joins.

Results set for outer joins

In principle, the outer join creates the same results set as the inner join, with the difference that, for each selected row on the left side, at least one row is created in the results set, even if no rows on the right side fulfill the condition join_cond. The columns on the right side that do not meet the condition join_cond are filled with null values.


Example

See Joins.

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, you must specify at least one comparison.
  • Single comparisons can only be joined using AND.
  • All comparisons must contain a column of the database table or view dbtab_right of the right side, as an operand.
  • You cannot use the following additions: NOT, LIKE, IN.
  • You cannot use subqueries.
  • In the outer join, only equality comparisons are possible (=, EQ).
  • If there is an outer join after FROM, the join condition of each join expression must contain at least one comparison between the columns on the left and right sides.
  • In outer joins, all comparisons that contain columns from the database table or view dbtab_right on the right side (as an operand) must be specified in the associated join condition. These columns are not allowed as operands in the WHERE condition of the same SELECT statement.
  • 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

  • The results sets of the left and right sides are independent of each other and are constructed in the same way as SELECT statements. For example, a WHERE condition for a column in a database table applies to precisely the results set of this table.
  • 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 ~.
  • Since join expressions are not subject to SAP buffering, they should not be used on 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.
  • It is strongly recommended that you use join conditions only between database columns with the same type and length.


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. 

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

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

SELECT c~carrname p~connid f~fldate 
       INTO CORRESPONDING FIELDS OF TABLE itab 
       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 ). 

LOOP AT itab INTO wa. 
  WRITE: / wa-fldate, wa-carrname, wa-connid. 
ENDLOOP.

Example

Join of the columns carrid, carrname, and connid of the database tables scarr and spfli using an 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). The LOOP displays all carriers that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom. 

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

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

LOOP AT itab INTO wa. 
  IF wa-connid = '0000'. 
    WRITE: / wa-carrid, wa-carrname. 
  ENDIF. 
ENDLOOP.