Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses →  SELECT - FROM →  SELECT - FROM JOIN 

Multiple Joins

This example demonstrates chaining and compounding of multiple joins in the SELECT statement.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA itab TYPE TABLE OF wa WITH DEFAULT KEY.
    DATA jtab LIKE itab.
    out->begin_section( `Joins` ).
    out->begin_section( `1. demo1 INNER JOIN demo2` ).
    SELECT FROM
             demo_join1 AS t1
               INNER JOIN
                 demo_join2 AS t2 ON t2~d = t1~d
           FIELDS 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
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `2. demo3 INNER JOIN demo4 ` ).
    SELECT FROM
             demo_join3 AS t3
               INNER JOIN
                 demo_join4 AS t4 ON t4~l = t3~l
           FIELDS t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3,
                  t4~l AS l4,
                  t4~m AS m4,
                  t4~n AS n4
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `3a. demo1 INNER JOIN demo2 ` &&
                       `LEFT OUTER JOIN demo3` ).
    SELECT FROM
             demo_join1 AS t1
               INNER JOIN
                 demo_join2 AS t2 ON t2~d = t1~d
                     LEFT OUTER JOIN
                       demo_join3 AS t3 ON t3~l = t2~d
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `3b. (demo1 INNER JOIN demo2) ` &&
                       `LEFT OUTER JOIN demo3` ).
    SELECT FROM
             ( demo_join1 AS t1
                 INNER JOIN
                   demo_join2 AS t2 ON t2~d = t1~d )
                     LEFT OUTER JOIN
                       demo_join3 AS t3 ON t3~l = t2~d
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `4a. demo1 INNER JOIN demo2 ` &&
                       `LEFT OUTER JOIN demo3 INNER JOIN demo4` ).
    SELECT FROM
             demo_join1 AS t1
               INNER JOIN
                 demo_join2 AS t2 ON t2~d = t1~d
                   LEFT OUTER JOIN
                     demo_join3 AS t3 ON t3~l = t2~d
                       INNER JOIN
                         demo_join4 AS t4 ON t4~l = t3~l
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3,
                  t4~l AS l4,
                  t4~m AS m4,
                  t4~n AS n4
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `4b. ((demo1 INNER JOIN demo2) ` &&
                       `LEFT OUTER JOIN demo3) INNER JOIN demo4` ).
    SELECT FROM
             ( ( demo_join1 AS t1
                   INNER JOIN
                     demo_join2 AS t2 ON t2~d = t1~d )
                       LEFT OUTER JOIN
                         demo_join3 AS t3 ON t3~l = t2~d )
                           INNER JOIN
                             demo_join4 AS t4 ON t4~l = t3~l
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3,
                  t4~l AS l4,
                  t4~m AS m4,
                  t4~n AS n4
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `5a. demo1 INNER JOIN demo2 ` &&
                       `LEFT OUTER JOIN demo3 INNER JOIN demo4` ).
    SELECT FROM
             demo_join1 AS t1
               INNER JOIN
                 demo_join2 AS t2 ON t2~d = t1~d
               LEFT OUTER JOIN
                 demo_join3 AS t3
                   INNER JOIN
                     demo_join4 AS t4 ON t4~l = t3~l ON t3~l = t2~d
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3,
                  t4~l AS l4,
                  t4~m AS m4,
                  t4~n AS n4
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `5b. (demo1 INNER JOIN demo2) ` &&
                       `LEFT OUTER JOIN (demo3 INNER JOIN demo4)` ).
    SELECT FROM
             ( demo_join1 AS t1
                 INNER JOIN
                   demo_join2 AS t2 ON t2~d = t1~d )
               LEFT OUTER JOIN
                 ( demo_join3 AS t3
                     INNER JOIN
                       demo_join4 AS t4 ON t4~l = t3~l ) ON t3~l = t2~d
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3,
                  t4~l AS l4,
                  t4~m AS m4,
                  t4~n AS n4
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `6a. demo1 CROSS JOIN demo2 ` &&
                       `CROSS JOIN demo3` ).
    SELECT FROM
             demo_join1 AS t1
               CROSS JOIN
                 demo_join2 AS t2
                     CROSS JOIN
                       demo_join3 AS t3
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    jtab = itab.
    out->write( itab
      )->next_section( `6b. ( demo1 CROSS JOIN demo2 ) ` &&
                       `CROSS JOIN demo3` ).
    SELECT FROM
            ( demo_join1 AS t1
               CROSS JOIN
                  demo_join2 AS t2 )
                      CROSS JOIN
                        demo_join3 AS t3
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    ASSERT itab = jtab.
    out->write( itab
      )->next_section( `6c. demo1 CROSS JOIN ( demo2 ` &&
                       `CROSS JOIN demo3 )` ).
    SELECT FROM
             demo_join1 AS t1
               CROSS JOIN
                 ( demo_join2 AS t2
                       CROSS JOIN
                         demo_join3 AS t3 )
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    ASSERT jtab = itab.
    out->write( itab
      )->next_section( `7a. demo1 CROSS JOIN demo2 ` &&
                       `RIGHT OUTER demo3` ).
    SELECT FROM
             demo_join1 AS t1
               CROSS JOIN
                 demo_join2 AS t2
                     RIGHT OUTER JOIN
                       demo_join3 AS t3 ON t3~l = t2~d
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `7b. ( demo1 CROSS JOIN demo2 ) ` &&
                       `RIGHT OUTER demo3` ).
    SELECT FROM
             ( demo_join1 AS t1
                 CROSS JOIN
                   demo_join2 AS t2 )
                       RIGHT OUTER JOIN
                         demo_join3 AS t3 ON t3~l = t2~d
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SORT itab.
    out->write( itab
      )->next_section( `7c. demo1 CROSS JOIN ( demo2  ` &&
                       `RIGHT OUTER demo3 )` ).
    SELECT FROM
             demo_join1 AS t1
               CROSS JOIN
                 ( demo_join2 AS t2
                       RIGHT OUTER JOIN
                         demo_join3 AS t3 ON t3~l = t2~d )
           FIELDS 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,
                  t3~i AS i3,
                  t3~j AS j3,
                  t3~k AS k3,
                  t3~l AS l3
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    SELECT COUNT( * ) FROM demo_join1
                      INTO @DATA(left).
    SELECT COUNT( * ) FROM demo_join2 AS t2 RIGHT OUTER JOIN
                           demo_join3 AS t3 ON t3~l = t2~d
                      INTO @DATA(right).
    ASSERT lines( itab ) = left * right.
    SORT itab.
    out->display( itab ).

