ON Condition vs WHERE Clause in ABAP CDS View Entities

75 min read
ON Condition vs WHERE Clause
Learn the architectural difference between ON conditions and WHERE clauses, understand filter pushdown and discover why choosing the correct location for a condition changes the generated SQL.
Click image to enlarge

Association Masterclass

Lesson 8 of 15 ā€“ ON Condition vs WHERE Clause

This lesson answers one of the most common interview questions and one of the biggest causes of performance issues in custom CDS Views.

Should a condition be placed inside the ON clause or in the WHERE clause?

Although both filter data, they are executed at different stages of query processing and therefore can produce different SQL, performance characteristics and even different business results.

Learning Objectives

  • Understand the purpose of the ON condition.
  • Understand the purpose of the WHERE clause.
  • Learn how the CDS compiler treats both differently.
  • Understand filter pushdown.
  • Prepare for TO MANY restrictions in Part 2.

Introduction

Developers often think that these two statements are interchangeable.

ON
ABAP CDS
association [0..1] to I_Customer as _Customer

on $projection.SoldToParty = _Customer.Customer
WHERE
ABAP CDS
where SalesOrganization = '1010'

They are not solving the same problem.

The ON condition defines the relationship between two business objects.

The WHERE clause filters the final result set after those business relationships have been evaluated.

Architect Perspective

Think of the ON condition as answering one question:

"How are these two business objects related?"

Think of the WHERE clause as answering another question:

"Which business records should finally be returned?"

Understanding the ON Condition

Every Association requires an ON condition.

It defines the relationship between the source and target business objects.

Association
ABAP CDS
association [0..1] to I_Customer as _Customer

on

$projection.SoldToParty

=

_Customer.Customer

The compiler stores this relationship as metadata.

Whenever a consumer navigates the Association, this condition becomes part of the generated JOIN.

Understanding the WHERE Clause

The WHERE clause is fundamentally different.

Example
ABAP CDS
where SalesOrganization = '1010'

This condition has nothing to do with the relationship between business objects.

It simply filters the records that should appear in the final result.

A Real Business Example

Consider a Sales Order and Customer relationship.

Association
ABAP CDS
association [0..1] to I_Customer as _Customer

on

$projection.SoldToParty

=

_Customer.Customer

This relationship should never change.

Whether you display one Sales Order or one million Sales Orders, every Sales Order is still related to its Sold-To Customer using the same business rule.

Now suppose the business asks:

Show only Sales Orders created today.

That is not a relationship.

That is simply a filter.

Correct
ABAP CDS
where CreationDate = $session.system_date

Compiler View of ON vs WHERE

The CDS compiler processes these clauses at different stages.

ON ConditionWHERE Clause
Defines business relationship.Filters final result.
Becomes JOIN condition.Becomes SQL WHERE.
Part of Association metadata.Part of query execution.
šŸ§ 

INSIDE THE CDS COMPILER

Relationship vs Filtering

šŸ’­ Compiler Thought Process

The compiler first builds relationships between business objects and only afterwards applies result filtering.

Compiler Decision Flow

1

Read the root CDS View.

2

Read every Association.

3

Store every ON condition.

4

Generate JOINs.

5

Apply WHERE conditions.

6

Return the filtered result.

āœ… Final Compiler Decision

ON conditions participate in relationship generation. WHERE clauses filter the generated result set.

āš” SQL Optimization Insight

Moving a condition from ON to WHERE can completely change both the generated SQL and the returned business data.

The Same Condition in ON and WHERE Produces Different Results

At first glance, the following two CDS Views appear to do the same thing.

They don't.

The location of the condition completely changes the semantics of the query.

Condition inside ON
ABAP CDS
association [0..1] to I_Customer as _Customer

on  $projection.SoldToParty = _Customer.Customer
and _Customer.Country = 'IN'

Here the country condition becomes part of the JOIN itself.

Every Sales Order is still returned, but Customer data is populated only when the matching Customer belongs to India.

Condition inside WHERE
ABAP CDS
where _Customer.Country = 'IN'

Here the relationship is evaluated first and then the entire result set is filtered.

Sales Orders without an Indian Customer disappear completely.

How the Generated SQL Changes

Consider a LEFT OUTER JOIN generated by the compiler.

Generated SQL (Condition in ON)
SQL
LEFT OUTER JOIN I_CUSTOMER

ON

SALESORDER.SOLDTOPARTY = CUSTOMER.CUSTOMER

AND CUSTOMER.COUNTRY = 'IN'

