ABAP Developmentintermediate

Performance Part 7: JOIN vs FOR ALL ENTRIES vs Nested SELECT

Key takeaway

Compare data retrieval patterns: nested SELECT (worst), FOR ALL ENTRIES (driver from ABAP), INNER and LEFT OUTER JOINs (database does the work), when each fits, join conditions on keys, number of joined tables, buffered tables in joins, and views/CDS for reuse.

Explanation

📌 KEY POINTS
✅ Nested SELECT (SELECT…ENDSELECT with inner SELECT) = one round trip per outer row — avoid.
✅ JOIN lets the database combine tables in one statement — usually best when all data is in the database and join conditions use keys/indexes.
✅ FOR ALL ENTRIES fits when the driver comes from ABAP (file, previous processing, a BAPI result) or when a join would multiply rows unnecessarily.
⚠️ JOINs bypass table buffering; joining a buffered customizing table can be slower than reading it separately from the buffer.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 4–5 replaced loops with FAE. Often a JOIN is even better — this lesson helps choose.

━━━━━━━━━━━━━━━━━━━━
▶ 1. NESTED SELECT
SELECT … ENDSELECT with SELECT inside → many round trips; only acceptable for tiny volumes.

━━━━━━━━━━━━━━━━━━━━
▶ 2. INNER JOIN

Header + item: VBAK INNER JOIN VBAP ON vbap~vbeln = vbak~vbeln, WHERE on header fields (dates, order type).
Join on key fields; selective WHERE; field list with aliases (~).
Keep the number of tables reasonable (2–4) for readability and optimizer stability.

━━━━━━━━━━━━━━━━━━━━
▶ 3. LEFT OUTER JOIN
Keeps left rows without matches (e.g. orders without deliveries); fields of the right table are initial; restrictions on right-table fields in WHERE effectively make it inner in classic Open SQL — put them in the ON condition.

━━━━━━━━━━━━━━━━━━━━
▶ 4. FOR ALL ENTRIES

Driver from ABAP or a different source; second step after complex filtering in ABAP.
Needs empty check and key fields (Part 5).

━━━━━━━━━━━━━━━━━━━━
▶ 5. BUFFERED TABLES

JOIN reads from DB, bypassing buffers; for small customizing tables (texts, descriptions) read them separately into a hashed table from the buffer.

━━━━━━━━━━━━━━━━━━━━
▶ 6. REUSE
Database views (SE11) or CDS views encapsulate proven joins for reuse (Part 8).

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Rewrite a nested SELECT to an INNER JOIN.
Compare ST05 executions and runtime with an FAE version.
Try a LEFT OUTER JOIN to list orders without deliveries.

✅ CHECKPOINT: you choose between JOIN, FAE and separate reads based on data source and volume.

Code example

ABAP Code

REPORT zperf_join. TYPES: BEGIN OF ty_row,         vbeln TYPE vbeln_va,         erdat TYPE erdat,         kunnr TYPE kunnr,         posnr TYPE posnr_va,         matnr TYPE matnr,         netwr TYPE netwr_ap,       END OF ty_row. DATA: lt_rows TYPE STANDARD TABLE OF ty_row,      ls_row  TYPE ty_row. SELECT-OPTIONS: s_erdat FOR ls_row-erdat,                s_kunnr FOR ls_row-kunnr. START-OF-SELECTION.* ===== BAD: nested SELECT =====*  SELECT vbeln erdat kunnr FROM vbak INTO ... WHERE erdat IN s_erdat.*    SELECT posnr matnr netwr FROM vbap INTO ... WHERE vbeln = ...*    ENDSELECT.*  ENDSELECT. * ===== GOOD: one INNER JOIN on the key =====  SELECT a~vbeln a~erdat a~kunnr b~posnr b~matnr b~netwr    INTO TABLE lt_rows    FROM vbak AS a    INNER JOIN vbap AS b ON b~vbeln = a~vbeln    WHERE a~erdat IN s_erdat      AND a~kunnr IN s_kunnr.   LOOP AT lt_rows INTO ls_row.    WRITE: / ls_row-vbeln, ls_row-posnr, ls_row-matnr, ls_row-netwr.  ENDLOOP. * ===== LEFT OUTER JOIN: orders without deliveries (condition on right table in ON) =====*  SELECT a~vbeln b~vbeln AS deliv*    FROM vbak AS a*    LEFT OUTER JOIN vbfa AS b ON b~vbelv = a~vbeln AND b~vbtyp_n = 'J'*    INTO TABLE ... WHERE a~erdat IN s_erdat.*  -> rows with initial DELIV have no delivery

Real project scenario

A report used a nested SELECT over VBAK and VBAP for a quarter and ran 35 minutes. An FAE rewrite brought it to 6 minutes; an INNER JOIN with the date filter on VBAK brought it to 50 seconds because the database could use the date index and join in one pass.
For material descriptions (MAKT) the team kept a separate read into a hashed table, since joining it multiplied rows per language.

Common mistakes

Nested SELECT…ENDSELECT.
Joins without key conditions.
Right-table conditions in WHERE of LEFT OUTER JOIN.
Joining buffered customizing tables.
Too many tables in one join.

Best practices

JOIN when data is all in DB.
FAE when driver comes from ABAP.
Key-based join conditions, selective WHERE.
Read buffered tables separately.
Views/CDS for reusable joins.

Interview angle

Questions: "JOIN vs FOR ALL ENTRIES?", "Why avoid nested SELECT?", "LEFT OUTER JOIN pitfalls?"
Strong answer: nested SELECT = round trip per row; JOIN lets DB combine data in one statement, best with key joins and selective WHERE; FAE when driver is in ABAP; left outer join conditions on right table in ON; joins bypass buffering; views/CDS for reuse.

Practice this lesson

Quick check

1. Headers and items are both in the database and filtered by header date. Usually the most efficient read?

2. When is FOR ALL ENTRIES usually preferred over a JOIN?

3. In a LEFT OUTER JOIN, where should conditions on the right table usually go?

Interview questions

1. How do you decide between JOIN, FOR ALL ENTRIES and CDS for performance?

Answer it in your own words first, then compare.

2. A report selects order headers and then selects items inside the header loop. What alternatives exist, and how do you choose?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.