FULL OUTER JOIN in ABAP SQL?

[Sandra_Rossi]

FULL OUTER JOIN is still not permitted directly in ABAP 7.58 (ABAP Keyword Documentation: SELECT, FROM JOIN).

What I'd like to do:

    SELECT COALESCE( ekkn~ebeln, ekbe~ebeln ) AS ebeln,
           COALESCE( ekkn~ebelp, ekbe~ebelp ) AS ebelp,
           COALESCE( ekkn~zekkn, ekbe~zekkn ) AS zekkn,
           ekbe~bewtp,
           ekkn~loekz
      FROM ekbe
           FULL OUTER JOIN ekkn     " <=== FULL not valid in ABAP SQL
             ON  ekkn~ebeln = ekbe~ebeln
             AND ekkn~ebelp = ekbe~ebelp
             AND ekkn~zekkn = ekbe~zekkn
      WHERE ...
      INTO TABLE @DATA(itab).

What is the most simple workaround?

Thanks.

Sandra

[Sandra_Rossi]

The most simple workaround is via a CTE (WITH), UNION DISTINCT (UNION, DISTINCT) and two LEFT OUTER JOIN (see "Result set for outer joins"):

    TYPES: BEGIN OF ty_full_outer_join,
             ebeln TYPE ebeln,
             ebelp TYPE ebelp,
             zekkn TYPE dzekkn,
             bewtp TYPE ekbe-bewtp,
             loekz TYPE ekkn-loekz,
           END OF ty_full_outer_join.
    TYPES tt_full_outer_join TYPE STANDARD TABLE OF ty_full_outer_join WITH EMPTY KEY.

    DATA(itab) = VALUE tt_full_outer_join( ).
    WITH +union AS (
        SELECT ebeln, ebelp, zekkn
          FROM ekbe
        UNION DISTINCT
        SELECT ebeln, ebelp, zekkn
          FROM ekkn )
    SELECT +union~ebeln,
           +union~ebelp,
           +union~zekkn,
           ekbe~bewtp,
           ekkn~loekz
      FROM +union
           LEFT OUTER JOIN ekbe
             ON  ekbe~ebeln = +union~ebeln
             AND ekbe~ebelp = +union~ebelp
             AND ekbe~zekkn = +union~zekkn
           LEFT OUTER JOIN ekkn
             ON  ekkn~ebeln = +union~ebeln
             AND ekkn~ebelp = +union~ebelp
             AND ekkn~zekkn = +union~zekkn
      WHERE ...
      INTO TABLE @itab.

[BiberM]

Hi Sandra, 

yes, this would hve been my solution, too. Would this be possible without the CTE? 

Another solution would be AMDP as HANA does Support a FULL OUTER JOIN.

Best regards

Michael

[Sandra_Rossi]

I guess it's possible without the CTE, but it will be less elegant, less legible.

If you find a solution without the CTE, please post it as another solution.

[BiberM]

Hi,

I just tried the following in our dev system with very limited data (4-digit line count in each table). The performance was .... I just did one run.

    SELECT coalesce( ekbe~ebeln, ekkn~ebeln ) as ebeln,
           coalesce( ekbe~ebelp, ekkn~ebelp ) as ebelp,
           coalesce( ekbe~zekkn, ekkn~zekkn ) as zekkn,
           ekbe~bewtp,
           ekkn~loekz
    FROM ekbe
      LEFT OUTER JOIN ekkn
        ON    ekbe~ebeln = ekkn~ebeln
          AND ekbe~ebelp = ekkn~ebelp
          AND ekbe~zekkn = ekkn~zekkn
*    WHERE ...
    UNION
    SELECT coalesce( ekbe~ebeln, ekkn~ebeln ) as ebeln,
           coalesce( ekbe~ebelp, ekkn~ebelp ) as ebelp,
           coalesce( ekbe~zekkn, ekkn~zekkn ) as zekkn,
           ekbe~bewtp,
           ekkn~loekz
    FROM ekkn
      LEFT OUTER JOIN ekbe
        ON    ekbe~ebeln = ekkn~ebeln
          AND ekbe~ebelp = ekkn~ebelp
          AND ekbe~zekkn = ekkn~zekkn
*    WHERE ...
    INTO TABLE @DATA(lt_result).

    out->write( lt_result ).

The idea is to do both "parts" of a FULL OUTER JOIN seperately and then unite them with a UNION. This one especially without the ALL addition in order to remove the duplicates which are the datasets which exists in both tables. 

Best regards
Michael

[HermannGahm]

Hi,

yes this is what i would have suggested as well,

Just with left and right outer in the union branches , see below.

Depending on the database (or engines) used the union branches might be executed in parallel.
In my case the select returns 323 records (EKBE 3211 records  EKKN 582 records)  in 6.5 ms. The
UNION branches are executed in parallel.

With larger volumes (EKBE 20,001,016 rows and 117,896,463) it returns 8345 records in 160 ms.

Kind regards,

Hermann

SELECT COALESCE( ekkn~ebeln, ekbe~ebeln ) AS ebeln,
           COALESCE( ekkn~ebelp, ekbe~ebelp ) AS ebelp,
           COALESCE( ekkn~zekkn, ekbe~zekkn ) AS zekkn,
           ekbe~bewtp,
           ekkn~loekz
      FROM ekbe
           LEFT OUTER JOIN ekkn     " <=== FULL not valid in ABAP SQL
             ON  ekkn~ebeln = ekbe~ebeln
             AND ekkn~ebelp = ekbe~ebelp
             AND ekkn~zekkn = ekbe~zekkn
      WHERE ekbe~gjahr = '2026'
 UNION
  SELECT COALESCE( ekkn~ebeln, ekbe~ebeln ) AS ebeln,
           COALESCE( ekkn~ebelp, ekbe~ebelp ) AS ebelp,
           COALESCE( ekkn~zekkn, ekbe~zekkn ) AS zekkn,
           ekbe~bewtp,
           ekkn~loekz
      FROM ekbe
           RIGHT OUTER JOIN ekkn     " <=== FULL not valid in ABAP SQL
             ON  ekkn~ebeln = ekbe~ebeln
             AND ekkn~ebelp = ekbe~ebelp
             AND ekkn~zekkn = ekbe~zekkn
      WHERE ekkn~aedat = '20260101'
      INTO TABLE (itab).