ABAP Cloud Course

Appearance

Lesson 4 Companion — CDS Joins ​

This companion expands Lesson 4 — CDS Joins.

It contains:

  • relational-modeling context;
  • precise join semantics;
  • complete route-model reference code;
  • Reader and Runner references;
  • row-count and key reasoning;
  • practice solutions;
  • troubleshooting;
  • current SAP documentation references.

How to use this companion

Build the main lesson and practice models first where possible. Use this file to compare your design with a complete reference implementation and to review the reasoning behind join type, ON conditions, aliases, and keys.

Related Lesson 4 files:

  1. Lesson 4 — CDS Joins
  2. Lesson 4 Practice — Booking Details with CDS Joins

1. Why joins come before associations ​

Current SAP CDS provides both:

text
join expressions
associations

SAP describes associations as the preferred reusable relationship-modeling mechanism and notes that associations are instantiated as joins when needed.

That is why explicit joins are worth understanding first.

A learner should understand:

text
what rows are matched
what ON means
what INNER means
what LEFT OUTER means
how result rows can multiply

before adding the abstraction of cardinality and path expressions.

The course sequence is therefore:

text
Lesson 4
explicit relational combination

        ↓

Lesson 5
reusable relationship modeling

2. Verified /DMO/ structures ​

SAP's ABAP Flight Reference Scenario documents:

/DMO/CONNECTION ​

text
carrier_id          key
connection_id       key
airport_from_id
airport_to_id
departure_time
arrival_time
distance
distance_unit

/DMO/CARRIER ​

text
carrier_id          key
name
currency_code

/DMO/AIRPORT ​

text
airport_id          key
name
city
country

The guided joins therefore match:

text
Connection.carrier_id
    =
Carrier.carrier_id

Connection.airport_from_id
    =
OriginAirport.airport_id

Connection.airport_to_id
    =
DestinationAirport.airport_id

3. Current CDS join scope ​

Current ABAP CDS relationship modeling supports join expressions including:

text
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
CROSS JOIN

This lesson focuses on:

text
INNER JOIN
LEFT OUTER JOIN

because they establish the most important beginner distinction:

text
require related data
vs
preserve the left record if related data is missing

4. INNER JOIN semantics ​

For an inner join:

text
left row + matching right row → returned
left row + no right row       → removed

An inner join can therefore:

text
reduce rows
preserve rows
multiply rows

depending on the data and condition.

That last case matters.

5. LEFT OUTER JOIN semantics ​

SAP defines a left outer join as returning:

  • rows that satisfy the join condition; and
  • an additional row for each unmatched row from the left source.

For unmatched rows, right-side values are null at SQL level.

So:

text
LEFT OUTER JOIN

is not simply a "safer INNER JOIN."

It expresses a different model.

6. Why join conditions are more important than syntax ​

This activates:

text
source.field = target.field

only if the field references and types are legal.

Activation does not prove that the condition represents the intended relationship.

A correct join requires understanding:

text
source identity
foreign/reference fields
target identity
expected cardinality

7. Row multiplication ​

Suppose a target key is:

text
A + B

but the join uses only:

text
A

If the target contains:

text
A 1
A 2
A 3

then one source row with A can become three joined rows.

This affects:

text
counts
aggregations
apparent duplicates
service results
key assumptions

If duplication is unexpected, the first response should be:

text
inspect the relationship
inspect the target key
inspect the ON condition

not:

text
add DISTINCT

8. Why the Route CDS key stays the Connection key ​

/DMO/CONNECTION is identified by:

text
carrier_id + connection_id

The carrier and airports enrich the route.

They do not change what the row represents.

Therefore:

abap
key Connection.carrier_id    as CarrierId,
key Connection.connection_id as ConnectionId,

is appropriate.

Descriptions are not identifiers:

text
CarrierName
OriginCity
DestinationCity

9. Role-based aliases ​

The same table appears twice:

abap
/dmo/airport as OriginAirport
/dmo/airport as DestinationAirport

This is a particularly useful example of why aliases should describe roles.

