Association vs SQL JOIN in ABAP CDS View Entities

• Understand the difference between Associations and SQL JOINs.
• Learn how the CDS compiler generates SQL.
• Understand why SAP prefers Associations in Interface Views.
• Understand Join Elimination.
• Prepare for ON vs WHERE in the next lesson.

One of the biggest misconceptions in ABAP CDS is believing that an Association is simply another way of writing a SQL JOIN.

Although Associations eventually become SQL JOINs during execution, they are fundamentally different concepts.

A SQL JOIN tells the database exactly how data should be retrieved.

An Association tells the CDS compiler how business objects are related.

The compiler then decides whether a SQL JOIN is actually required.

Consider the following CDS View.

define view entity ZC_SalesOrderOverview

  as select from ZI_SalesOrder

{

    key SalesDocument,

    SalesDocumentType,

    SalesOrganization,

    CreationDate,

    SoldToParty,

    _Customer.Customer,

    _Item.Material,

    _Item._Text.ProductName

}

Most developers read this from top to bottom.

The CDS compiler does not.

It first builds a graph of business relationships and then determines which branches of that graph are actually required.

ZI_SalesOrder
   │
   ├── _Customer
   │
   └── _Item
          │
          └── _Text

This graph represents business relationships—not SQL.

The compiler traverses only the branches required by the current consumer.

For example, if the Product Text is never requested, the _Text branch disappears completely before SQL is generated.

Every CDS View starts from a single root data source.

define view entity ZC_SalesOrderOverview

as select from ZI_SalesOrder

The compiler first reads only the root object.

At this moment, the generated SQL is conceptually equivalent to:

SELECT

    SalesDocument,
    SalesDocumentType,
    SalesOrganization,
    CreationDate,
    SoldToParty

FROM ZI_SALESORDER

Notice that no JOIN has been generated yet because the compiler has not encountered any navigated Associations.

The compiler now encounters:

_Customer.Customer

It immediately asks two questions:

1. Where does _Customer come from?
2. Which Association defines this relationship?

The compiler opens the Association definition.

association [0..1] to I_Customer as _Customer

on $projection.SoldToParty = _Customer.Customer

Because the Association is [0..1], the compiler knows that each Sales Order can match at most one Customer.

LEFT OUTER MANY TO ONE JOIN I_CUSTOMER

ON

ZI_SALESORDER.SOLDTOPARTY = I_CUSTOMER.CUSTOMER

This is one of the most misunderstood topics in ABAP CDS.

Many developers incorrectly think the compiler generates a simple LEFT OUTER JOIN.

Think from the perspective of the left side of the relationship.

Sales Orders

Order 100

↓

Customer 100

Order 101

↓

Customer 100

Order 102

↓

Customer 100

Notice what is happening.

• Many Sales Orders
• One Customer

From the SQL optimizer's perspective, this is a MANY TO ONE relationship.

Next, the compiler encounters:

_Item.Material

It now opens the second Association.

association [0..*] to ZI_SalesOrderItem as _Item

on $projection.SalesDocument = _Item.SalesDocument

Unlike the Customer Association, this relationship is TO MANY.

LEFT OUTER JOIN ZI_SALESORDERITEM

ON

ZI_SALESORDER.SALESDOCUMENT

=

ZI_SALESORDERITEM.SALESDOCUMENT

Notice that the compiler no longer generates a MANY TO ONE JOIN because multiple Item records may exist for a single Sales Order.

Finally, the compiler encounters:

_Item._Text.ProductName

This is not a direct navigation from Sales Order to Product Text.

Instead, the compiler follows the Association graph.

Sales Order

↓

Item

↓

Product Text

It first materializes the Item Association and only then navigates the Product Text Association.

This exactly mirrors the business relationships defined in the Virtual Data Model.

One of the most interesting compiler optimizations happens when you use session variables inside an Association.

Consider the following Association:

association [0..1] to I_ProductText as _Text

on  $projection.Material = _Text.Product
and _Text.Language = $session.system_language

You never wrote any HANA-specific SQL. However, during SQL generation, the CDS compiler converts the session variable into the corresponding native HANA function.

LEFT OUTER MANY TO ONE JOIN I_PRODUCTTEXT

ON

ITEM.MATERIAL = PRODUCTTEXT.PRODUCT

AND PRODUCTTEXT.LANGUAGE =
SESSION_CONTEXT('LOCALE_SAP')

This transformation is completely automatic and allows CDS Views to remain database independent while still executing efficiently on SAP HANA.

Another optimization performed by the CDS compiler is automatic client handling.

Notice that none of the Associations defined earlier contain theMANDT field.

Yet the generated SQL automatically includes client conditions similar to the following:

ON

HEADER.MANDT = CUSTOMER.MANDT

...

WHERE

HEADER.MANDT = SESSION_CONTEXT('CDS_CLIENT')

The compiler injects these conditions automatically, ensuring that the query retrieves data only from the current client.

Now let's look at one of the most powerful optimizations performed by the CDS compiler.

Consider the following CDS View:

{

key SalesDocument,

_Customer.Customer,

_Item.Material,

_Item._Text.ProductName

}

The compiler generates JOINs for Customer, Item and Product Text because all three Associations are navigated.

Now remove only one field.

{

key SalesDocument,

_Customer.Customer,

_Item.Material

}

Since _Item._Text.ProductName is no longer requested, the compiler removes the entire Product Text branch before SQL is generated.

No unnecessary JOIN is executed.

Initially, the compiler builds the following graph:

ZI_SalesOrder
   │
   ├── _Customer
   │
   └── _Item
          │
          └── _Text

After removing _Item._Text.ProductName, the graph becomes:

ZI_SalesOrder
   │
   ├── _Customer
   │
   └── _Item

Remove _Item.Material as well.

ZI_SalesOrder
   │
   └── _Customer

Finally, remove the Customer field.

ZI_SalesOrder

At this point, every Association has disappeared from the generated SQL because none of them are required by the consumer.