UNION in a LEFT OUTER JOIN –  CDS (SQL) performance anti-pattern

This blog post focuses on the CDS view performance anti-pattern called “UNION in LEFT OUTER JOIN” and it's part of the CDS View Performance Best Practices series. You can find an introduction to CDS View performance recommendations, aspects you need to consider in terms of performance, and a list of all available CDS anti-patterns in the following blog post: 
CDS View Performance Best Practices 

 

Why this pattern deserves attention

A UNION in the right-hand input of a LEFT OUTER JOIN is logically valid. The risk is performance: on large data volumes and complex data models, both UNION branches may be processed before a selective filter from the left side of the join can reduce them. In simple and highly selective cases, the optimizer may apply semi-join reduction. In more complex plans, that optimization is not guaranteed because plan selection is cost based.

 

The business example

A retail company tracks customer orders for multiple sales regions. Each order is fulfilled through one of two independent delivery systems: a central warehouse or a regional warehouse. Fulfillment is exclusive in this model: an order is only assigned to one delivery system. Some orders are still open and therefore have no delivery record. The data below is intentionally minimal so that result of every row can be easily followed through each query variant by hand.

Data model

The following three tables are created and populated by the complete runnable SQL at the end of this post. All rows are shown so every query result can be verified by hand.

Table: ORDERS

ORDER_ID	REGION_ID
ORD100	1
ORD200	1
ORD300	2
ORD400	2
ORD500	2

 ORD400 is still open and has no delivery in either system and therefore is not part of the following tables.

Table: CENTRAL_DELIVERIES

ORDER_ID	DELIVERY_DATE
ORD100	2026-09-10
ORD300	2026-09-15
ORD900	2026-08-20

 ORD900 is an older, delivered order that is not part of the current ORDERS selection.

Table: REGIONAL_DELIVERIES

ORDER_ID	DELIVERY_DATE
ORD200	2026-09-12
ORD500	2026-09-18
ORD950	2026-08-25

 ORD950 is another older, delivered order that is not part of the current ORDERS selection.

 

The original, correct query

The query goal is to list every order together with its delivery date from Region 2 (region_id = 2), regardless of which delivery system handled it. Open orders must still appear with a NULL delivery date — the LEFT OUTER JOIN is the mechanism that guarantees this.

with deliveries as (
select order_id, delivery_date
from central_deliveries
union all
select order_id, delivery_date
from regional_deliveries
)
select o.order_id,
o.region_id,
d.delivery_date
from orders o
left outer join deliveries d
on o.order_id = d.order_id
where o.region_id = 2;

Please note that the query uses a common table expression (CTE). The with_clause creates a temporary result set based on its subquery. The native SQL approach uses the WITH clause for simplicity, avoiding multiple view definitions and lengthy code snippets while showing the essence of the anti-pattern in a single executable statement.

Technically, this has the same effect as when a CDS view uses an association (technically a LEFT OUTER JOIN) to another CDS view that contains a UNION.

 The main query selects from the table ORDERS and uses the temporary view (CTE) DELIVERIES in a LEFT OUTER JOIN. The CTE  DELIVERIES contains a UNION ALL of the two delivery tables CENTRAL_DELIVERIES and REGIONAL_DELIVERIES.

Correct and expected result of the query:

ORDER_ID	REGION_ID	DELIVERY_DATE
ORD300	2	2026-09-15
ORD400	2	NULL
ORD500	2	2026-09-18

 

Let’s look at the execution plan for the query.

The plan places the UNION ALL below the LEFT OUTER JOIN, this means the UNION ALL is executed before the LEFT OUTER JOIN. The filtered ORDERS table is the left input, while CENTRAL_DELIVERIES and REGIONAL_DELIVERIES are combined as the right input.

The UNION ALL combines the rows from both delivery tables so that an order can match a delivery stored in either table. The LEFT OUTER JOIN then keeps every selected Region 2 order, including ORD400, for which neither table contains a delivery record.

The plan does not show the selected order IDs being used to reduce the scans of CENTRAL_DELIVERIES and REGIONAL_DELIVERIES. Rows that cannot match a selected order are therefore processed.

Can the filtered ORDERS input and the LEFT OUTER JOIN be pushed into the individual UNION ALL branches to reduce the data earlier?

 

The tempting but incorrect rewrite

