The Outer Join That Filters Itself Back to an Inner Join
Harbor Freight Logistics has 500 active customers and 12,400 orders; 37 customers have never placed an order and 420 customers currently have no open orders. The service team needs every customer with open order details, including customers with none. A developer writes:
SELECT c.customer_id, c.customer_name, o.order_id, o.status FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.status = 'OPEN';
The query returns 1,120 rows and none of the 420 customers without open orders. Moving the condition to the ON clause returns 1,540 rows, and a teammate worries the outer join is now broken. A separate revenue report joins customers to orders and then to order_items; it returns 3,980 rows for the 1,120 open orders and repeats each order header total on every item line, inflating revenue. Status values are uppercase; 18 orders have a NULL status.
1. Explain why the WHERE version loses the 420 customers and why moving the condition to ON is the correct fix. State what the 1,540 rows represent and what happens to the 18 NULL-status orders.
Read the worked answer
A left outer join preserves rows from CUSTOMERS, but only until a later WHERE predicate rejects them. For a customer with no open orders, the outer join produces a row with o.status set to NULL. The predicate o.status = 'OPEN' evaluates to UNKNOWN for that row, and WHERE keeps only rows for which the predicate is TRUE. The preserved customer is therefore discarded, making the query behave like an inner join for that filter.
Moving the condition into the ON clause changes the match rule, not the preservation rule:
SELECT c.customer_id, c.customer_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
AND o.status = 'OPEN';
Now a customer with no matching open order still has no match, so the outer join emits one row with NULL order columns. The 1,540 rows are 1,120 open-order rows plus 420 customers with no open order. The 18 NULL-status orders do not satisfy o.status = 'OPEN', so they are not matched; their customers are still preserved, with NULL order columns. If NULL status should count as open, use NVL(o.status,'OPEN') = 'OPEN' only after agreeing the business rule.
2. Write a query that returns exactly the 420 customers with no open orders. Give both an outer-join method and a NOT EXISTS method, and explain which one you would standardize on.
Read the worked answer
Outer-join anti-join:
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'OPEN'
WHERE o.order_id IS NULL;
NOT EXISTS:
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'OPEN'
);
Both express the same anti-join and are null-safe because they do not compare a value to a subquery result that might contain NULL. The outer-join form is easy to extend with additional columns, but the IS NULL test must be on a column that is guaranteed non-null on a real match, such as ORDER_ID. NOT EXISTS states the intent directly and is harder to break when the SELECT list changes. I would standardize on NOT EXISTS for 'missing related row' reports and reserve the outer-join pattern for cases where other preserved-side columns are needed.
3. The revenue report returns 3,980 item rows for 1,120 open orders and inflates revenue. Write a corrected query shape and explain why SUM(DISTINCT order_total) is not a safe repair.
Read the worked answer
The join reaches item grain, so each order header value is repeated once per item line. If an order has four items, its header total appears four times and a plain SUM(o.order_total) multiplies revenue. The row count of 3,980 is also an item-line count, not an order count.
Aggregate the item table before joining, or aggregate at the correct grain:
WITH line_rev AS (
SELECT order_id,
SUM(quantity * unit_price) AS order_revenue,
COUNT(*) AS line_count
FROM order_items
GROUP BY order_id
)
SELECT c.customer_id, c.customer_name,
o.order_id,
NVL(r.order_revenue, 0) AS order_revenue,
NVL(r.line_count, 0) AS line_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN line_rev r ON r.order_id = o.order_id
WHERE o.status = 'OPEN';
For a customer-level total, sum r.order_revenue, not the header total. For a count of orders, use COUNT(DISTINCT o.order_id). SUM(DISTINCT order_total) is unsafe because two different orders can legitimately have the same total; DISTINCT would collapse them and undercount revenue. The fix is to control the grain, not to make the aggregate distinct.