Compare:

text
Airport1
Airport2

with:

text
OriginAirport
DestinationAirport

The second model communicates business meaning directly.

Lesson 5 can preserve this pattern with:

text
_OriginAirport
_DestinationAirport

10. Complete Route CDS reference ​

abap
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Route'

define view entity ZI_Route_###
  as select from /dmo/connection as Connection

    inner join /dmo/carrier as Carrier
      on Connection.carrier_id = Carrier.carrier_id

    inner join /dmo/airport as OriginAirport
      on Connection.airport_from_id = OriginAirport.airport_id

    inner join /dmo/airport as DestinationAirport
      on Connection.airport_to_id = DestinationAirport.airport_id

{
  key Connection.carrier_id       as CarrierId,
  key Connection.connection_id    as ConnectionId,

      Carrier.name                as CarrierName,

      Connection.airport_from_id  as OriginAirportId,
      OriginAirport.name          as OriginAirportName,
      OriginAirport.city          as OriginCity,
      OriginAirport.country       as OriginCountry,

      Connection.airport_to_id    as DestinationAirportId,
      DestinationAirport.name     as DestinationAirportName,
      DestinationAirport.city     as DestinationCity,
      DestinationAirport.country  as DestinationCountry,

      Connection.departure_time   as DepartureTime,
      Connection.arrival_time     as ArrivalTime,
      Connection.distance         as Distance,
      Connection.distance_unit    as DistanceUnit
}

11. Why no distance semantics yet ​

The model contains:

text
Distance
DistanceUnit

but Lesson 4 does not yet connect them with semantic annotations.

That belongs in Lesson 6.

The course separation is:

text
Lesson 4 → combine sources
Lesson 5 → model reusable relationships
Lesson 6 → add semantic meaning/annotations

12. SQL Console reference ​

abap
SELECT FROM zi_route_###
  FIELDS CarrierId,
         ConnectionId,
         CarrierName,
         OriginAirportId,
         OriginCity,
         DestinationAirportId,
         DestinationCity
  ORDER BY CarrierId,
           ConnectionId

Possible filter:

abap
SELECT FROM zi_route_###
  FIELDS CarrierId,
         ConnectionId,
         CarrierName,
         OriginCity,
         OriginCountry,
         DestinationCity,
         DestinationCountry
  WHERE OriginCountry = 'DE'
  ORDER BY CarrierId,
           ConnectionId

Use values that exist in the current tenant.

13. Route Reader reference ​

abap
CLASS zcl_route_reader_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.

    TYPES tt_routes TYPE STANDARD TABLE OF zi_route_###
      WITH EMPTY KEY.

    METHODS get_all
      RETURNING VALUE(routes) TYPE tt_routes.

    METHODS get_from_airport
      IMPORTING
        airport_id TYPE /dmo/airport-airport_id
      RETURNING
        VALUE(routes) TYPE tt_routes.

ENDCLASS.


CLASS zcl_route_reader_### IMPLEMENTATION.

  METHOD get_all.

    SELECT FROM zi_route_###
      FIELDS *
      ORDER BY CarrierId,
               ConnectionId
      INTO TABLE @routes.

  ENDMETHOD.


  METHOD get_from_airport.

    SELECT FROM zi_route_###
      FIELDS *
      WHERE OriginAirportId = @airport_id
      ORDER BY CarrierId,
               ConnectionId
      INTO TABLE @routes.

  ENDMETHOD.

ENDCLASS.

14. Route Runner reference ​

abap
CLASS zcl_cds_l04_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.


CLASS zcl_cds_l04_### IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

    DATA(reader) = NEW zcl_route_reader_###( ).

    DATA(routes) = reader->get_all( ).

    LOOP AT routes INTO DATA(route).

      out->write(
        |{ route-CarrierName }: | &&
        |{ route-OriginCity } ({ route-OriginAirportId }) -> | &&
        |{ route-DestinationCity } ({ route-DestinationAirportId })|
      ).

    ENDLOOP.

  ENDMETHOD.