At first glance, it may seem attractive to push the filtered ORDERS input and the LEFT OUTER JOIN into each UNION ALL branch:

with deliveries_wrong_opt as (
select o.order_id, o.region_id,
cd.delivery_date
from orders o
left outer join central_deliveries cd
on o.order_id = cd.order_id
where o.region_id = 2
union all
select o.order_id, o.region_id,
rd.delivery_date
from orders o
left outer join regional_deliveries rd
on o.order_id = rd.order_id
where o.region_id = 2
)
select * from deliveries_wrong_opt;

Why it fails: Each LEFT OUTER JOIN independently preserves every selected order. ORD300 has a matching row in CENTRAL_DELIVERIES, but not in REGIONAL_DELIVERIES therefore the regional branch produces a NULL-extended row for it. ORD500 has the symmetric problem. ORD400 has no match in either delivery table, so both branches produce a NULL-extended row for it.

The UNION ALL then combines the independently produced branch results. Consequently, the rewritten query returns six rows instead of the expected three. The additional rows are not caused by UNION ALL itself; they arise because each LEFT OUTER JOIN preserves unmatched orders independently.

ORDER_ID	REGION_ID	DELIVERY_DATE
ORD300	2	2026-09-15
ORD300	2	NULL
ORD400	2	NULL
ORD400	2	NULL
ORD500	2	NULL
ORD500	2	2026-09-18

The execution plan shows both the performance appeal and the structural problem of this rewrite:

The UNION ALL is now above two separate LEFT OUTER JOIN operators. Each branch reads the filtered Region 2 rows from ORDERS and joins them independently with one delivery table: the first branch with CENTRAL_DELIVERIES and the second with REGIONAL_DELIVERIES. Because only the selected orders participate in each join, fewer rows may be passed from the join branches into the UNION ALL.

The earlier reduction therefore looks attractive from a performance perspective, but it changes the result. Because both joins are LEFT OUTER JOINs, each branch preserves all three Region 2 orders before the branch results are combined. A match in one branch does not prevent the other branch from producing a NULL-extended row for the same order. For this data set, the UNION ALL consequently receives three rows from each branch and returns the six rows shown above.

If the LEFT OUTER JOIN cannot be pushed down into the UNION ALL branches, can the delivery tables still be reduced earlier without changing the result?

 

A semantics-preserving manual reduction

The LEFT OUTER JOIN cannot safely be pushed down into the UNION ALL branches. Therefore, we describe an approach where the selected order IDs can be used to reduce each delivery table before the tables are combined.

The query first creates selected_order_keys, containing the order IDs for Region 2. Each delivery table is then joined with these IDs, so only deliveries for the selected orders enter the UNION ALL. The LEFT OUTER JOIN remains in the main query and therefore still returns open orders, such as ORD400, exactly once.

with selected_order_keys as (
select distinct order_id
from orders
where region_id = 2
),
reduced_deliveries as (
select d.order_id, d.delivery_date
from central_deliveries d
inner join selected_order_keys k
on k.order_id = d.order_id
union all
select d.order_id, d.delivery_date
from regional_deliveries d
inner join selected_order_keys k
on k.order_id = d.order_id
)
select o.order_id,
o.region_id,
d.delivery_date
from orders o
left outer join reduced_deliveries d
on d.order_id = o.order_id
where o.region_id = 2;

The DISTINCT ensures that each order ID occurs only once in selected_order_keys. Otherwise, duplicate IDs could multiply matching rows in the delivery tables.

This query produces the correct and expected result:

ORDER_ID	REGION_ID	DELIVERY_DATE
ORD300	2	2026-09-15
ORD400	2	NULL
ORD500	2	2026-09-18

The result remains the same as for the original query, but unrelated delivery rows are removed before the UNION ALL. The execution plan below shows how SAP HANA implements this reduction.

The filtered Region 2 order IDs are produced once by the DISTINCT operator and reused as a shared subplan. Each delivery table is then reduced through a HASH JOIN (SEMI): only delivery rows whose ORDER_ID occurs in selected_order_keys are passed to the UNION ALL. The resulting delivery rows form the right-hand input of the final HASH JOIN (LEFT OUTER).

The semi-joins reduce the delivery branches without producing NULL-extended order rows. Preserving unmatched orders remains the responsibility of the single LEFT OUTER JOIN above the UNION ALL. The plan therefore combines early reduction with the same result semantics as the original query.