Every Sales Order still participates in the result because the filter belongs to the JOIN condition.

Generated SQL (Condition in WHERE)
SQL
LEFT OUTER JOIN I_CUSTOMER

ON

SALESORDER.SOLDTOPARTY = CUSTOMER.CUSTOMER

WHERE CUSTOMER.COUNTRY = 'IN'

This WHERE clause removes every row where the joined Customer is NULL.

In practice, the LEFT OUTER JOIN now behaves almost like an INNER JOIN.

Why TO MANY Associations Cannot Be Used in WHERE

This is one of the most misunderstood CDS restrictions.

TO MANY Association
ABAP CDS
association [0..*] to I_SalesDocumentItem as _Item

on $projection.SalesDocument = _Item.SalesDocument

Developers often try to write:

Incorrect
ABAP CDS
where _Item.Material = 'TG11'

The CDS compiler rejects this because the Association may return multiple matching records.

Understanding the Ambiguity

Imagine the following Sales Order.

Sales Order
Text
Sales Order 500000001

ā†“

Item 10 ā†’ Material ABC

ā†“

Item 20 ā†’ Material XYZ

ā†“

Item 30 ā†’ Material DEF

Now ask yourself one simple question.

What exactly does this condition mean?
WHERE
ABAP CDS
where _Item.Material = 'ABC'

Should the Sales Order be returned because one Item matches?

Should only Item 10 remain?

Should Items 20 and 30 disappear?

The compiler cannot answer these questions deterministically.

Rather than guessing, it rejects the statement completely.

šŸ§ 

INSIDE THE CDS COMPILER

TO MANY in WHERE

šŸ’­ Compiler Thought Process

The Association may return multiple target records. A scalar WHERE condition requires exactly one value.

Compiler Decision Flow

1

Read the Association.

2

Determine Cardinality (TO MANY).

3

Evaluate the WHERE clause.

4

Detect multiple possible target records.

5

Unable to determine a single comparison value.

6

Reject the statement.

āœ… Final Compiler Decision

Compilation error because the semantics are ambiguous.

āš” SQL Optimization Insight

The restriction exists to protect correctness, not because of a technical limitation.

What Should You Do Instead?

If you need to filter Item data, there are better design options.

RequirementRecommended Solution
Filter Sales Order ItemsBuild the CDS on I_SalesDocumentItem instead of the header.
Filter CustomerTO ONE Associations can safely be filtered.
Complex filteringConsider explicit JOINs or redesign the data model.

Real Project Example: Sales Order Search Application

A business user wants to search Sales Orders containing Material TG11.

A common mistake is trying to filter the Header CDS View using:

Code Example
ABAP CDS
where _Item.Material = 'TG11'
Instead, SAP typically starts from the Item CDS View, applies the Material filter there and then navigates back to the Header if required.

This produces deterministic results and scales much better for large datasets.

Architect Perspective

Technical Architect Insight

Ask yourself one question before writing every condition.

Am I defining a relationship,
or filtering business data?

Relationship rules belong in the ON condition.

Business filters belong in the WHERE clause.

Mixing these responsibilities is one of the most common causes of incorrect CDS models.

šŸ’” SAP Best Practice

Use the ON condition only to describe how business objects are related.

Use the WHERE clause only to restrict the final business result.

If filtering requires navigating a TO MANY Association, reconsider the root data source or redesign the CDS View instead of trying to force the condition into WHERE.

āŒ Common Mistakes

  • Using ON conditions to implement business filters.
  • Using WHERE conditions to define relationships.
  • Trying to filter TO MANY Associations directly in WHERE.
  • Assuming ON and WHERE are interchangeable.
ExperiencedInterview Question

Why can the same condition produce different results when placed in ON and WHERE?

Answer: An ON condition becomes part of the JOIN relationship, whereas a WHERE clause filters rows after the JOIN has been evaluated. This can change both the generated SQL and the final result set.

ArchitectInterview Question

Why are TO MANY Associations restricted in WHERE clauses?

Answer: Because a TO MANY Association may return multiple target records. The compiler cannot derive a single deterministic value for a scalar comparison, so it rejects the statement to prevent ambiguous query semantics.

šŸ’” Key Takeaway

The ON condition and WHERE clause solve different problems.

The ON condition defines relationships between business objects, whereas the WHERE clause filters the final business result.

Understanding this distinction explains why conditions placed in different locations can produce different SQL, different performance characteristics and even different business results.

In the next lesson, we'll explore Multiple and Nested Associations and see how the CDS compiler navigates complex business relationship graphs.