A report intended to show every customer can unexpectedly omit customers without qualifying orders. The query may contain a LEFT JOIN, yet its final result behaves as though matching orders were mandatory.
The location of a filter can explain the difference. A condition in the join’s ON clause participates in deciding which right-side rows match. A condition in WHERE filters the resulting rows.
Start with the reporting question
Consider two requirements:
The first is to show every customer and attach any open orders. The second is to show only customers who have open orders.
Those requirements need different treatment. The word “open” describes the orders in both cases, but the customer population differs.
Oracle documents that a left outer join preserves rows from the left table and supplies null values for right-side expressions when no matching row exists.
For the first requirement, a query can place the order-status condition in the join:
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'OPEN';A customer with no open order can still appear, with a null order identifier.
Understand what WHERE removes
Now move the condition:
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.status = 'OPEN';The rows with no matching order have a null right-side status. Comparing that value with ‘OPEN’ does not produce true, so those rows do not survive the filter.
Oracle’s null documentation explains that ordinary comparisons involving null produce an unknown result, and a WHERE condition that evaluates to unknown does not select the row.
The second query can therefore be suitable when the report genuinely requires an open order. The problem arises when the stated requirement was to retain every customer.
Do not patch the query without testing the edge case
A tempting repair is to add OR o.order_id IS NULL to the WHERE clause. That does not necessarily reproduce the first query.
Imagine a customer who has one closed order and no open orders. The initial join finds the closed order, so it does not generate an unmatched null row for that customer. The later status filter removes the closed order, leaving the customer absent.
This example shows why the matching rule must be designed directly. A condition that seems to restore missing rows may still fail for customers with nonqualifying matches.
Check multiplicity as well as inclusion
A left join can return several rows for one customer when several orders qualify. Preserving all customers does not imply producing exactly one row per customer.
Create a small test dataset containing a customer with no orders, one with only closed orders, one with one open order, and one with several open orders. Define the expected output for each.
If the report needs one customer row, decide how multiple qualifying orders should be summarized or selected. Avoid using a final duplicate-removal step as a substitute for that decision.
A reliable join expresses both parts of the report: which entities must remain visible and how their related records should contribute.