JOIN vs FOR ALL ENTRIES vs Nested SELECT

Explanation

JOIN, FOR ALL ENTRIES and nested SELECT are common data retrieval patterns. Nested SELECT is usually the weakest for large volumes because it creates many database calls. JOIN is often best when tables have clear relationships and the database can filter and combine data efficiently. FOR ALL ENTRIES is useful when the driver data is already available in ABAP memory or when joining is not practical. On HANA, pushing filtering and joining to the database is often beneficial, but the result set must remain controlled. The right answer depends on data volume, cardinality, indexes, table relationship and readability.

Code example

* Weak pattern:* Nested SELECT creates repeated DB calls. LOOP AT lt_vbak INTO DATA(ls_vbak). SELECT vbeln, posnr, matnr FROM vbap INTO TABLE @DATA(lt_vbap_temp) WHERE vbeln = @ls_vbak-vbeln.ENDLOOP. * Better pattern when relationship is clear:* Use JOIN and push filtering to database. SELECT a~vbeln, a~erdat, b~posnr, b~matnr FROM vbak AS a INNER JOIN vbap AS b ON b~vbeln = a~vbeln INTO TABLE @DATA(lt_sales) WHERE a~vkorg = @p_vkorg AND a~erdat IN @s_erdat. * Key point:* Fetch the required combined result once instead of repeated child selects.

Real project scenario

A report first selected BKPF, then selected BSEG inside a loop. Replacing it with controlled JOIN/CDS filtering on company code and posting date reduced DB calls massively and improved runtime.

Common mistakes

- Using nested SELECT for large data. - Creating JOIN without selective WHERE conditions. - Fetching too many columns. - Using FOR ALL ENTRIES without deduplicated driver.

Best practices

- Avoid nested SELECT for large data. - Use JOIN for clear DB relationships. - Use FAE when driver data is already prepared. - Control result size with WHERE filters.

Interview angle

A senior answer should not blindly say JOIN is always better. It should explain data volume, relationship and selectivity.