Never-Ordered Customers and the Latest-Order Query That Returns Extra Rows

NOT IN (subquery) is rewritten internally as <> ALL (subquery). Comparing a value with NULL yields UNKNOWN, not FALSE, and NOT (UNKNOWN) is still UNKNOWN. Because the subquery returns at least one NULL customer_id (guest checkouts), no row can ever satisfy the predicate, so zero rows come back. This is the single most common NULL trap in Oracle SQL; the query is logically 'not equal to every value, including an unknown one'.

Rewrite 1 - anti-join (recommended):

SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

NOT EXISTS tests row existence, never compares against NULL, and the optimizer can turn it into a hash anti-join or a nested-loop anti-join driven by the index on ORDERS(customer_id).

Rewrite 2 - NULL-safe NOT IN:

SELECT customer_id, email FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders
                          WHERE customer_id IS NOT NULL);

This is correct but easy to break: anyone who edits the subquery and drops the IS NOT NULL predicate silently reintroduces the bug.

A third option, MINUS, also works and treats NULLs as equal, but it de-duplicates and sorts both inputs, which is expensive here and hides which side produced a row. Ship the NOT EXISTS version: it is NULL-proof, index-friendly and self-documenting.

The correlated predicate o.order_ts = (SELECT MAX(order_ts) ...) is tie-tolerant by design: if a customer has two orders with the same timestamp, both rows satisfy equality against the same maximum, so both are returned. 3,118 minus 3,050 means 68 customers have tied timestamps (common after a batch import that stamps many orders with the same SYSTIMESTAMP).

Rewrite 1 - analytic row numbering (most flexible):

SELECT order_id, customer_id, order_total
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY customer_id
                                ORDER BY order_ts DESC, order_id DESC) AS rn
      FROM orders o
      WHERE customer_id IS NOT NULL)
WHERE rn = 1;

Adding order_id DESC makes the result fully deterministic; without it the winner among ties is arbitrary and can change between runs.

Rewrite 2 - KEEP LAST aggregate:

SELECT MAX(order_id) KEEP (DENSE_RANK LAST ORDER BY order_ts) AS order_id,
       customer_id,
       MAX(order_total) KEEP (DENSE_RANK LAST ORDER BY order_ts) AS order_total
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id;

KEEP returns one row per group even when ties exist, which is exactly the required grain.

Either way, back the access path with an index on (customer_id, order_ts) so the partition or grouping does not require a full scan of 2.4 million rows.

ORA-01427 is 'single-row subquery returns more than one row'. A scalar subquery in the SELECT list must return at most one row per outer row; last week's load created a second matching row for at least one customer (for example a duplicate active contract), and the query aborted.

The mistake is assuming a subquery that returns one row today will always return one row. Any subquery used as a scalar expression needs an aggregate without GROUP BY (which always returns exactly one row, even if that row is NULL) or an explicit row-limiting step.

Safe with an aggregate (one row guaranteed, NULL when no match):

SELECT c.customer_id,
       (SELECT MAX(o.order_total)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS max_order_total
FROM customers c;

Note that MAX over no matching rows returns NULL, not zero; wrap with NVL if a number is required downstream.

Safe with row limiting when you need a whole row:

SELECT c.customer_id,
       (SELECT o.order_total
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY o.order_ts DESC
        FETCH FIRST 1 ROW ONLY) AS latest_total
FROM customers c;

FETCH FIRST 1 ROW ONLY caps the subquery deterministically if the ORDER BY is unique; add order_id as a tiebreak if not. Do not 'fix' this by adding DISTINCT to the outer query - it hides the duplicate-data problem instead of resolving which row is correct.