SAP HANA LEFT JOIN Filters: Why Rows Disappear

A query uses a LEFT JOIN but loses sales rows that have no matching customer text. The cause can be a right-side filter placed in the WHERE clause. Understanding the difference between selecting a match and filtering the final result helps explain why a report total changes.

This small SQL example supports learners exploring SAP HANA training in Vizag. The table names and data are fictional. The SELECT snippets illustrate query semantics and have not been executed against a production system.

Define the intended result

Suppose the report should retain every sale and display an English customer text where one exists. Missing text should leave the description empty; it should not remove the sale. That requirement determines the appropriate filter placement.

The demo_sales table contains sale S1 for customer C1 with amount 100, and sale S2 for customer C2 with amount 200. The demo_customer_texts table contains one English text for C1 and no text for C2.

The starting sales total is 100 + 200 = 300. We assume there is at most one eligible English text per customer in this example.

A WHERE filter removes the unmatched sale

SELECT s.sale_id, s.customer_id, s.amount, t.customer_name
FROM demo_sales AS s
LEFT JOIN demo_customer_texts AS t
  ON s.customer_id = t.customer_id
WHERE t.language = 'EN';

The LEFT JOIN initially preserves the S2 sale and supplies NULL for the missing right-side columns. The WHERE predicate then tests t.language against ‘EN’. For S2, that comparison does not evaluate to true because the language value is NULL. The row is excluded from the final result.

Only S1 remains, so the visible amount is 100. The difference of 200 is not caused by a missing sale in the source table. It arises from applying the text-language criterion to the joined result.

An ON criterion restricts eligible matches

SELECT s.sale_id, s.customer_id, s.amount, t.customer_name
FROM demo_sales AS s
LEFT JOIN demo_customer_texts AS t
  ON s.customer_id = t.customer_id
 AND t.language = 'EN';

Here the language criterion determines which customer text qualifies as a match. S1 matches the English text. S2 has no qualifying match, but the LEFT JOIN still retains its sales columns and supplies NULL for the text. The result contains both sales, totalling 300 under the stated one-text-per-customer assumption.

Do not move every filter automatically

If the business requirement is to report only sales with an English customer text, the first query may express the intended restriction. Moving the filter would change that requirement. Decide whether the right-side attribute defines an eligible match or a condition every final row must satisfy.

A left-side sales filter, such as a reporting date condition, needs its own reasoning. The lesson is to inspect each predicate’s role, not to apply a blanket instruction that all WHERE conditions belong in ON.

Check cardinality before trusting totals

If C1 has two eligible English text rows, the join can produce two result rows for S1. Summing the joined amount would then repeat its 100. Preserving unmatched rows does not guarantee a correct aggregate.

Validate the join keys and the expected number of eligible matches per customer. If the source allows multiple texts, the business rule must define which row belongs in the result. Using DISTINCT can conceal duplicate symptoms without establishing the correct relationship.

Keep missing information meaningful

NULL can communicate that no qualifying text exists. Replacing it with a blank or a label may be useful for display, but does not create a customer text or explain why it is absent. Keep the distinction available when diagnosing completeness.

A repeatable reconciliation

1. Record the source sales count and amount.
2. Compare the joined row count and amount before aggregation.
3. Identify unmatched sales and multiply matched sales.
4. Inspect right-side predicates and their placement.
5. Verify the final query against the stated reporting requirement.

For this example, explain why the WHERE version returns 100 and the ON version returns 300. Then describe what additional check is required if the text table contains duplicates. This connects SQL behaviour to a defensible reporting total.

Continue learning

HANA calculation views and joins. Explore the linked course page for the current training information.