ENDCLASS.

15. Why the Reader still exists ​

The CDS model owns:

text
how route data is combined

The Reader owns:

text
how application code retrieves route data

For example:

text
GET_ALL
GET_FROM_AIRPORT

The Runner still owns:

text
execution
display

That keeps the responsibilities introduced in Lesson 1 consistent.

16. Why GET_FROM_AIRPORT uses ABAP SQL filtering ​

The requested airport is a runtime value.

This is naturally expressed as:

abap
WHERE OriginAirportId = @airport_id

A separate CDS entity for every airport would not make sense.

This reinforces:

text
reusable model logic → CDS
runtime request filter → consuming query

17. Inner vs left outer with demo data ​

The SAP flight demo data is designed to be coherent.

Therefore, converting a key-based join from:

text
INNER JOIN

to:

text
LEFT OUTER JOIN

may not visibly change the result.

That does not change the semantics.

It means the current data has matching target rows.

18. Null handling boundary ​

A left outer join can introduce null-valued right-side fields.

Nulls affect:

text
filters
CASE
calculations
ABAP consumption

Lesson 4 should not become a full null-semantics lesson.

The useful boundary is:

text
LEFT OUTER keeps the left row

More advanced null-aware modeling can be introduced when needed.

19. Why right outer and cross join are not central here ​

They are part of CDS join functionality.

But the course should not become a catalogue of every syntax variant.

Students need depth on:

text
INNER JOIN
LEFT OUTER JOIN
ON conditions
target keys
row multiplication

before moving to associations.

20. SAP's current association direction ​

Current SAP CDS guidance describes associations as reusable relationship definitions that are instantiated as joins when target data is needed.

This makes them a better abstraction for relationships that should be reusable and navigable across a CDS model.

Explicit joins remain valid and important.

The distinction is:

text
join
  → fixed combination in this model

association
  → reusable relationship

21. Practice source model ​

The practice uses:

text
/DMO/BOOKING
/DMO/CUSTOMER
/DMO/CARRIER

SAP's flight scenario documents /DMO/BOOKING with:

text
travel_id          key
booking_id         key
booking_date
customer_id
carrier_id
connection_id
flight_date
flight_price
currency_code

Customer data includes:

text
customer_id
first_name
last_name

Carrier data includes:

text
carrier_id
name
currency_code

The practice therefore reinforces two to-one-style joins and preserves the booking key.

22. Practice CDS reference ​

abap
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Booking Detail'

define view entity ZI_Booking_Detail_###
  as select from /dmo/booking as Booking

    inner join /dmo/customer as Customer
      on Booking.customer_id = Customer.customer_id

    inner join /dmo/carrier as Carrier
      on Booking.carrier_id = Carrier.carrier_id

{
  key Booking.travel_id       as TravelId,
  key Booking.booking_id      as BookingId,

      Booking.booking_date    as BookingDate,

      Booking.customer_id     as CustomerId,
      Customer.first_name     as CustomerFirstName,
      Customer.last_name      as CustomerLastName,

      concat_with_space(
        Customer.first_name,
        Customer.last_name,
        1
      )                       as CustomerName,

      Booking.carrier_id      as CarrierId,
      Carrier.name            as CarrierName,

      Booking.connection_id   as ConnectionId,
      Booking.flight_date     as FlightDate,
      Booking.flight_price    as FlightPrice,
      Booking.currency_code   as CurrencyCode
}

This deliberately reuses a Lesson 3 expression while keeping Lesson 4 focused on joins.

23. Practice SQL Console reference ​

abap
SELECT FROM zi_booking_detail_###
  FIELDS TravelId,
         BookingId,
         CustomerName,
         CarrierName,
         ConnectionId,
         FlightDate,
         FlightPrice,
         CurrencyCode
  ORDER BY TravelId,
           BookingId

Carrier filter:

abap
SELECT FROM zi_booking_detail_###
  FIELDS TravelId,
         BookingId,
         CustomerName,
         CarrierId,
         CarrierName
  WHERE CarrierId = 'LH'
  ORDER BY TravelId,
           BookingId

