FOR ALL ENTRIES vs JOIN vs Subquery on HANA

If you've been writing ABAP for more than a few years, you've probably had the FOR ALL ENTRIES vs JOIN debate with a colleague — or at least in your own head. On older databases it was almost a religion. On SAP HANA, the rules have shifted, but not disappeared. Let me share what actually happens under the hood in 2026, with real execution plan observations and practical guidance you can apply tomorrow morning.

I'm going to benchmark three approaches for the same logical problem: FOR ALL ENTRIES IN, an explicit JOIN, and a correlated/uncorrelated subquery. We'll look at when each one wins and — more importantly — when each one silently destroys your runtime.

Why the FOR ALL ENTRIES vs JOIN HANA Debate Still Matters

You might think: "HANA is in-memory, it's fast, why do I care?" I hear this constantly. The truth is that pushing the wrong query shape to HANA can still generate 10x more I/O than necessary, blow through your working memory limits, and produce execution plans that the SQL optimizer cannot fully parallelize. The hardware is forgiving — until it isn't.

The three patterns we're comparing:

• FOR ALL ENTRIES IN (FAE) — classic ABAP construct, translates to a database-level IN clause or a series of OR conditions depending on volume
• JOIN — standard SQL JOIN pushed down to HANA directly
• Subquery — correlated or uncorrelated SELECT inside a WHERE clause

Test Setup and Methodology

All tests were run on an S/4HANA 2023 FPS02 system, HANA 2.0 SPS07, using ABAP 7.58. I used ST05 (SQL Trace) and the HANA Plan Visualizer (transaction DBACOCKPIT → SQL Analyzer) to capture execution plans. The tables involved are typical FI-document scenarios: BKPF (~4M rows), BSEG (~28M rows), filtered by a dynamic set of company codes and fiscal years loaded from a preceding selection.

For a deeper look at the tooling side, see my earlier post on using ST05, SAT, and SQLM to find bottlenecks. That article covers the capture workflow I relied on for these benchmarks.

Pattern 1: FOR ALL ENTRIES IN

DATA: lt_bkpf TYPE TABLE OF bkpf,
      lt_bseg TYPE TABLE OF bseg.

" Step 1 – fetch header data
SELECT bukrs, belnr, gjahr, bldat
  FROM bkpf
  INTO TABLE @lt_bkpf
  WHERE bukrs IN @lr_bukrs
    AND gjahr = @lv_gjahr.

" Step 2 – fetch line items using FAE
IF lt_bkpf IS NOT INITIAL.
  SELECT bukrs, belnr, gjahr, buzei, dmbtr, hkont
    FROM bseg
    INTO TABLE @lt_bseg
    FOR ALL ENTRIES IN @lt_bkpf
    WHERE bukrs = @lt_bkpf-bukrs
      AND belnr = @lt_bkpf-belnr
      AND gjahr = @lt_bkpf-gjahr.
ENDIF.

What HANA actually does: The ABAP kernel translates FAE into an IN ((...),(...),...) construct when the driving table is small-to-medium. Once you exceed roughly 5,000–10,000 entries (the exact threshold varies by kernel patch level), it may switch to a temporary table join or split the statement into multiple round-trips. On HANA this is significantly less painful than on Oracle or DB2 — but the two-round-trip nature is still a network overhead you're paying for.

Measured result (100k BKPF rows as driver):
DB time: ~1.4 seconds | Rows transferred to AS ABAP: 28M filtered down to 1.1M

Key gotcha: If you forget the IS NOT INITIAL check, FAE with an empty internal table reads the entire target table. No filter. I've seen this take a production system to its knees on a Monday morning.

Pattern 2: The JOIN Approach

SELECT h~bukrs, h~belnr, h~gjahr, h~bldat,
       i~buzei,  i~dmbtr,  i~hkont
  FROM bkpf AS h
  INNER JOIN bseg AS i
    ON  i~bukrs = h~bukrs
    AND i~belnr = h~belnr
    AND i~gjahr = h~gjahr
  INTO TABLE @DATA(lt_result)
  WHERE h~bukrs IN @lr_bukrs
    AND h~gjahr  = @lv_gjahr.

What HANA actually does: This becomes a single SQL statement. HANA's column store engine applies the filter on BKPF first, then performs a hash join or merge join against BSEG — entirely inside the database. No intermediate internal table is materialized on the application server. The execution plan is fully parallelized across HANA worker threads.

Measured result (same dataset):
DB time: ~0.38 seconds | Rows transferred: exactly what you select

That's a 3.7x speedup over FAE for this data volume. The difference grows as row counts increase, because JOIN keeps everything inside HANA's parallel engine. This is the core argument for pushing logic to the database — something I cover in detail in the post on ABAP SQL optimization and HANA SELECT patterns.

When JOIN can lose: If you have a very wide result set and you actually need the two tables separately in memory for different processing purposes, forcing a JOIN means you're duplicating header data across every line item. That's sometimes a memory trade-off not worth making.

