Why prefer EXISTS over comparing SELECT COUNT(*) to zero when testing whether a matching row exists?

## Two questions that look alike "Does this customer have any orders?" and "how many orders does this customer have?" feel like the same query with an extra step. They are not. The first has an answer the moment the engine finds one row. The second has no answer until the engine has found and tallied the last one. Asking the counting question and then comparing to zero means paying the full price of the harder question to get the answer to the easier one. ## What each form makes the engine do ```sql -- Counts every matching order just to learn "at least one" SELECT c.customer_id FROM customers c WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 0; ``` `COUNT(*)` is an aggregate over a set. Aggregates are, by nature, blocking: the value is not known until the input is exhausted. So for each customer the engine walks every index entry or row matching that customer id, incrementing a counter, and finally produces a number that the outer predicate reduces to a single bit. ```sql -- Stops at the first matching order SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); ``` `EXISTS` is a predicate, not an aggregate. It is TRUE when the subquery produces at least one row, which means the engine is free to abandon the subquery as soon as it has one. On an index over `orders(customer_id)` that is a descent to the first matching entry and nothing more. Its cost is essentially constant in the number of matches; the count's cost is linear in them. ## Why the gap is invisible in development On a seeded test database where every customer has three orders, both forms cost the same, and the counting version passes review. Production has the customer with 300,000 orders — a marketplace seller, a batch integration account, the internal test tenant nobody deleted. That single row is where the two forms diverge by five orders of magnitude, and it is also the row most likely to be requested repeatedly. This is the classic shape of a query that "was fine in staging". ## Do not lean on the optimizer Some engines can recognise certain count-then-compare shapes and short-circuit them; others cannot, and the behaviour differs across versions and across where in the statement the count appears. Treat that as an unreliable safety net rather than a licence. The predicate you write is documentation of your intent as well as an instruction: `EXISTS` says "I want to know whether any exist", `COUNT(*) > 0` says "I want the total, and then I want to compare it". Write the one you mean, and the plan tends to follow. ## The same reasoning in the application layer The anti-pattern also arrives from application code: fetching the full child collection and checking whether it is empty, or issuing `SELECT COUNT(*)` from the service and branching on the result. Both push the same wasted work into the database and then across the network. If the only thing the code needs is a boolean, the query should return a boolean, and it should be a query whose cost does not scale with how much data happens to sit behind the answer. ## Related shapes worth recognising A few near-relatives of the same mistake: - `COUNT(*) >= 1` and `COUNT(1) > 0` are the same anti-pattern in different clothing; the `1` versus `*` argument changes nothing about the scan. - `COUNT(DISTINCT o.id) > 0` is strictly worse — now the engine also deduplicates a set whose size you were never going to use. - Fetching rows and checking whether the result set is empty from the client shifts the same work onto the wire. - The mirror case, "has no matching row", has the same shape: `NOT EXISTS` expresses it directly, whereas counting to prove a zero forces a complete scan by definition, since you cannot know a count is zero until you have looked everywhere. ## When counting is genuinely the right query None of this makes `COUNT(*)` bad — it is exactly right when the number is the answer. If the report shows "orders: 42", or the predicate is `> 5` rather than `> 0`, you need the aggregate and there is no shortcut, because a threshold above one cannot be settled by the first row. The rule is narrow and precise: when the comparison is against zero, the count is a detour, and `EXISTS` is the direct route.