Use a carrier ID present in Data Preview.

24. Practice Reader reference ​

abap
CLASS zcl_booking_reader_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.

    TYPES tt_bookings
      TYPE STANDARD TABLE OF zi_booking_detail_###
      WITH EMPTY KEY.

    METHODS get_all
      RETURNING VALUE(bookings) TYPE tt_bookings.

    METHODS get_by_carrier
      IMPORTING
        carrier_id TYPE /dmo/carrier-carrier_id
      RETURNING
        VALUE(bookings) TYPE tt_bookings.

ENDCLASS.


CLASS zcl_booking_reader_### IMPLEMENTATION.

  METHOD get_all.

    SELECT FROM zi_booking_detail_###
      FIELDS *
      ORDER BY TravelId,
               BookingId
      INTO TABLE @bookings.

  ENDMETHOD.


  METHOD get_by_carrier.

    SELECT FROM zi_booking_detail_###
      FIELDS *
      WHERE CarrierId = @carrier_id
      ORDER BY TravelId,
               BookingId
      INTO TABLE @bookings.

  ENDMETHOD.

ENDCLASS.

25. Practice Runner reference ​

abap
CLASS zcl_cds_l04_practice_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.


CLASS zcl_cds_l04_practice_### IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

    DATA(reader) = NEW zcl_booking_reader_###( ).

    DATA(bookings) = reader->get_all( ).

    LOOP AT bookings INTO DATA(booking).

      out->write(
        |{ booking-TravelId }/{ booking-BookingId }: | &&
        |{ booking-CustomerName } - { booking-CarrierName }|
      ).

    ENDLOOP.

  ENDMETHOD.

ENDCLASS.

26. Practice reasoning answers ​

Why are TravelId and BookingId the keys? ​

They identify the booking in /DMO/BOOKING. Customer and carrier records enrich it.

Why join Customer by customer_id? ​

It identifies the customer referenced by the booking.

Why join Carrier by carrier_id? ​

It identifies the carrier referenced by the booking.

Why calculate CustomerName in CDS? ​

It is a reusable representation derived from the joined customer fields and reinforces Lesson 3's expression skills.

What happens if an INNER JOIN has no target? ​

The booking is removed from the joined result.

What would LEFT OUTER JOIN change? ​

The left-side booking can remain even without the target; right-side values are null at SQL level.

27. Optional challenge reference: add Connection ​

If the practice adds /DMO/CONNECTION, the relationship must use the complete connection key:

abap
inner join /dmo/connection as Connection
  on  Booking.carrier_id    = Connection.carrier_id
  and Booking.connection_id = Connection.connection_id

Joining only:

abap
Booking.connection_id = Connection.connection_id

is unsafe because /DMO/CONNECTION is identified by:

text
carrier_id + connection_id

This challenge is a direct test of complete-key reasoning.

28. Troubleshooting ​

name is ambiguous ​

Qualify it:

abap
Carrier.name
OriginAirport.name
DestinationAirport.name

Origin and destination are swapped ​

Check both:

text
ON condition
element alias

Correct roles:

abap
airport_from_id → OriginAirport.airport_id
airport_to_id   → DestinationAirport.airport_id

Duplicate routes appear ​

Investigate:

text
target key
ON condition
whether one source row can match several target rows

Do not immediately add DISTINCT.

Rows disappear ​

If using INNER JOIN, inspect whether the removed source rows lack a matching joined target.

LEFT OUTER and INNER show the same count ​

The demo references may all be valid.

The semantics are still different.

29. Self-review checklist ​

A strong Lesson 4 solution should demonstrate:

  • correct source/target relationships;
  • meaningful aliases;
  • complete-key reasoning;
  • correct route or booking keys;
  • no unexplained row multiplication;
  • correct INNER vs LEFT OUTER explanation;
  • joins centralized in CDS;
  • Reader consuming CDS rather than reproducing joins;
  • readiness for association cardinality.

30. Current SAP references ​