SQL outer join vs. join of internal tables

Pattern how to realise the SQL outer join with the loops over the internal tables.

The sample program below generates following output:

Here the source code:

REPORT zitab_outjoin.

TYPES:
  BEGIN OF ts_data,
    carrid   TYPE s_carr_id,
    carrname TYPE s_carrname,
    connid   TYPE s_conn_id,
    cityfrom TYPE s_city,
  END OF ts_data,

  tt_data TYPE TABLE OF ts_data.

PERFORM database_join.
SKIP.
PERFORM internal_table_join.

*&---------------------------------------------------------------------*
*&      Form  database_join
*&---------------------------------------------------------------------*
FORM database_join.

  DATA:
    lt_data TYPE tt_data,
    ls_data TYPE ts_data,
    m1 TYPE i,
    m2 TYPE i.

  GET RUN TIME FIELD m1.

  SELECT scarr~carrid scarr~carrname spfli~connid spfli~cityfrom
    INTO (ls_data-carrid, ls_data-carrname, ls_data-connid, ls_data-cityfrom)
    FROM scarr
    LEFT OUTER JOIN spfli ON scarr~carrid = spfli~carrid
    WHERE scarr~carrid BETWEEN 'AA' AND 'LH'
    ORDER BY scarr~carrid scarr~carrname spfli~connid.

    APPEND ls_data TO lt_data.
  ENDSELECT.

  GET RUN TIME FIELD m2.

  LOOP AT lt_data INTO ls_data.
    WRITE: / ls_data-carrid, ls_data-carrname, ls_data-connid, ls_data-cityfrom.
  ENDLOOP.

  m2 = m2 - m1.
  WRITE: / 'db join', m2, 'microseconds'.
ENDFORM.                    "database_join

*&---------------------------------------------------------------------*
*&      Form  internal_table_join
*&---------------------------------------------------------------------*
FORM internal_table_join.

  DATA:
    lt_data TYPE tt_data,
    ls_data TYPE ts_data,
    lt_scarr TYPE SORTED TABLE OF scarr WITH NON-UNIQUE KEY carrid,
    ls_scarr TYPE scarr,
    lt_spfli TYPE HASHED TABLE OF spfli WITH UNIQUE KEY carrid connid,
    ls_spfli TYPE spfli,
    m1 TYPE i,
    m2 TYPE i,
    m3 TYPE i,
    m4 TYPE i.

* Init-Load
  GET RUN TIME FIELD m1.

  SELECT carrid carrname
    INTO CORRESPONDING FIELDS OF TABLE lt_scarr
    FROM scarr
    WHERE scarr~carrid BETWEEN 'AA' AND 'LH'
    ORDER BY carrid carrname.

  SELECT carrid connid cityfrom
    INTO CORRESPONDING FIELDS OF TABLE lt_spfli
    FROM spfli.

  GET RUN TIME FIELD m2.
  m2 = m2 - m1.

* Join the internal tables
  GET RUN TIME FIELD m3.

  LOOP AT lt_scarr INTO ls_scarr.

    CLEAR ls_data.
    MOVE-CORRESPONDING ls_scarr TO ls_data.

    LOOP AT lt_spfli INTO ls_spfli
      WHERE carrid = ls_scarr-carrid.

      MOVE-CORRESPONDING ls_spfli TO ls_data.
      APPEND ls_data TO lt_data.
    ENDLOOP.

    IF sy-subrc <> 0.
      APPEND ls_data TO lt_data.
    ENDIF.
  ENDLOOP.

  GET RUN TIME FIELD m4.

  LOOP AT lt_data INTO ls_data.
    WRITE: / ls_data-carrid, ls_data-carrname, ls_data-connid, ls_data-cityfrom.
  ENDLOOP.

  m4 = m4 - m3.
  WRITE: / 'itable join - load ', m2, 'microseconds'.
  WRITE: / 'itable join - loops', m4, 'microseconds'.
  m4 = m4 + m2.
  WRITE: / 'itable join - total', m4, 'microseconds'.
ENDFORM.                    "internal_table_join

See other related notes on my website:

Scroll to Top