PrismitaLEARN · PRACTICE · GROW

For checking whether any rows exist, should you use COUNT or EXISTS?

SQL Server Query Tuning Intermediate 2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

Use EXISTS (or EF's .Any()). EXISTS stops at the first matching row; COUNT(*) > 0 counts every matching row before you even use the number. For “does at least one exist”, EXISTS is faster, sometimes dramatically.

Always EXISTS, or .Any() in EF. The reason is short-circuiting — EXISTS returns as soon as it finds one matching row, whereas COUNT(*) > 0 has to count all the matches first and then you compare. If a customer has ten thousand orders and I just want to know if they have any, EXISTS reads one row and COUNT reads ten thousand. And it doesn't matter whether you write SELECT 1 or SELECT * inside EXISTS, the columns are ignored.

  • IF EXISTS (SELECT 1 FROM Orders WHERE CustomerId = @id) — the engine can short-circuit on a seek.
  • SELECT 1 vs SELECT * inside EXISTS makes no difference — the column list is ignored.
  • EF: await db.Orders.AnyAsync(o => o.CustomerId == id) translates to EXISTS; CountAsync() > 0 does a full count.
All SQL Server questions More on Query Tuning