Zero Rows, Doubled Totals, and a Forty-Minute Query
An analyst maintains a month-end reconciliation against the general ledger. The core query selects from payments, about 9 million rows per month, and excludes batches already settled:
SELECT p.payment_id, p.amount FROM payments p WHERE p.batch_id NOT IN (SELECT batch_id FROM settled_batches);
It returned the expected ~1.2 million rows until the upstream team added a nullable batch_id to settled_batches; since then it returns zero rows. The analyst rewrote it with NOT EXISTS, which restored the row count, then joined payment_events to show the latest event per payment. The summed amount is now roughly 2.3 times the ledger figure. Separately, the job that used to run in four minutes now takes forty; it filters with WHERE CAST(p.posted_ts AS date) = @run_date and the plan shows a full scan of payments.
The ledger total is the control figure and finance will not accept a variance, so the analyst needs a fix that can be demonstrated rather than argued.
1. Explain why the original query returned zero rows, and state the correct fix including what to change upstream.
Read the worked answer
The predicate is three-valued. For a payment whose batch_id is 7, the expression is NOT (7 = 1 OR 7 = 2 OR ... OR 7 = NULL). Any comparison with NULL yields UNKNOWN, so the OR chain can never evaluate to FALSE for a row where the subquery contains a NULL; if no value matches, the result is UNKNOWN, and a WHERE clause discards UNKNOWN rows. One NULL in settled_batches therefore empties the whole result set, regardless of how many rows exist on either side.
The fix is NOT EXISTS, which tests row existence rather than value equality and is null-safe:
WHERE NOT EXISTS (SELECT 1 FROM settled_batches s WHERE s.batch_id = p.batch_id)
A defensive alternative is NOT IN with WHERE batch_id IS NOT NULL in the subquery, but that leaves the underlying ambiguity in place.
Upstream, ask the real question: what does a settled batch with no batch_id mean? It may be a legitimate manual settlement that should be excluded from matching, in which case the nulls must be given a meaning and documented. Add a NOT NULL constraint or route null rows to a quarantine table so the semantic question is answered once rather than rediscovered.
Finally, add a volume assertion to the job: a successful run returning zero rows must fail loudly, because a silent zero is how this went unnoticed.
2. Why is the total now 2.3 times the ledger, and what is the correct way to join the event data?
Read the worked answer
The join to payment_events is one-to-many. Each payment row is duplicated once per matching event, and SUM(p.amount) then adds the payment amount once per event. With roughly 2.3 events per payment on average, the total inflates by 2.3 times. The multiplier is data-dependent, which is why it looks like a mysterious variance rather than an obvious duplication.
Correct approach: reduce the event table to one row per payment before joining, for example with ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY event_ts DESC, event_id DESC) = 1 in a CTE or subquery, or a LATERAL / CROSS APPLY that fetches the latest event. Then the join is one-to-one and SUM is safe. Ordering must include a tiebreaker so the result is deterministic when two events share a timestamp.
Two related cautions. First, do not reach for SELECT DISTINCT: it hides the fan-out from a reviewer, and it will silently collapse genuinely identical rows, so the number becomes right for the wrong reason. Second, structure the query so that aggregation of facts happens before any attribute joins; the general rule is to never SUM a fact measure across a one-to-many join.
Verification, which is what finance will accept: assert that SUM(p.amount) is identical before and after the event join, and that COUNT(payment_id) equals COUNT(DISTINCT payment_id) after the join. Encode both as automated tests so the next change to this query fails fast instead of producing a 2.3x month-end surprise.
3. The job went from four minutes to forty. Diagnose the cause and describe how you would prove the improvement rather than assume it.
Read the worked answer
The filter WHERE CAST(p.posted_ts AS date) = @run_date applies a function to the column, so the optimiser cannot use an index seek on posted_ts; it must evaluate the expression for every row and therefore scans the table. Rewrite as a half-open range on the bare column:
WHERE p.posted_ts >= @run_date AND p.posted_ts < DATEADD(day, 1, @run_date)
This form is sargable and, importantly, correct across the boundary because it does not depend on how the platform truncates dates. A covering index on posted_ts with INCLUDE (payment_id, batch_id, amount) will then support a seek without touching the base table.
Before blaming only the predicate, check the other usual suspects, because a 10x regression after a date function is suggestive but not proof. Review whether statistics on posted_ts are stale after a large load, whether the query is parameter-sniffing a plan compiled for a very different date, and whether the plan changed shape after the new nullable column altered cardinality estimates.
Proof method: capture the actual execution plan and logical reads for the old and new queries on the same data, and record elapsed time and CPU over several runs. Measure, do not estimate from plan cost percentages. Keep the before-and-after evidence with the change, and add a regression check on runtime so the next such rewrite is caught by monitoring rather than by month-end pressure.