Lesson 4 — CDS Joins

Until now, each CDS model has started from one main data source.

You have already learned to:

select fields
rename fields
calculate elements
classify data with CASE
filter with WHERE
build one CDS entity on another

Real business information is rarely stored in one table.

A flight connection contains IDs such as:

carrier_id
airport_from_id
airport_to_id

A consumer usually wants more meaningful information:

carrier name
origin airport name
origin city
destination airport name
destination city

That information lives in related tables.

In this lesson, you will combine those sources with CDS joins.

---

Lesson information

Duration	About 120–150 minutes
Environment	SAP BTP ABAP Trial
Development tool	Eclipse with ABAP Development Tools (ADT)
Prerequisites	Lesson 1, Lesson 2, and Lesson 3
Primary source	/DMO/CONNECTION
Joined sources	/DMO/CARRIER, /DMO/AIRPORT
CDS entity	ZI_ROUTE_###
Reader	ZCL_ROUTE_READER_###
Runner	ZCL_CDS_L04_###

---

Learning objectives

After completing this lesson, you should be able to:

• explain why related business data is stored in several tables;
• explain what a join does;
• write an INNER JOIN in a CDS View Entity;
• define a join condition with ON;
• qualify fields with data-source aliases;
• join more than two data sources;
• join the same source twice using different aliases;
• explain why join conditions must reflect the real key relationship;
• explain how an incomplete join can duplicate result rows;
• explain the difference between INNER JOIN and LEFT OUTER JOIN;
• inspect a joined CDS model with Data Preview;
• query it with SQL Console;
• filter joined elements from ABAP SQL;
• consume the joined CDS entity through a Reader;
• explain why associations are the next modeling step.

---

Before you start

Replace ### with your own student/group suffix.

Example:

ZI_ROUTE_###         → ZI_ROUTE_017
ZCL_ROUTE_READER_### → ZCL_ROUTE_READER_017
ZCL_CDS_L04_###      → ZCL_CDS_L04_017

