Back to Blog

Why Your AI's Order Count Includes Customers Who Never Ordered

Asked how many orders there were and got 270. There are 250 orders. The other 20 are customers with no orders. COUNT(*) counts the empty side of a LEFT JOIN as a row.

You ask: "How many orders do we have?" The AI says 270. The orders table has 250 rows. There are 100 customers, and 20 of them have never ordered.

SELECT COUNT(*) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

The join keeps all 100 customers. The 80 who ordered produce 250 rows. The 20 who did not produce one row each, with a NULL order id. COUNT(*) counts rows, so 250 + 20 = 270.

An empty match is still a row

LEFT JOIN is doing what it was asked to do. The count is measuring the wrong thing. A customer with no order is present, and presence is not an order.

SELECT COUNT(*)    AS joined_rows,   -- 270
       COUNT(o.id) AS orders          -- 250
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

COUNT(o.id) skips NULL. The 20 empty rows disappear, and the number matches the orders table. SUM(o.amount) was already fine, because SUM skips NULL. That is why revenue can look right in the same query that over-counts orders.

This is not a LEFT JOIN that quietly becomes an INNER JOIN. There, a filter in WHERE throws the unmatched customers away and the number falls. This number is high. It is also not a join that repeats a single order. Each real order is here once. The extras are customers.

How to fix it

-- orders
SELECT COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

-- or don't join the customers at all
SELECT COUNT(*) FROM orders

Join from customers only when the question is about customers. "How many customers have no orders?" is the empty rows on purpose:

SELECT COUNT(*) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL

How to catch it

SELECT COUNT(*) - COUNT(o.id) AS customers_with_no_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

If that difference equals the gap between the AI's count and SELECT COUNT(*) FROM orders, the AI counted customers who never ordered.

The prompt that heads this off

When I ask for a count of orders, payments, or events, count that table's
id. Do not COUNT(*) across a LEFT JOIN from customers or users. If you
keep the unmatched rows, tell me how many of them have a NULL id, and
leave them out of the count.

The short version

COUNT(*) after a LEFT JOIN counts the customers who did not match. Count the order id, or count the orders table. The gap between the two counts is the empty side.


Read-only MCP gateway: mcpserver.design.

Related reading: