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 

Inner, Outer, and Cross Joins

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

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA: itab  TYPE TABLE OF wa,
          itabi LIKE itab,
          itab1 LIKE itab,
          itab2 LIKE itab,
          itab3 LIKE itab.
    out->begin_section( `Inner Joins`
      )->begin_section( `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
           ORDER BY t1~d
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    itabi = itab.
    out->write( itab
      )->next_section( `demo2 INNER JOIN demo1` ).
    SELECT FROM demo_join2 AS t2
             INNER JOIN demo_join1 AS t1 ON t1~d = 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
           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 FROM demo_join1 AS t1
             LEFT OUTER 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
           ORDER BY t1~d
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->next_section( `demo2 LEFT OUTER JOIN demo1` ).
    SELECT FROM demo_join2 AS t2
             LEFT OUTER JOIN demo_join1 AS t1 ON t1~d = 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
           ORDER BY t1~d
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->end_section(
      )->begin_section( `demo1 RIGHT OUTER JOIN demo2` ).
    SELECT FROM demo_join1 AS t1
             RIGHT OUTER 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
          ORDER BY t1~d
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->next_section( `demo2 RIGHT OUTER JOIN demo1` ).
    SELECT FROM demo_join2 AS t2
             RIGHT OUTER JOIN demo_join1 AS t1 ON t1~d = 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
           ORDER BY t1~d
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    out->write( itab
      )->end_section(
      )->next_section( `Cross Joins`
      )->begin_section( `demo2 CROSS JOIN demo1` ).
    SELECT FROM demo_join2 AS t2
             INNER JOIN demo_join1 AS t1 ON 1 = 1
           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
           ORDER BY t1~a,
                    t1~b,
                    t1~c,
                    t1~d,
                    t2~d,
                    t2~e,
                    t2~f,
                    t2~g,
                    t2~h
           INTO CORRESPONDING FIELDS OF TABLE @itab1.
    SELECT FROM demo_join2 AS t2
             LEFT OUTER JOIN demo_join1 AS t1 ON 1 = 1
           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
           ORDER BY t1~a,
                    t1~b,
                    t1~c,
                    t1~d,
                    t2~d,
                    t2~e,
                    t2~f,
                    t2~g,
                    t2~h
           INTO CORRESPONDING FIELDS OF TABLE @itab2.
    SELECT FROM demo_join2 AS t2
             RIGHT OUTER JOIN demo_join1 AS t1 ON 1 = 1
           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
           ORDER BY t1~a,
                    t1~b,
                    t1~c,
                    t1~d,
                    t2~d,
                    t2~e,
                    t2~f,
                    t2~g,
                    t2~h
           INTO CORRESPONDING FIELDS OF TABLE @itab3.
    SELECT FROM demo_join2 AS t2
             CROSS JOIN demo_join1 AS t1
           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
           ORDER BY t1~a,
                    t1~b,
                    t1~c,
                    t1~d,
                    t2~d,
                    t2~e,
                    t2~f,
                    t2~g,
                    t2~h
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    ASSERT itab = itab1.
    ASSERT itab = itab2.
    ASSERT itab = itab3.
    out->write( itab
      )->next_section( `demo1 CROSS JOIN demo2` ).
    SELECT FROM demo_join1 AS t1
             CROSS JOIN demo_join2 AS t2
           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
           ORDER BY t1~a,
                    t1~b,
                    t1~c,
                    t1~d,
                    t2~d,
                    t2~e,
                    t2~f,
                    t2~g,
                    t2~h
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    ASSERT itab = itab1.
    ASSERT itab = itab2.
    ASSERT itab = itab3.
    out->write( itab
      )->next_section( `demo1 CROSS JOIN demo2 WHERE ...` ).
    SELECT FROM demo_join1 AS t1
             CROSS JOIN demo_join2 AS t2
           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
           WHERE t2~d = t1~d
           ORDER BY t1~d
           INTO CORRESPONDING FIELDS OF TABLE @itab.
    ASSERT itab = itabi.
    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 results set, regardless of whether they are arranged on the left or the right. Three row combinations meet the join condition.
  • The results sets of the two left joins depends on the arrangement of the tables on the left and right side. Apart from the three rows already produced by the inner join, an empty row (with null values) is created on the right for each extra row on the left. The fact that the empty row is displayed on the left in the final result is due to the structure of the internal table in the INTO clause. Here, the columns of DEMO_JOIN1 are always read from the left and those of DEMO_JOIN2 are always read from the right. If * was specified in the SELECT list, and CORRESPONDING FIELDS was not used in the INTO clause, the result would be arranged differently.
  • The results 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).
  • The results sets of both cross joins without a WHERE condition are identical to the result sets of the corresponding inner and outer joins whose ON condition is always true. It makes no difference which side the data sources are specified on in the cross join. The number of rows is determined by 4 x 3 = 12.
  • The results set of the cross join with a WHERE condition is identical to the results set of an inner join with a corresponding ON condition. Note that in a cross join, all rows are read first and then selected, whereas the inner join only reads the rows that meet the conditions.

See also the executable 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.