ABAP Developmentintermediate

Open SQL Part 5: JOINs — When to Use and When to Avoid

Key takeaway

Combine tables in one statement: INNER JOIN vs LEFT OUTER JOIN, ON conditions with keys, aliases and the selector, joins on buffered tables, and when JOIN, FOR ALL ENTRIES or a CDS view is the better choice.

Explanation

📌 KEY POINTS
✅ INNER JOIN returns only rows with a match on both sides; LEFT OUTER JOIN keeps all left rows.
✅ Join on key fields (ideally the full key of the right table) to avoid row multiplication.
✅ Use aliases and the ~ column selector: f~carrid, b~bookid.
⚠️ Joins bypass the table buffer — for small buffered master tables, a separate read can be better.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 3 combined tables via FOR ALL ENTRIES. A JOIN combines them in one database statement — usually faster, especially on HANA.

━━━━━━━━━━━━━━━━━━━━
▶ 1. INNER JOIN
SELECT f~carrid f~connid f~fldate b~bookid b~customid
FROM sflight AS f INNER JOIN sbook AS b
ON b~carrid = f~carrid AND b~connid = f~connid AND b~fldate = f~fldate
INTO TABLE gt_result
WHERE f~carrid = p_carr.
Flights without bookings are not returned.

━━━━━━━━━━━━━━━━━━━━
▶ 2. LEFT OUTER JOIN
Keep all flights, with booking fields empty where no booking exists:
FROM sflight AS f LEFT OUTER JOIN sbook AS b ON …
Classic restrictions (older releases): WHERE conditions on the right table are not allowed — put them into ON. Newer releases relax this, but ON vs WHERE still changes the meaning:

Condition in ON → filters the right side, left rows stay.
Condition in WHERE on the right table → removes left rows without match (turns it into an inner join).

━━━━━━━━━━━━━━━━━━━━
▶ 3. ROW MULTIPLICATION
One flight with 200 bookings produces 200 result rows. That is correct for booking lists but wrong if you then sum flight-level fields (e.g. seatsmax) — sum at the right level or aggregate in SQL (Part 6).

━━━━━━━━━━━━━━━━━━━━
▶ 4. HOW MANY TABLES?
Joins of 2–4 tables on key fields are normal. Very large joins over many tables become hard to read and to optimize — model them as CDS views (see RAP/CDS module).

━━━━━━━━━━━━━━━━━━━━
▶ 5. JOIN VS FOR ALL ENTRIES VS SEPARATE READS

Both tables in DB, related by keys → JOIN (one round trip).
Driver created in ABAP → FOR ALL ENTRIES.
Small buffered customizing/master table (e.g. SCARR) → separate SELECT (buffer) + hashed table can be faster than joining.
Reused data model → CDS view.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: inner join flights–bookings, left outer join showing flights without bookings, and a 3-table join with airline names.

✅ CHECKPOINT: you choose and write joins that return the rows you expect.

Code example

ABAP Code

REPORT zosql_part5. TYPES: BEGIN OF ty_row,         carrid   TYPE s_carr_id,         connid   TYPE s_conn_id,         fldate   TYPE s_date,         bookid   TYPE s_book_id,         customid TYPE s_customer,       END OF ty_row,       BEGIN OF ty_all,         carrid TYPE s_carr_id,         connid TYPE s_conn_id,         fldate TYPE s_date,         bookid TYPE s_book_id,       END OF ty_all,       BEGIN OF ty_named,         carrname TYPE s_carrname,         connid   TYPE s_conn_id,         fldate   TYPE s_date,         seatsocc TYPE s_seatsocc,       END OF ty_named. DATA: gt_result TYPE STANDARD TABLE OF ty_row,      gt_all    TYPE STANDARD TABLE OF ty_all,      gs_all    TYPE ty_all,      gt_named  TYPE STANDARD TABLE OF ty_named. PARAMETERS p_carr TYPE s_carr_id DEFAULT 'LH'. START-OF-SELECTION.* ===== 1. INNER JOIN: only flights WITH bookings =====  SELECT f~carrid f~connid f~fldate b~bookid b~customid    FROM sflight AS f    INNER JOIN sbook AS b      ON  b~carrid = f~carrid      AND b~connid = f~connid      AND b~fldate = f~fldate    INTO TABLE gt_result    UP TO 100 ROWS    WHERE f~carrid = p_carr      AND b~cancelled = space. * ===== 2. LEFT OUTER JOIN: ALL flights, bookings where they exist =====*    right-table filter in ON (keeps flights without matching bookings)  SELECT f~carrid f~connid f~fldate b~bookid    FROM sflight AS f    LEFT OUTER JOIN sbook AS b      ON  b~carrid = f~carrid      AND b~connid = f~connid      AND b~fldate = f~fldate      AND b~cancelled = space    INTO TABLE gt_all    UP TO 100 ROWS    WHERE f~carrid = p_carr.   LOOP AT gt_all INTO gs_all WHERE bookid IS INITIAL.    WRITE: / 'no bookings:', gs_all-connid, gs_all-fldate.  ENDLOOP. * ===== 3. Three tables: add airline name =====  SELECT c~carrname f~connid f~fldate f~seatsocc    FROM scarr AS c    INNER JOIN sflight AS f ON f~carrid = c~carrid    INTO TABLE gt_named    UP TO 20 ROWS    WHERE c~carrid = p_carr. * NOTE: SCARR is small and buffered - reading it separately into a* HASHED table and READ TABLE may be faster than joining it everywhere.* Note: in older releases, constants in the ON condition of a LEFT OUTER JOIN* may need strict syntax; if the syntax check complains, filter after the SELECT.

Real project scenario

A report of open orders per customer joined order headers with items and then summed the header's freight charge. Customers with 10 items showed 10 times the freight. The join itself was correct — the aggregation happened at the wrong level.
The team summed item values from the joined result and read header freight separately per order (or aggregated items per order in SQL first), and added a test case with multi-item orders.

Common mistakes

Right-table conditions in WHERE of a LEFT OUTER JOIN (unintended inner join).
Joining on non-key fields and multiplying rows.
Summing header fields after joining items.
Very large multi-table joins instead of a CDS model.
Joining small buffered tables unnecessarily.

Best practices

Join on key fields with aliases and ~.
Put right-table filters of outer joins into ON.
Aggregate at the correct level.
Use CDS views for reusable multi-table models.
Read buffered master tables separately when appropriate.

Interview angle

Questions: "INNER vs LEFT OUTER JOIN?", "Where do you put conditions on the right table of an outer join?", "JOIN vs FOR ALL ENTRIES?"
Strong answer: inner joins return only matching rows, left outer joins keep all left rows with initial right fields; right-table filters belong into ON, otherwise the outer join behaves like an inner join; joins bypass the buffer; use JOINs for DB-resident related tables, FAE for ABAP-built drivers, CDS for reusable models; beware row multiplication when aggregating.

Practice this lesson

Quick check

1. In a LEFT OUTER JOIN, where should a filter on the right table go to keep all left rows?

2. A join of order items and partners doubles item amounts in totals. What is the cause?

3. Which join returns only matching rows on both sides?

Interview questions

1. A SELECT statement returns duplicate rows due to a JOIN with a table that has multiple matches. How would you identify this issue and resolve it?

Answer it in your own words first, then compare.

2. A sales report joins header, items and partners, and totals are too high. What is the likely cause?

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.