The manual rewrite makes the reduction explicit by deriving the relevant order IDs and using them to restrict both delivery branches.

But is this manual rewrite always necessary, or can SAP HANA derive the same semi-join reduction automatically?

 

How SAP HANA can apply semi-join reduction automatically

The fact that the LEFT OUTER JOIN cannot be pushed down does not automatically imply poor performance. SAP HANA can introduce semi-join reduction as an optimizer transformation without changing the SQL statement. Whether it does so is cost based and depends on estimated cardinalities, internal cost evaluation rules, selectivity, table sizes, statistics, and the complexity of the execution plan resulting from the surrounding data model.

In the small teaching example used so far, the delivery tables contain only a few rows. Reducing them first offers little or no cost advantage, so SAP HANA may reasonably retain the original plan.

With larger data volumes, reducing rows before the UNION ALL can provide enough benefit for the optimizer to choose semi-join reduction automatically. The following example demonstrates this with large SAP tables.

Example with large SAP tables

Table	Description	Approximate number of rows
TADIR	Directory of ABAP Repository objects. It provides the object type, object name, and package assignment.	7 million
DD03L	Contains field information for ABAP Dictionary tables and structures.	15 million
DD03ND	Contains field information for structured Dictionary objects, including objects related to CDS data definitions.	2.5 million

Purpose of the query

The query selects the names and types of table definitions (TABL) and CDS data definitions (DDLS) assigned to package FINS_CODING_BLOCK in TADIR. It combines field information from DD03L and DD03ND with UNION ALL and left-joins the result to the selected repository objects.

The LEFT OUTER JOIN ensures that repository objects without matching field information are still returned. Objects with multiple fields produce one result row per field.

with fields as (
select d.tabname as objectname,
d.fieldname
from dd03l d

union all

select d.strucobjn as objectname,
d.fieldname
from dd03nd d
)
select t.object,
t.obj_name,
f.fieldname
from tadir t
left outer join fields f
on f.objectname = t.obj_name
where t.devclass=”FINS_CODING_BLOCK”
and t.object in (‘TABL', ‘DDLS');

Execution with automatic semi-join reduction

For runtime measurements we will use Plan Visualizer (PlanViz) instead of the explain plan. If we create the execution plan for the query using PlanViz we can find the following time measurements on the Overview tab in the execution summary section:

Execution Time (Elapsed): 4.524 ms
Execution Time (CPU): 3.771 ms

The Plan Graph view of the PlanViz looks like this:

The filtered TADIR input is evaluated once and reused as a shared subplan. The filter returns 48 rows, compared with an estimate (by the optimizer) of 12 rows (number in parentheses). The operator times shown in this Plan Graph are CPU times, not elapsed times.

SAP HANA uses this selective result to apply a TABLE SCAN SEMI JOIN to both branches below the UNION ALL. The semi-join on DD03L passes 222 rows to the UNION ALL, while the semi-join on DD03ND passes 196 rows. Only rows with an object name that occurs in the filtered TADIR result are passed to the UNION ALL.

The UNION ALL combines the two branch results into 418 rows. These rows form the right-hand input of the final HASH JOIN (LEFT OUTER), while the filtered TADIR result remains the left-hand input. The outer join therefore continues to preserve selected repository objects without matching field information.

The plan demonstrates automatic semi-join reduction: SAP HANA uses the selective left-side input to reduce both large field tables before their rows are combined, without moving the LEFT OUTER JOIN into the UNION ALL branches. The measured CPU time for this run is approximately 3.8 ms.

The plan shows that SAP HANA reduces both field tables before the UNION ALL. But how much work does this transformation avoid? To make the difference visible, we can execute the same statement once more with semi-join reduction disabled and compare the two PlanViz results.

Execution with automatic semi-join reduction disabled

To isolate the effect of semi-join reduction, the same statement was executed again with the hint NO_HEX_TABLE_SCAN_SEMI_JOIN that prevents the table-scan semi-join reduction in the individual UNION ALL branches. The hint is used only for demonstration; disabling the optimization is not recommended.