Pattern 3: Subqueries

Uncorrelated Subquery (IN)

SELECT bukrs, belnr, gjahr, buzei, dmbtr, hkont
  FROM bseg
  INTO TABLE @DATA(lt_bseg)
  WHERE ( bukrs, belnr, gjahr ) IN (
    SELECT bukrs, belnr, gjahr
      FROM bkpf
      WHERE bukrs IN @lr_bukrs
        AND gjahr = @lv_gjahr
  ).

The tuple-based subquery syntax became available in ABAP 7.54 and is one of the cleanest ways to express this logic. HANA treats this similarly to a semi-join internally — it doesn't necessarily materialize the full inner result set.

Measured result:
DB time: ~0.42 seconds — very close to the explicit JOIN, occasionally faster when HANA chooses a more aggressive pruning strategy on the subquery side.

Correlated Subquery (EXISTS)

SELECT bukrs, belnr, gjahr, buzei, dmbtr, hkont
  FROM bseg AS i
  INTO TABLE @DATA(lt_bseg)
  WHERE EXISTS (
    SELECT 1 FROM bkpf AS h
    WHERE h~bukrs = i~bukrs
      AND h~belnr = i~belnr
      AND h~gjahr = i~gjahr
      AND h~bukrs IN @lr_bukrs
      AND h~gjahr  = @lv_gjahr
  ).

Measured result:
DB time: ~0.55 seconds — slightly slower than the uncorrelated version. HANA can often unnest EXISTS into a semi-join, but the optimizer needs to recognize the pattern. When it does, performance is comparable. When it doesn't, you're re-evaluating the inner query per outer row, which is expensive.

Benchmark Summary Table

When FOR ALL ENTRIES Still Makes Sense

Don't throw FAE away entirely. There are real scenarios where it remains the right tool:

• The driving table comes from multiple, complex pre-processing steps. If you've already built lt_bkpf through business logic that can't be expressed in SQL, FAE is your only option to avoid re-querying what you already have.
• Very small driver sets (< 500 rows). The overhead of a JOIN on a 50-row driver is negligible, and FAE keeps your code readable and obvious.
• You need both result sets independently in memory. Sometimes you legitimately process headers and items through separate logic paths. Two queries is architecturally cleaner in that case.
• Legacy code compatibility. When refactoring, changing FAE to JOIN can alter result semantics (duplicates, NULL handling). Be deliberate.

CDS Views and the Hidden JOIN

Here's something worth calling out: when you consume a CDS view with an association or a built-in JOIN, you're already using the JOIN pattern — just at a higher abstraction level. The HANA query that gets generated from a CDS view with associations resolved is structurally equivalent to what we wrote manually above. This is one reason well-designed CDS views often outperform equivalent ABAP FAE code without any explicit tuning effort.

If you're building CDS views and want to understand the performance implications of how you wire associations, take a look at common CDS performance antipatterns and how associations and joins behave in CDS. The way you expose associations in consumption views has a direct impact on the JOIN strategy HANA picks.

Practical Decision Framework

Here's my personal decision tree when I hit this choice in code reviews or architecture sessions:

1. Can the filter logic be fully expressed in SQL? → Yes → Use JOIN or subquery. No → FAE or CTE approach.
2. Do you need both datasets independently in ABAP memory? → Yes → Two separate SELECTs (FAE or filtered). No → JOIN is almost always better.
3. Is the driver table > 5,000 rows? → Yes → Strong preference for JOIN to avoid FAE batching overhead.
4. Are you writing a CDS view? → Model the join at CDS level and let HANA optimize the full query plan end-to-end.
5. Unsure? → Capture the execution plan in ST05 or HANA Plan Visualizer and look at the actual scan/join costs. Numbers don't lie.

Performance profiling isn't guesswork — it's measurement. Tools like ST05, SAT, and SQLM give you the evidence to make these decisions with confidence rather than intuition. If you haven't built that habit yet, this walkthrough on finding bottlenecks with ST05 and SQLM is the place to start.

One More Thing: Duplicate Elimination

FAE implicitly deduplicates the driver table before sending it to the database. A JOIN does not — if your left-side table has duplicate key entries, you'll get duplicate result rows. This is a behavioral difference, not just a performance one. Always add DISTINCT or deduplicate your driver before switching from FAE to JOIN, or you'll introduce subtle data bugs that are painful to diagnose in production.

Summary

On SAP HANA in 2026, JOIN and tuple-based subqueries are almost always faster than FOR ALL ENTRIES for medium-to-large datasets, because they eliminate the application server round-trip and allow HANA's parallel column engine to optimize the full query. FAE still has its place for small drivers and cases where the driving data is the result of complex ABAP-side logic. Use the decision framework above, measure with ST05, and don't let old habits from the R/3 era drive your architecture on a modern HANA stack.