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.

Fictional sales rows and English customer-text availability
SaleCustomerAmountEnglish text available?
S1C1100Yes
S2C2200No

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.

A third case: the customer has only another language

Add sale S3 for customer C3 with amount 150. C3 has one German text with language DE and no English text. The intended sales total is now 450. This case is valuable because a customer with the wrong language is different from a customer with no text records at all.

A tempting repair is to keep the original join and write WHERE t.language = ‘EN’ OR t.language IS NULL. That preserves S2, which has no text match. It still removes S3: the join finds C3’s German row, so its language is DE, not NULL, and neither condition is true. The result totals 300 instead of 450. Restricting the eligible language in ON preserves all three sales and supplies a missing text for both S2 and S3.

Use small diagnostic queries before changing a report

Start with the reporting population alone. For the three-sale exercise, the following check should return three rows and amount 450. In an actual report, apply the same reporting dates, organisation and currency scope to every comparison.

SELECT COUNT(*) AS sale_rows, SUM(amount) AS sales_amount
FROM demo_sales;

Next, find customers with more than one eligible text. This query identifies duplication in the English subset, so you can investigate the relevant customer instead of inspecting the entire joined report.

SELECT customer_id, COUNT(*) AS english_text_rows
FROM demo_customer_texts
WHERE language = 'EN'
GROUP BY customer_id
HAVING COUNT(*) > 1;

Investigate whether the missing key is a text type, company, validity interval or another field in your actual model. Do not choose an arbitrary minimum name to suppress duplicates. The correct selection should follow the meaning of the data and produce a repeatable result.

To list sales without an English match, retain the language criterion in ON and test a right-side key. Here a successful equality match guarantees that t.customer_id is not NULL, making it a suitable marker.

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

This returns S2 and S3 in the expanded example. Testing customer_name instead would answer a different question: an existing English record could itself contain a NULL name. Separate a missing record from a missing attribute when assigning a data correction task.

A compact acceptance test for the correction

Use a small test dataset with one English match, no text, only a German text and two English matches. Record the expected result for each before executing the revised query. For the duplicate case, explicitly expect two joined rows until the underlying relationship is corrected; an outer join does not select a preferred text automatically.

Compare both row-level detail and the total. Two different errors can cancel: a duplicated amount may offset a removed amount and make the grand total look correct. Preserve the query version and sample sale identifiers with the test result so another developer can reproduce it.

Frequently asked questions

Will COALESCE restore sales that disappeared?

No. A display expression can replace a NULL in a retained row; it cannot bring back a row already excluded by the query’s conditions. Correct the matching and filtering logic first, then choose a helpful display label.

Does COUNT(customer_name) count every sale?

No. COUNT of a column ignores NULL values, while COUNT(*) counts result rows. Neither is automatically the number of distinct sales after a one-to-many join. State whether the measure counts sales, text records or joined rows.

Should performance tuning happen first?

First establish the required result and verify it on representative cases. Then inspect performance with suitable HANA tools. A faster query that loses sales or duplicates revenue still fails the reporting requirement.

Official SAP references

Continue learning

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

Leave a Comment

Your email address will not be published. Required fields are marked *