A LEFT JOIN report lost its zero-activity rows after a WHERE date filter was added — how do you diagnose and fix it?

## The failure mode Reports that must show every entity — every customer, every product, every region — are built on outer joins precisely so that entities with no activity still get a line. The bug class is that someone later adds a perfectly reasonable filter ("this quarter only", "exclude cancelled", "amount over zero") in the `WHERE` clause, and the zero-activity lines silently disappear. Nothing errors. The report still renders. It simply stops being a report about *every* customer, and the reader interprets the absence as a business fact. ```sql -- looks fine; quietly drops customers with no 2024 orders SELECT c.id, c.name, o.id AS order_id, o.ordered_at FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.ordered_at >= DATE '2024-01-01' AND o.ordered_at < DATE '2025-01-01'; ``` ## Diagnosis, in order **1. Establish the expected cardinality.** The report claims one line per customer at minimum. Get the baseline: `SELECT COUNT(*) FROM customers` (plus whatever preserved-side filter the report legitimately applies). Compare with the customers actually present in the output. A shortfall means the query is filtering them, not that the data is missing. **2. Pick a known-good witness.** Find one customer you can prove exists and has no orders in the range. Its absence from the output is your reproducer; its presence after the fix is your proof. Working from a specific row beats reasoning about counts, especially when other filters are in play. **3. Read the WHERE clause for outer-joined aliases.** This is the mechanical check and it finds the bug most of the time. Any predicate that names a column of the optional side — with `=`, `<>`, `<`, `>`, `BETWEEN`, `IN`, `LIKE` — evaluates to UNKNOWN on NULL-extended rows and removes them. The one exception is an explicit `IS NULL` test, which is TRUE for those rows and keeps them. **4. Bisect.** Remove the suspect predicate and re-run. If the witness reappears, you have confirmed the cause without needing to reason further. Restore it in the right clause and check the witness again. **5. Watch for the same bug arriving through a chained join.** A later `INNER JOIN` onto the optional side does the same damage as a `WHERE` predicate: its `ON` condition can never be TRUE for a NULL-extended row, so those rows are eliminated further down the `FROM` clause. Diagnosing only the `WHERE` clause misses this variant. ## The fix Move the range into the join condition, so it decides which orders count as matches: ```sql SELECT c.id, c.name, o.id AS order_id, o.ordered_at FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.ordered_at >= DATE '2024-01-01' AND o.ordered_at < DATE '2025-01-01'; ``` A customer whose orders all fall outside the window now has no surviving match, so NULL-extension gives them one row with blank order columns — exactly the "no activity" line the report wanted. Note that the fix moves the *entire* range: leaving one bound in `WHERE` reintroduces the collapse, because that single remaining comparison is still UNKNOWN on NULL-extended rows. Filters that belong to the preserved side stay where they are. `WHERE c.status = 'ACTIVE'` reads a column that is never NULL-extended, so it removes exactly the customers you meant and leaves the outer join intact. Sorting predicates by which side they name is the whole discipline. ## Verifying and keeping it fixed After the fix, the result should contain at least one row for every customer the preserved-side filters admit. That is a checkable property, and it is the right regression test: assert that a seeded customer with zero in-range orders appears in the output. Row-count equality is *not* the right assertion, because customers with several in-range orders legitimately produce several rows. It is also worth writing the intent into the SQL. A one-line comment above the join — "date range lives in ON so customers with no 2024 orders still appear" — is the cheapest defence against the next person helpfully "tidying" the predicate back into `WHERE`. ## What interviewers listen for They want a method, not a lucky guess: baseline count, a concrete witness row, a targeted read of `WHERE` for optional-side aliases, bisection to confirm, then a fix expressed as predicate placement rather than as a different join type. Mentioning the chained-inner-join variant, the need to move *all* bounds of a range, and the regression test that keeps the fix in place is what separates a senior answer from a merely correct one.