Description

Chaining of up to four database tables using inner, outer, and cross joins.

  • Inner join between tables DEMO_JOIN1 and DEMO_JOIN2. The results set contains three rows, in which column D of DEMO_JOIN1 has the same content as column D of DEMO_JOIN2.
  • Inner join between tables DEMO_JOIN3 and DEMO_JOIN4. The results set contains three rows, in which column L of DEMO_JOIN3 has the same content as column L of DEMO_JOIN4.
  • Outer join between results set of point 1 and table DEMO_JOIN3. The results set contains three rows. In addition to the row, in which column L of DEMO_JOIN3 and column D of DEMO_JOIN2 from the results set of point 1 have the same content, there are two rows, in which the columns of DEMO_JOIN3 are filled with zero values, because they do not meet the join condition. In variant 3b, explicit parentheses are used to visualize the implicit parentheses in variant 3a.
  • Inner join between results set of point 3 and table DEMO_JOIN4. The results set contains a row, in which column L of DEMO_JOIN4 has the same content as column L of DEMO_JOIN3 from the results set of point 3. In variant 4b, explicit parentheses are used to visualize the implicit parentheses in variant 4a.
  • Outer join between the results set of point 1 and results set of point 2. The results set contains three rows. In addition to the row, in which column L ofDEMO_JOIN3 from the results set of point 2 and column D of DEMO_JOIN2 from the results set of point 1 have the same content, there are two rows, in which the columns from the results set of point 2 are filled with zero values, because they do not meet the join condition. In variant 5b, explicit parentheses are used to visualize the implicit parentheses in variant 5a.
  • Cross joins between the tables DEMO_JOIN1, DEMO_JOIN2, and DEMO_JOIN3. The results set is not determined by the order of the evaluation defined by the parentheses. The number of rows is determined by 4 x 3 x 3 = 36.
  • Combinations of a cross join with a right outer join executed in different orders.
    • The parenthesis in variant 7b matches the implicit order of variant 7a. The cross join is evaluated first and its results set is joined with the database table DEMO_JOIN3 using the right outer join.
    • The parentheses in variant 7c produce a different result. A cross join is created between DEMO_JOIN1 and the results set of the right outer join between DEMO_JOIN2 and DEMO_JOIN3. The number of rows is calculated from 4 x 3 x 12 = 36.