A 'customers with no orders' report uses HAVING COUNT(*) = 0 and returns nothing — why?

## Groups come from rows The grouping step does not consult a catalogue of possible key values; it partitions the rows it actually received. If `orders` holds no row for customer 42, the grouping step never sees customer 42, never creates a group for it, and `HAVING` — which runs afterwards and only ever removes groups — has nothing to act on. That makes `HAVING COUNT(*) = 0` unsatisfiable over a single table: ```sql -- always empty SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) = 0; ``` Every group exists precisely because at least one row produced it, so `COUNT(*)` is never 0 for a returned group. The query is not wrong in syntax; it is wrong in premise. ## Absence must enter at row level Because `HAVING` cannot add anything, the missing customers must be present *before* grouping. That means starting from the table that does have them: ```sql SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) = 0; ``` Now every customer contributes at least one row, so every customer forms a group. Note the aggregate: `COUNT(o.order_id)`, not `COUNT(*)`. The unmatched rows still exist — the outer join has filled the order columns with NULLs — so `COUNT(*)` would report 1 for a customer with no orders. Counting a column from the order side gives 0, because that form of `COUNT` ignores NULLs. The direct alternative expresses the intent without aggregating at all: ```sql SELECT c.customer_id, c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` ## The same trap with a WHERE filter The subtler production version has nothing to do with `COUNT(*) = 0`. Consider "orders per customer in the last 90 days", where customers with none must still show a zero: ```sql -- customers whose only orders are old vanish from the report SELECT c.customer_id, COUNT(o.order_id) AS recent_orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_date >= DATE '2026-05-22' GROUP BY c.customer_id; ``` The `WHERE` predicate runs before grouping and eliminates the NULL-extended rows as well as the old orders, so those customers form no group at all and simply disappear — no zero, no row. The fix is to stop treating the date as a row filter over the joined result: either attach it to the join condition so unmatched customers survive, or keep every row and make the date part of the aggregate expression, so the group still exists and reports 0. ## Why HAVING keeps attracting the wrong predicate `HAVING` reads like "the final filter", so people reach for it whenever a report has too many or too few rows. The reliable check is to ask what the predicate needs in order to be evaluated. If it needs a group's summary — a count, a sum, a maximum — only `HAVING` can host it. If it needs a single row's column, it belongs in `WHERE` (or in the join condition, when preserving unmatched rows matters). And if the requirement is about rows that *do not exist*, no clause after `GROUP BY` can help at all, because the object being filtered was never created. ## Diagnosing this in the wild When a report is missing entities rather than showing zeros, trace it in stages. Run the query without `HAVING`: if the entity is still absent, the loss happened at or before grouping, so look at `WHERE` and at the join. If it appears with a wrong count, the aggregate expression is counting the wrong thing — frequently `COUNT(*)` where the outer-join side's column was meant. Only if the entity appears with a correct count does the fault actually lie in the `HAVING` predicate. ## What a strong answer sounds like State the invariant plainly — a group exists only because rows produced it, so `HAVING COUNT(*) = 0` can never match — then show that absence must be introduced before grouping, and note the related `WHERE`-filter version of the bug, which is the one that reaches production because it looks correct on data where every customer happens to have a recent order.