ABAP Cloud and /DMO/*

The /DMO/* objects used in this course are SAP demo/tutorial content from the ABAP Flight Reference Scenario.

They are suitable for these exercises.

For productive ABAP Cloud development, consume SAP functionality through APIs released for the applicable cloud-development contract.

---

1. The problem: IDs are not enough

Open:

/DMO/CONNECTION

The table contains:

carrier_id
connection_id
airport_from_id
airport_to_id
departure_time
arrival_time
distance
distance_unit

A row can tell you:

Carrier ID:      LH
Connection ID:   0400
Origin airport:  FRA
Destination:     JFK

But /DMO/CONNECTION does not contain:

carrier name
airport name
airport city
airport country

Those values belong to other data sources.

---

2. Inspect the related sources

Before writing a join, inspect the tables and their keys.

Step 2.1 — Open /DMO/CARRIER

Press:

Ctrl + Shift + A

Search for:

/DMO/CARRIER

The fields are:

carrier_id    ← key
name
currency_code

The connection's:

carrier_id

can therefore be matched with:

/DMO/CARRIER.carrier_id

---

Step 2.2 — Open /DMO/AIRPORT

Open:

/DMO/AIRPORT

The fields are:

airport_id    ← key
name
city
country

The connection has two airport references:

airport_from_id
airport_to_id

Both correspond to:

/DMO/AIRPORT.airport_id

---

3. Think relationally

The relationships look like:

/DMO/CARRIER
     ▲
     │ carrier_id
     │
/DMO/CONNECTION
     │
     ├──── airport_from_id ────► /DMO/AIRPORT
     │                            origin role
     │
     └──── airport_to_id ──────► /DMO/AIRPORT
                                  destination role

The same airport source appears in two different roles.

That will make aliases essential.

---

4. What is a join?

A join combines rows from several data sources according to a condition.

Conceptually:

Connection row
carrier_id = LH

Carrier rows
AA
LH  ← matching row
UA

        ↓

Combined result row
connection fields + carrier fields

The condition that defines which rows belong together is written after:

on

---

5. Create the Route Data Definition

Create:

ZI_ROUTE_###

Step 5.1 — Open the wizard

Right-click your package:

New
  → Other ABAP Repository Object
  → Core Data Services
  → Data Definition

Choose Next.

Step 5.2 — Enter object details

Use:

Name:        ZI_ROUTE_###
Description: Lesson 4 - Route

Choose the course transport request if required.

Choose a CDS View Entity template.

Confirm that the generated source uses:

define view entity

Choose Finish.

---

6. First join: Connection + Carrier

Start with two sources:

@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
{
  key Connection.carrier_id       as CarrierId,
  key Connection.connection_id    as ConnectionId,
      Carrier.name                as CarrierName,
      Connection.airport_from_id  as OriginAirportId,
      Connection.airport_to_id    as DestinationAirportId
}

Replace ###.

---

7. Read the join syntax

The first source is:

/dmo/connection as Connection

The second is:

inner join /dmo/carrier as Carrier

The relationship condition is:

on Connection.carrier_id = Carrier.carrier_id

Read it as:

Combine a connection row and carrier row when their carrier IDs are equal.

---

8. Why aliases matter

The aliases are:

Connection
Carrier

Both sources contain:

carrier_id

The qualified names are unambiguous:

Connection.carrier_id
Carrier.carrier_id

With several data sources, prefer:

Alias.field

so the origin of every element is clear.

---

9. Check, activate, preview

Run:

Ctrl + F2

Fix any errors.

Activate:

Ctrl + F3

Preview:

F8

You should see:

CarrierId
ConnectionId
CarrierName
OriginAirportId
DestinationAirportId

CarrierName came from the joined carrier record.

---

10. What INNER JOIN means

An inner join returns rows only when the join condition finds a match on both sides.

Conceptually:

Connection     Carrier
----------     -------
LH             LH     → returned
AA             AA     → returned
XX             --     → not returned

So:

INNER JOIN
=
matching rows only

The SAP demo data is expected to be internally consistent, so your current preview may not contain an unmatched example.

The semantics still matter.

---

11. Add the origin airport

Add a third source:

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

The alias:

OriginAirport

describes the role of this occurrence of /DMO/AIRPORT.

---

12. Expose origin information

Add:

OriginAirport.name    as OriginAirportName,
OriginAirport.city    as OriginCity,
OriginAirport.country as OriginCountry,

Run:

Ctrl + F2
Ctrl + F3
F8

Verify that:

OriginAirportId
OriginAirportName
OriginCity
OriginCountry

belong together.

---

13. Join the airport source again

A destination is also an airport.

Add another join:

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

Now the same repository source is used twice:

/DMO/AIRPORT as OriginAirport
/DMO/AIRPORT as DestinationAirport

Each occurrence has a different role and alias.

---

14. Why the two aliases must be different

This:

Airport.name

would not tell the reader whether the value means:

origin name

or:

destination name

Role-based aliases make the intent explicit:

OriginAirport.name
DestinationAirport.name

TIP

Choose aliases based on the role of a source in the model.

---

15. Complete ZI_ROUTE_###

Use:

@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
}

Activate and preview.

---

16. Verify the joined values

Choose one result row.

Record:

CarrierId:
OriginAirportId:
DestinationAirportId:

Then inspect:

/DMO/CARRIER
/DMO/AIRPORT

Confirm independently that:

CarrierName
OriginAirportName
DestinationAirportName

match the referenced IDs.

This is a useful habit whenever you create a new join.

---

17. Join conditions and target keys

The carrier condition is:

Connection.carrier_id = Carrier.carrier_id

Carrier.carrier_id is the key of /DMO/CARRIER.

The airport conditions end in:

Airport.airport_id

which is the key of /DMO/AIRPORT.

Because each target is matched using its complete key, one connection is expected to find at most:

one carrier
one origin airport
one destination airport

---

18. Why complete join conditions matter

Suppose a target is identified by:

FieldA + FieldB

and a join uses only:

FieldA

One source row can match several target rows:

Source row
   │
   ├── Target 1
   ├── Target 2
   └── Target 3

The result becomes:

3 rows

even though there was only one source row.

This is called row multiplication.

It is sometimes correct.

If you expected one related row, it indicates a relationship or join-condition problem.

WARNING

A CDS definition can activate successfully and still have a logically wrong join.

Always understand the keys and expected relationship.

---

19. Keys of the joined CDS entity

/DMO/CONNECTION is identified by:

carrier_id
connection_id

The joined records add descriptive information.

They do not change what the route represents.

Therefore:

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

remain the CDS keys.

Do not mark these as keys:

CarrierName
OriginCity
DestinationCity

---

20. Query the joined model in SQL Console

Open SQL Console.

Run:

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

The query itself contains no joins.

The CDS model already defines them.

---

21. Filter a joined element

Choose an origin country that exists in your Data Preview.

For example, if DE exists:

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

Use a real value from your tenant.

A joined field can be consumed and filtered like any other exposed CDS element.

---

22. LEFT OUTER JOIN

Another important join type is:

LEFT OUTER JOIN

Inner join

Keep a left-side row only when a matching right-side row exists.

Left outer join

Keep the left-side row even when no matching right-side row exists.

Conceptually:

Connections       Airport target
-----------       --------------
FRA → JFK         JFK exists
FRA → XYZ         XYZ missing

With an inner join:

FRA → JFK

With a left outer join:

FRA → JFK
FRA → XYZ

For the unmatched row, right-side values are null at SQL result level.

---

23. LEFT OUTER JOIN syntax

The syntax is:

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

For example:

define view entity ...
  as select from /dmo/connection as Connection

    left outer join /dmo/airport as OriginAirport
      on Connection.airport_from_id = OriginAirport.airport_id
{
  ...
}

The relationship condition remains after:

ON

Only the join type changes.

---

24. Choosing the join type

Ask:

Should the main record disappear if no related record is found?

If the model means:

only connections with valid related data

an inner join can express that.

If the model means:

keep all connections, and enrich them when related data exists

a left outer join may be appropriate.

Do not choose a join type by habit.

Choose it according to the meaning of the model.

---

25. Why Data Preview may look identical

The /DMO/ demo data is designed to be coherent.

If every connection references valid airports, then:

INNER JOIN

and:

LEFT OUTER JOIN

can show the same rows.

That does not make the two join types equivalent.

It only means the current data contains a matching right-side row for every relevant left-side row.

---

26. A note about null values

A left outer join can create null values for right-side elements when no target row matches.

Null values matter when you later:

filter
calculate
compare
consume

For this lesson, remember the core distinction:

INNER JOIN
unmatched left row disappears

LEFT OUTER JOIN
unmatched left row remains

---

27. Create ZCL_ROUTE_READER_###

Create an ABAP class:

ZCL_ROUTE_READER_###

It does not implement:

IF_OO_ADT_CLASSRUN

The Reader retrieves data.

The Runner executes the example.

---

28. Define the Reader result type

Use the CDS View Entity directly:

CLASS zcl_route_reader_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.

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

ENDCLASS.

The method contract intentionally represents the complete route model.

---

29. Add Reader methods

Add:

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.

The Reader supports:

all routes
routes from one origin airport

---

30. Implement GET_ALL

METHOD get_all.

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

ENDMETHOD.

The Reader does not implement the joins.

The CDS entity owns them.

---

31. Implement GET_FROM_AIRPORT

METHOD get_from_airport.

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

ENDMETHOD.

This is a runtime filter chosen by the consumer.

It belongs naturally in the Reader method rather than creating a CDS entity for every airport.

---

32. Complete Reader reference

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.

Activate it.

---

33. Create ZCL_CDS_L04_###

Create an executable console class:

CLASS zcl_cds_l04_### DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC.

  PUBLIC SECTION.
    INTERFACES if_oo_adt_classrun.

ENDCLASS.

---

34. Read all routes

Implement:

CLASS zcl_cds_l04_### IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

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

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

    out->write( routes ).

  ENDMETHOD.

ENDCLASS.

Activate and run:

F9

---

35. Display a readable route

Replace or supplement the table output with:

LOOP AT routes INTO DATA(route).

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

ENDLOOP.

The Runner now receives information combined from four source occurrences without containing any join syntax.

---

36. Test a dynamic airport filter

Choose an airport ID present in your Data Preview.

Example:

DATA airport_id TYPE /dmo/airport-airport_id VALUE 'FRA'.

DATA(routes_from_airport) =
  reader->get_from_airport( airport_id = airport_id ).

out->write( routes_from_airport ).

Use a value from your own system.

---

37. Final architecture

Runner
  ↓
Route Reader
  ↓
ABAP SQL
  ↓
ZI_ROUTE_###
  │
  ├── /DMO/CONNECTION
  ├── INNER JOIN /DMO/CARRIER
  ├── INNER JOIN /DMO/AIRPORT as OriginAirport
  └── INNER JOIN /DMO/AIRPORT as DestinationAirport

The route relationship logic is centralized in CDS.

---

38. Why not join directly in the Reader?

ABAP SQL can also perform joins.

But this route is a reusable data model.

Defining it as:

ZI_ROUTE_###

allows reuse by:

ABAP SQL
other CDS entities
services
later RAP layers

without repeating the same relational combination.

---

39. Why learn joins before associations?

Current ABAP CDS also provides associations for reusable relationship modeling.

Associations can later be instantiated as joins when related target data is requested.

You will learn them in Lesson 5.

Explicit joins first make these concepts visible:

data sources
ON condition
matching rows
join type
result cardinality
row multiplication

That foundation makes associations much easier to understand.

---

40. Join vs association — preview

For now:

JOIN
=
combine these sources explicitly in this CDS entity

Next lesson:

ASSOCIATION
=
define a reusable relationship that can be navigated

Do not refactor the route model yet.

Lesson 5 will revisit it deliberately.

---

41. Common mistakes

Guessing the relationship

Do not join fields only because their names look similar.

Inspect:

source fields
target key
actual business relationship

---

Forgetting source aliases

With several sources, aliases keep field references unambiguous.

---

Reusing the same alias for origin and destination

Use:

OriginAirport
DestinationAirport

because they represent different roles.

---

Selecting from the wrong alias

This can activate but still be semantically wrong:

OriginAirport.city as DestinationCity

Correct:

DestinationAirport.city as DestinationCity

---

Marking descriptions as keys

Do not mark:

CarrierName
OriginCity
DestinationCity

as route keys.

---

Assuming every join preserves the row count

A join can:

remove rows
preserve rows
multiply rows

depending on its type, condition, and target data.

---

Hiding duplicate rows with DISTINCT

If a join unexpectedly duplicates a source key, investigate the relationship first.

Do not use DISTINCT simply to hide an incorrect join condition.

---

Moving reusable joins into the Runner

The Runner is not the data-modeling layer.

Keep reusable joins in CDS.

---

42. Knowledge check

1. What does INNER JOIN return?

Show answer

Rows for which matching records satisfy the join condition on both sides.

2. What does ON define?

Show answer

The relationship condition that determines which rows from the joined sources belong together.

3. Why is /DMO/AIRPORT joined twice?

Show answer

A connection contains an origin airport and a destination airport. Both are airport records, but they play different roles.

4. Why use OriginAirport and DestinationAirport?

Show answer

They distinguish two occurrences of the same source and make the role of each occurrence clear.

5. What is the difference between INNER JOIN and LEFT OUTER JOIN?

Show answer

An inner join removes a left-side row when no right-side match exists. A left outer join preserves the left row and has null right-side values at SQL level when no match exists.

6. How can one source row become multiple result rows?

Show answer

If its join condition matches several target rows, the source row appears once for each match.

7. Which fields identify /DMO/CONNECTION?

Show answer

carrier_id and connection_id together.

8. Does the Reader contain the route joins?

Show answer

No. The Reader consumes ZI_ROUTE_###. The CDS entity owns the joins.

---

43. Lesson summary

You moved from single-source CDS to multi-source relational modeling.

You used:

INNER JOIN
LEFT OUTER JOIN
ON
data-source aliases
same source in multiple roles
complete-key reasoning
row-multiplication reasoning
Data Preview
SQL Console
Reader consumption
dynamic ABAP SQL filtering

The model is:

                 /DMO/CARRIER
                      │
                      ▼
/DMO/CONNECTION → ZI_ROUTE_### ← /DMO/AIRPORT as OriginAirport
                      ↑
                      └──────── /DMO/AIRPORT as DestinationAirport

The central idea is:

A CDS join combines related rows into one reusable result model according to an explicit relationship condition.

---

44. What comes next?

Before moving on, complete the Lesson 4 practice assignment to apply joins to booking, customer, and carrier data. Use the Lesson 4 companion for deeper explanations, troubleshooting, and reference implementations.

Lesson 5 — CDS Associations & Path Expressions introduces:

CDS associations
cardinality
association aliases
exposed associations
path expressions

You will revisit the relationships:

Connection → Carrier
Connection → Origin Airport
Connection → Destination Airport

and model them as reusable associations rather than only as fixed joins.