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

Inner and Outer Joins

This example demonstrates inner and outer joins in the SELECT statement.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA itab TYPE TABLE OF wa.
    out->begin_section( `Inner Joins`
      )->begin_section( `demo1 INNER JOIN demo2` ).
    SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1,
           t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2
       FROM demo_join1 AS t1
         INNER JOIN demo_join2 AS t2 ON t2~d = t1~d
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->next_section( `demo2 INNER JOIN demo1` ).
    SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1,
           t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2
       FROM demo_join2 AS t2
         INNER JOIN demo_join1 AS t1 ON t1~d = t2~d
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->end_section(
      )->next_section( `Outer Joins`
      )->begin_section( `demo1 LEFT OUTER JOIN demo2` ).
    SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1,
           t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2
       FROM demo_join1 AS t1
         LEFT OUTER JOIN demo_join2 AS t2 ON t2~d = t1~d
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->next_section( `demo2 LEFT OUTER JOIN demo1` ).
    SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1,
           t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2
       FROM demo_join2 AS t2
         LEFT OUTER JOIN demo_join1 AS t1 ON t1~d = t2~d
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->end_section(
      )->begin_section( `demo1 RIGHT OUTER JOIN demo2` ).
    SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1,
           t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2
       FROM demo_join1 AS t1
         RIGHT OUTER JOIN demo_join2 AS t2 ON t2~d = t1~d
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->next_section( `demo2 RIGHT OUTER JOIN demo1` ).
    SELECT t1~a AS a1, t1~b AS b1, t1~c AS c1, t1~d AS d1,
           t2~d AS d2, t2~e AS e2, t2~f AS f2, t2~g AS g2, t2~h AS h2
       FROM demo_join2 AS t2
         RIGHT OUTER JOIN demo_join1 AS t1 ON t1~d = t2~d
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->display( itab ).

Description

Inner and outer joins between two tables DEMO_JOIN1 and DEMO_JOIN2, for which the last column of DEMO_JOIN1 matches the first column of DEMO_JOIN2 in the join conditions.

Both inner joins between the tables produce the same result set, regardless of whether they are arranged to the left or the right. There are three line combinations for which the join condition is met.

The result sets of the two left joins depends on the arrangement of the tables on the left and right side. Apart from the three lines which the inner join already produces, for each extra line on the left-hand side an empty line (with null values) is created on the right-hand side. The fact that the empty line is displayed on the left in the final result is due to the structure of the internal table in the INTO clause, in which the columns of DEMO_JOIN1 are always read in from the left and those of DEMO_JOIN2 are always read in from the right. If * was specified in the SELECT clause, and CORRESPONDING FIELDS was not used in the INTO clause, the result would be arranged differently.

The result sets of both right outer joins exactly correspond to the result sets of both previous left outer joins - with the database tables specified in reverse order.

See also the example SQL Expressions, Coalesce Function, in which the default initial values set for the null values in the outer join are replaced by a different value.