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

Joins

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

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA itab LIKE TABLE OF wa.
    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
       INTO CORRESPONDING FIELDS OF TABLE itab
       ORDER BY t1~d.
    output( title  = 'demo1 INNER JOIN demo2'
            table  = itab ).
    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
       INTO CORRESPONDING FIELDS OF TABLE itab
       ORDER BY t1~d.
    output( title  = 'demo2 INNER JOIN demo1'
            same_line = abap_true
            table  = itab ).
    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
       INTO CORRESPONDING FIELDS OF TABLE itab
       ORDER BY t1~d.
    output( title  = 'demo1 OUTER JOIN demo2'
            table  = itab ).
    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
       INTO CORRESPONDING FIELDS OF TABLE itab
       ORDER BY t1~d.
    output( title  = 'demo2 OUTER JOIN demo1'
            same_line = abap_true
            table  = itab ).

Description

Inner and outer joins between two tables DEMO_JOIN1 and DEMO_JOIN2, for which the last column of DEMO_JOIN1 is equated with the first column of DEMO_JOIN2 in the join conditions. The results are as follows:

demo1           demo2
-----           -----
a1 b1 c1 uu     uu e1 f1 g1 h1
a2 b2 c2 uu     ww e2 f2 g2 h2
a3 b3 c3 vv     xx e3 f3 g3 h3
a4 b4 c4 ww

demo1 INNER JOIN demo2         demo2 INNER JOIN demo1
----------------------         ----------------------
a1 b1 c1 uu uu e1 f1 g1 h1     a1 b1 c1 uu uu e1 f1 g1 h1
a2 b2 c2 uu uu e1 f1 g1 h1     a2 b2 c2 uu uu e1 f1 g1 h1
a4 b4 c4 ww ww e2 f2 g2 h2     a4 b4 c4 ww ww e2 f2 g2 h2

demo1 OUTER JOIN demo2         demo2 OUTER JOIN demo1
----------------------         ----------------------
a1 b1 c1 uu uu e1 f1 g1 h1     a1 b1 c1 uu uu e1 f1 g1 h1
a2 b2 c2 uu uu e1 f1 g1 h1     a2 b2 c2 uu uu e1 f1 g1 h1
a3 b3 c3 vv                    a4 b4 c4 ww ww e2 f2 g2 h2
a4 b4 c4 ww ww e2 f2 g2 h2                 xx e3 f3 g3 h3

Both inner joins between the tables result in the same result set, irrelevant of the order to the left or the right. There are three line combinations for which the join condition is fulfilled.

The result sets of the two outer 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 the * specification was used in the SELECT clause, and CORRESPONDING FIELDS was not used in the INTO clause, the result would be arranged differently.