with fields as (
select d.tabname as objectname,
d.fieldname
from dd03l d

union all

select d.strucobjn as objectname,
d.fieldname
from dd03nd d
)
select t.object,
t.obj_name,
f.fieldname
from tadir t
left outer join fields f
on f.objectname = t.obj_name
where t.devclass=”FINS_CODING_BLOCK”
and t.object in (‘TABL', ‘DDLS')
WITH HINT( NO_HEX_TABLE_SCAN_SEMI_JOIN );

The following time measurements are from the overview in the execution summary:

Execution Time (Elapsed): 472.078 ms
Execution Time (CPU): 7,291.854 ms

Compared to the previous optimized execution we can see significantly higher times.

Execution Time (Elapsed): 4.524 ms
Execution Time (CPU): 3.771 ms

The Plan Graph view looks like this:

With branch-level semi-join reduction disabled, the filtered TADIR input is still evaluated once and reused as a shared subplan. The filter returns 48 rows.

The main difference is visible below the UNION ALL. Semi-join reduction has not disappeared completely, but it now occurs above the UNION ALL. Consequently, both large field tables are scanned and combined before the result is reduced to the selected object names. The DD03L scan returns 15,747,433 rows, while the DD03ND scan returns 2,495,229 rows.

These large branch results enter the UNION ALL before a HASH JOIN (SEMI) reduces the combined field data to the object names from the filtered TADIR input. The semi-join eventually reduces the data to 418 rows, but the reduction happens only after rows from both large tables have already been read and combined.

The resulting 418 rows form the right-hand input of the final HASH JOIN (LEFT OUTER). As in the optimized plan, the filtered TADIR result remains the left-hand input, so repository objects without matching field information are still preserved. The final result therefore remains unchanged. The measured CPU time for this run is approximately 7.3 s.

Comparison of the two executions

Measurement	Semi-join reduction enabled	Branch-level reduction disabled
Rows from DD03L entering UNION ALL	222	15,747,433
Rows from DD03ND entering UNION ALL	196	2,495,229
Total rows entering UNION ALL	418	18,242,662
Final result rows	418	418
Approximate elapsed time	4.5 ms	472 ms
Approximate CPU time	3.8 ms	7.3 s

Both executions return the same 418 rows, but the optimized plan reduces each field table before the UNION ALL. This avoids passing more than 18 million rows through the union and reduces CPU time from approximately 7.3 seconds to 3.8 milliseconds. But what can a developer do if SAP HANA does not apply semi-join reduction and the UNION branches become a performance bottleneck?

 

When semi-join reduction is not applied

Semi-join reduction is a cost-based optimizer decision. It may not be selected when the left-side filter is insufficiently selective, cardinality estimates are inaccurate, or join columns contain casts or calculations. The structure of the data model also matters: additional joins, aggregations, calculated fields, and nested (reusable) views can increase the complexity of the resulting execution plan and make the relationship between the selective input and the UNION branches harder for the optimizer to identify.

This is one reason why controlling CDS model complexity is important. For broader guidance, see CDS View Performance Best Practices.

Use PlanViz with representative data volumes to check where rows are reduced, how many rows enter the UNION ALL, and whether estimated row counts differ significantly from the actual values.

What can a developer do?

• If the branches use the same source rows at the same granularity and differ only in calculated values, consider replacing the union with a single source and CASE expressions. Verify that this preserves row multiplicity and NULL handling (see anti-pattern NOT NULL preserving LEFT OUTER JOIN).
• Consider separate, more focused views when a broadly reusable union model prevents early filtering.
• For expensive and stable combinations, consider whether a persisted or precomputed result is appropriate.
• Verify every redesign with PlanViz and realistic production-scale data.

The objective is not to force a particular execution plan, but to shape the data model so that SAP HANA can reduce large data sets as early as possible.

 

Conclusion

A UNION ALL on the right side of a LEFT OUTER JOIN is logically valid, but it can become expensive when large union branches are processed before the selective left-side input reduces them. Simply moving the LEFT OUTER JOIN into the individual branches is not a safe optimization and leads to a semantically different result, because each branch would preserve unmatched rows.

SAP HANA can avoid this work through semi-join reduction, restricting the union branches while keeping the LEFT OUTER JOIN above the UNION. If this optimization is not applied and the union becomes a performance bottleneck, review the CDS model for opportunities to reduce data earlier without changing row multiplicity or NULL handling. Use PlanViz with representative data volumes to identify the problem and verify any redesign.

 

Source link