SQL inner join vs. join of internal tables
I like SQL joins because of their compact form, which improves readability of code and makes it safe.
Sometimes I can't use SQL joins because of performance issues or in situations, in which I already have data in internal tables, for example as result of function module, than I have to join the internal tables.
Sometimes I can't use SQL joins because of performance issues or in situations, in which I already have data in internal tables, for example as result of function module, than I have to join the internal tables.
There is no support for join the internal tables in ABAP (apart the PROVIDE, which can be used in very special cases),
the join of internal tables has to be programmed with nested LOOP's or combination of LOOP and READ.
The optimal solution depends on the definition of internal tables and their content - the used data model.
Below you can find an example of SQL join and join of internal tables realized with nested LOOP's.
The standard flight application deals as data model, you can generate the content of used tables SPFLI, SBOOK and SCUSTOM with program SAPBC_DATA_GENERATOR.
Below you can find an example of SQL join and join of internal tables realized with nested LOOP's.
The standard flight application deals as data model, you can generate the content of used tables SPFLI, SBOOK and SCUSTOM with program SAPBC_DATA_GENERATOR.
The sample program below generates following output:
REPORT zitab_injoin.
TYPES:
BEGIN OF ts_data,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
fldate TYPE s_date,
name TYPE s_custname,
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 spfli~carrid spfli~connid sbook~fldate scustom~name
INTO (ls_data-carrid, ls_data-connid, ls_data-fldate, ls_data-name)
FROM spfli
INNER JOIN sbook
ON spfli~carrid = sbook~carrid
AND spfli~connid = sbook~connid
INNER JOIN scustom
ON scustom~id = sbook~customid
WHERE scustom~name = 'Horst Rahn'
AND spfli~carrid LIKE 'A%'.
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-connid, ls_data-fldate, ls_data-name.
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_spfli TYPE HASHED TABLE OF spfli WITH UNIQUE KEY carrid connid,
lt_sbook TYPE SORTED TABLE OF sbook WITH NON-UNIQUE KEY customid,
lt_scustom TYPE SORTED TABLE OF scustom WITH NON-UNIQUE KEY name,
m1 TYPE i,
m2 TYPE i,
m3 TYPE i,
m4 TYPE i.
FIELD-SYMBOLS:
<fs_spfli> TYPE spfli,
<fs_sbook> TYPE sbook,
<fs_scustom> TYPE scustom.
* Init-Load
GET RUN TIME FIELD m1.
SELECT carrid connid
INTO CORRESPONDING FIELDS OF TABLE lt_spfli
FROM spfli.
SELECT carrid connid fldate customid
INTO CORRESPONDING FIELDS OF TABLE lt_sbook
FROM sbook.
SELECT id name
INTO CORRESPONDING FIELDS OF TABLE lt_scustom
FROM scustom.
GET RUN TIME FIELD m2.
m2 = m2 - m1.
* Join the internal tables
GET RUN TIME FIELD m3.
LOOP AT lt_scustom ASSIGNING <fs_scustom>
WHERE name = 'Horst Rahn'.
LOOP AT lt_sbook ASSIGNING <fs_sbook>
WHERE customid = <fs_scustom>-id.
LOOP AT lt_spfli ASSIGNING <fs_spfli>
WHERE carrid = <fs_sbook>-carrid
AND connid = <fs_sbook>-connid
AND carrid CP 'A*'.
ls_data-carrid = <fs_spfli>-carrid.
ls_data-connid = <fs_spfli>-connid.
ls_data-fldate = <fs_sbook>-fldate.
ls_data-name = <fs_scustom>-name.
APPEND ls_data TO lt_data.
ENDLOOP.
ENDLOOP.
ENDLOOP.
GET RUN TIME FIELD m4.
LOOP AT lt_data INTO ls_data.
WRITE: / ls_data-carrid, ls_data-connid, ls_data-fldate, ls_data-name.
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
TYPES:
BEGIN OF ts_data,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
fldate TYPE s_date,
name TYPE s_custname,
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 spfli~carrid spfli~connid sbook~fldate scustom~name
INTO (ls_data-carrid, ls_data-connid, ls_data-fldate, ls_data-name)
FROM spfli
INNER JOIN sbook
ON spfli~carrid = sbook~carrid
AND spfli~connid = sbook~connid
INNER JOIN scustom
ON scustom~id = sbook~customid
WHERE scustom~name = 'Horst Rahn'
AND spfli~carrid LIKE 'A%'.
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-connid, ls_data-fldate, ls_data-name.
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_spfli TYPE HASHED TABLE OF spfli WITH UNIQUE KEY carrid connid,
lt_sbook TYPE SORTED TABLE OF sbook WITH NON-UNIQUE KEY customid,
lt_scustom TYPE SORTED TABLE OF scustom WITH NON-UNIQUE KEY name,
m1 TYPE i,
m2 TYPE i,
m3 TYPE i,
m4 TYPE i.
FIELD-SYMBOLS:
<fs_spfli> TYPE spfli,
<fs_sbook> TYPE sbook,
<fs_scustom> TYPE scustom.
* Init-Load
GET RUN TIME FIELD m1.
SELECT carrid connid
INTO CORRESPONDING FIELDS OF TABLE lt_spfli
FROM spfli.
SELECT carrid connid fldate customid
INTO CORRESPONDING FIELDS OF TABLE lt_sbook
FROM sbook.
SELECT id name
INTO CORRESPONDING FIELDS OF TABLE lt_scustom
FROM scustom.
GET RUN TIME FIELD m2.
m2 = m2 - m1.
* Join the internal tables
GET RUN TIME FIELD m3.
LOOP AT lt_scustom ASSIGNING <fs_scustom>
WHERE name = 'Horst Rahn'.
LOOP AT lt_sbook ASSIGNING <fs_sbook>
WHERE customid = <fs_scustom>-id.
LOOP AT lt_spfli ASSIGNING <fs_spfli>
WHERE carrid = <fs_sbook>-carrid
AND connid = <fs_sbook>-connid
AND carrid CP 'A*'.
ls_data-carrid = <fs_spfli>-carrid.
ls_data-connid = <fs_spfli>-connid.
ls_data-fldate = <fs_sbook>-fldate.
ls_data-name = <fs_scustom>-name.
APPEND ls_data TO lt_data.
ENDLOOP.
ENDLOOP.
ENDLOOP.
GET RUN TIME FIELD m4.
LOOP AT lt_data INTO ls_data.
WRITE: / ls_data-carrid, ls_data-connid, ls_data-fldate, ls_data-name.
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 in my infodepot:
Full list of examples in my infodepot
If you have a question, have found an error or just want to contact me, please use this form.
If you have a question, have found an error or just want to contact me, please use this form.
Copyright (C) 2011 http://www.kerum.pl/infodepot/
Disclaimer: I am not affiliated or related to any division or subsidiary of SAP AG.
Trademarks or registered trademarks of any products or companies referred to on this site belong to those companies.
Anyone using the given solutions, is doing it under his/her own responsibility and at own risk.