SELECT Inner join – left outer join – right outer join (- Cross join)

Bsp. für die Verwendung von left outer join, right outer join.

*&---------------------------------------------------------------------*
*& Report  ZTEST38
*&
*&---------------------------------------------------------------------*
*&
*& Demonstration of SQL Joins
*&
*&---------------------------------------------------------------------*

REPORT ztest38 NO STANDARD PAGE HEADING.
"REPORT demo_joins

DATA out TYPE REF TO if_demo_output.
out = cl_demo_output=>new( )->begin_section( `Database Tables` )->end_section( ).

DELETE FROM demo_join1.
*DATA wa_1 TYPE demo_join1.
*wa_1-a = 'a1'. wa_1-b = 'b1'. wa_1-c = 'c1'. wa_1-d = 'uu'.
*INSERT INTO demo_join1 VALUES wa_1.
*wa_1-a = 'a2'. wa_1-b = 'b2'. wa_1-c = 'c2'. wa_1-d = 'uu'.
*INSERT INTO demo_join1 VALUES wa_1.
*wa_1-a = 'a3'. wa_1-b = 'b3'. wa_1-c = 'c3'. wa_1-d = 'vv'.
*INSERT INTO demo_join1 VALUES wa_1.
*wa_1-a = 'a4'. wa_1-b = 'b4'. wa_1-c = 'c4'. wa_1-d = 'ww'.
*INSERT INTO demo_join1 VALUES wa_1.
DATA wat_1 TYPE TABLE OF demo_join1.
wat_1 = VALUE #(
      ( a = 'a1' b = 'b1' c = 'c1'  d = 'uu' )
      ( a = 'a2' b = 'b2' c = 'c2'  d = 'uu' )
      ( a = 'a3' b = 'b3' c = 'c3'  d = 'vv' )
      ( a = 'a4' b = 'b4' c = 'c4'  d = 'ww' ) ).
INSERT demo_join1 FROM TABLE wat_1.
SELECT * FROM demo_join1 INTO TABLE @DATA(itab1).

DELETE FROM demo_join2.
*DATA wa_2 TYPE demo_join2.
*wa_2-d = 'uu'. wa_2-e = 'e1'. wa_2-f = 'f1'. wa_2-g = 'g1'.  wa_2-h = 'h1'.
*INSERT INTO demo_join2 VALUES wa_2.
*wa_2-d = 'ww'. wa_2-e = 'e2'. wa_2-f = 'f2'. wa_2-g = 'g2'.  wa_2-h = 'h2'.
*INSERT INTO demo_join2 VALUES wa_2.
*wa_2-d = 'xx'. wa_2-e = 'e3'. wa_2-f = 'f3'. wa_2-g = 'g3'.  wa_2-h = 'h3'.
*INSERT INTO demo_join2 VALUES wa_2.
DATA wat_2 TYPE TABLE OF demo_join2.
wat_2 = VALUE #(
      ( d = 'uu' e = 'e1' f = 'f1'  g = 'g1'  h = 'h1' )
      ( d = 'ww' e = 'e2' f = 'f2'  g = 'g2'  h = 'h2' )
      ( d = 'xx' e = 'e3' f = 'f3'  g = 'g3'  h = 'h3' ) ) .
INSERT demo_join2 FROM TABLE wat_2.
SELECT * FROM demo_join2 INTO TABLE @DATA(itab2).

out->begin_section( `demo1`
  )->write( itab1
  )->end_section( ).
out->next_section( `demo2`
  )->write( itab2
  )->end_section( ).

select a~a, a~b, a~c, a~d, b~d as d2, b~e, b~f, b~g, b~h from demo_join1 as a inner join demo_join2 as b on a~d = b~d into table @data(itab3).
"Select mit * funktioniert nicht bei einer Tabelle mit @data
"select * from demo_join1 as a inner join demo_join2 as b on a~d = b~d into table @data(itab3).
out->begin_section( `inner join`
  )->write( itab3
  )->end_section( ).

select a~a, a~b, a~c, a~d, b~d as d2, b~e, b~f, b~g, b~h from demo_join1 as a left outer join demo_join2 as b on a~d = b~d into table @data(itab4).
out->begin_section( `left outer join`
  )->write( itab4
  )->end_section( ).


select a~a, a~b, a~c, a~d, b~d as d2, b~e, b~f, b~g, b~h from demo_join1 as a right outer join demo_join2 as b on a~d = b~d into table @data(itab5).
out->begin_section( `right outer join`
  )->write( itab5
  )->end_section( ).

"Cross Join erst ab Version ABAP 7.51
*select a~a, a~b, a~c, a~d, b~d as d2, b~e, b~f, b~g, b~h from demo_join1 as a cross join demo_join2 as b into table @data(itab6).
*out->begin_section( `cross join`
*  )->write( itab5
*  )->end_section( ).
out->begin_section( `cross join erst ab Version ABAP 7.51`
  )->end_section( ).



out->display( ).

Für die besonders interessierten hier ein Link auf eine visuelle Darstellung, die hier noch weit darüber hinausgeht:

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/