skip to content

Does COUNT(1) ever return a different number from COUNT(*) in SQL?

level: middleimportance: should knowfreq 70%

answer

  1. substitute the literal into the definition
  2. a constant is never NULL
  3. the digit is not a column position
  4. only a column argument changes the number
  5. the two are always equal

basics

~20 s

No. COUNT(1) counts rows where the constant 1 is not NULL, and a constant is never NULL, so it counts every row — exactly what COUNT(*) does. The choice between them is style, not semantics.

solid answer

~50 s

They are always equal. `COUNT(*)` is defined as the number of rows in the group; `COUNT(1)` evaluates the literal `1` for each row and counts the rows whose result is not NULL, and a literal is never NULL, so every row qualifies. The same holds for `COUNT(42)` or `COUNT('x')`. The `1` is **not** an ordinal reference to the first column — unlike `ORDER BY 1` or `GROUP BY 1`, an aggregate argument is an ordinary expression. What *does* change the answer is putting a **column** there: `COUNT(order_id)` skips rows where `order_id` is NULL, which is a real difference and the reason `COUNT(id)` is not a safe habit after an outer join. As for the folklore that one is faster: since the two are semantically identical there is nothing for them to differ on except an engine quirk, so prefer `COUNT(*)`, the spelling the standard blesses, and measure rather than argue.

code

sql · 6 lines
sql
-- invoices: 40 rows, 12 with amount IS NULL
SELECT COUNT(*)      AS star,     -- 40
       COUNT(1)      AS one,      -- 40
       COUNT('x')    AS literal,  -- 40
       COUNT(amount) AS a_column  -- 28
FROM invoices;

go deeper

for a junior

Be able to say that COUNT(1) and COUNT(*) always return the same number, and why: a constant is never NULL, so no row is skipped.

for a middle

Derive the equality from the definition of COUNT rather than asserting it, and immediately redirect to the difference that does matter — a nullable column as the argument.

for a senior

Treat this as a code-review question: push back on COUNT(column) used to mean 'row count', because the NULL-skipping is invisible to the next reader and breaks after an outer join.

for a principal

Use it as an example of how performance folklore survives in a codebase. Set the team norm — one spelling, justified by semantics, and any performance claim backed by a measurement on your own engine and data.

## The rule that settles it SQL gives `COUNT` two definitions: - `COUNT(*)` — the number of rows in the group. - `COUNT(<expression>)` — the number of rows for which the expression evaluates to something other than NULL. Substitute the literal `1` into the second definition. `1` is a constant; it evaluates to `1` for every row and is NULL for none of them. Therefore `COUNT(1)` counts every row, which is precisely `COUNT(*)`. The same reasoning makes `COUNT(0)`, `COUNT(-7)` and `COUNT('x')` identical to `COUNT(*)` as well. ```sql -- invoices has 40 rows, 12 of which have amount IS NULL SELECT COUNT(*) AS a, -- 40 COUNT(1) AS b, -- 40 COUNT('x') AS c, -- 40 COUNT(amount) AS d -- 28 FROM invoices; ``` Only the last column differs, and it differs because it names a **column**, not because of anything about the number 1. ## The ordinal misconception Some candidates reason that `1` means "the first column", by analogy with `ORDER BY 1` and `GROUP BY 1`, where a bare integer is a positional reference to a select-list item. That positional rule applies only in those clauses. Inside a function call the integer is just a value expression, so `COUNT(1)` never looks at any column. If it did mean "first column", it would return a different number whenever that column contained NULLs — which it does not. ## The performance folklore The belief that `COUNT(1)` is cheaper because "the engine only has to look at one thing", or that `COUNT(*)` "expands all columns", is widespread and wrong. `COUNT(*)` does not read columns at all; the star inside `COUNT` is a grammar token, unrelated to the select-list star. Since the two expressions are defined to produce identical results, any measurable difference would be an implementation artefact of one engine and version, not a rule you can carry between systems. The professional answer is: they mean the same, write `COUNT(*)`, and if someone insists there is a difference on their platform, ask for a measurement on their data rather than adopting the belief. ## What *is* worth arguing about The interesting choice is not `*` versus `1` but `*` versus a **column**: - `COUNT(*)` — how many rows are here. - `COUNT(some_column)` — how many rows have a value in that column. - `COUNT(DISTINCT some_column)` — how many different values are here. `COUNT(id)` on a table's own primary key is equal to `COUNT(*)` because a primary key cannot be NULL. That equality is why the habit spreads — and why it breaks so silently. Once that column reaches the aggregate through a `LEFT JOIN`, unmatched rows carry a NULL `id`, and `COUNT(id)` starts returning a smaller (usually correct!) number while `COUNT(*)` keeps returning the inflated one. Someone who wrote `COUNT(id)` "because it is faster" got the right answer for the wrong reason and cannot explain the query. ## How to say it in an interview Give the rule first, then the demonstration: a constant is never NULL, so the non-NULL count is the row count. Add that the `1` is not an ordinal. Close by moving the discussion where it belongs — the real semantic fork is `*` versus a nullable column — which shows you understand the family rather than having memorised a trivia answer. ## Style Teams do sometimes standardise on `COUNT(1)`; it is harmless. What is worth banning in review is `COUNT(some_column)` used as a *synonym* for "count rows", because the reader cannot tell whether the NULL-skipping was intended.

  • Is COUNT(id) also equivalent to COUNT(*)?
    Only while `id` cannot be NULL, as on a table's own primary key. If that column reaches the aggregate through an outer join, unmatched rows carry NULL and `COUNT(id)` falls below `COUNT(*)`. Relying on the equivalence hides the intent, so prefer `COUNT(*)` when you mean rows.
  • Why does GROUP BY 1 mean something different from COUNT(1)?
    A bare integer is treated as a positional reference to a select-list item only in clauses that explicitly allow it, such as `ORDER BY` and, in several engines, `GROUP BY`. Inside a function call the integer is an ordinary value expression, so `COUNT(1)` counts rows and never refers to a column.

saying these in an interview costs you the question

  • Claims COUNT(1) is faster because it touches one column
  • Says COUNT(*) has to expand and read every column
  • Thinks the 1 refers to the first column of the table
  • Believes COUNT(1) skips rows containing NULLs
  • Recommends COUNT(id) as a general replacement for COUNT(*)

context

resolving links…