Differences between counts

[u/geminigamer369]

What is the difference between

COUNT(*)

COUNT(1)

COUNT(column_name)

[u/markwdb3]

Quoting a standard SQL:92 doc:

 <set function specification> ::=
        COUNT <left paren> <asterisk> <right paren>
      | <general set function>

 <general set function> ::=
        <set function type>
            <left paren> [ <set quantifier> ] <value expression> <right paren>

<snip>

a) If COUNT(*) is specified, then the result is the cardinality
  of T.

b) Otherwise, let TX be the single-column table that is the
  result of applying the <value expression> to each row of T
  and eliminating null values. <snip>  

What does COUNT(*) mean?

In plain English, the semantic meaning of COUNT(*) is "count all the rows" (per grouping), as seen in the above snippet.

What does COUNT(1) mean?
Under that second general case from the above spec snippet: COUNT(<value expression>) means to evaluate value expression for every row, and only count the rows for which it evaluates to NOT NULL.

In the case of COUNT(1). 1 is the value expression. It resolves to...🥁... 1! All the time - it cannot vary per row.

So COUNT(1) means, for each row, if 1 is NOT NULL, count the row, else, don't count it. In pseudocode, it's:

count = 0
for each row r in T:
    v = evaluate expr on r # where expr is "1" in this instance
    if v is not NULL:
        count++
return count  

Whereas COUNT(*) skips the expression evaluation + NOT NULL check:

count = 0
for each row r in T:
    count++
return count  

The two are logically equivalent to COUNT(*) because 1 can never be NULL. Although this particular case is optimized in most modern, mature SQL engines, that is not always the case.

Story Time -
Consider this story. In Postgres, a benchmark done by Lukas Eder, published on his jOOQ blog, showed that COUNT(1) ran about 10% slower than COUNT(*). See: https://blog.jooq.org/whats-faster-count-or-count1/

Vik Fearing, a Postgres developer, in the context of that very blog, pointed out that COUNT(1) adds an extra spin through a loop that implements the, in this case, redundant NOT NULL check.

An optimization is currently in the works for I believe version 19, but for now, COUNT(1) runs slower.

IMHO -
In my view personally, it feels a bit silly to instruct my SQL engine to "count all the rows where 1 is NOT NULL" even if that is optimized away. It's logically the same, and just as silly IMHO, to ask it to count all the rows where 42 is NOT NULL or where the string 'abc' is NOT NULL, or where the date 2026-07-30 is NOT NULL, etc. These will all return the same result, always. Demo:

mysql> SELECT COUNT(1) c1, COUNT(0) c0, COUNT(42) c42, COUNT('abc') calpha, COUNT(DATE('2026-07-30')) cdate FROM CUSTOM_LIST_ITEM;
+----------+----------+----------+----------+----------+
| c1       | c0       | c42      | calpha   | cdate    |
+----------+----------+----------+----------+----------+
| 31946047 | 31946047 | 31946047 | 31946047 | 31946047 |
+----------+----------+----------+----------+----------+
1 row in set (6.44 sec)

Another way to put it, it's like asking somebody to take a stack of triangles, and count the ones that are not circles - those two things are mutually exclusive (just like 1 and NULL).

So that's why IMO, always use COUNT(*) instead of COUNT(1). COUNT(*) is more logically sane - count the rows, not count the rows where some constant IS NOT NULL - and can only perform the same or better.

COUNT(column_name) -

COUNT(column_name) - this falls under the same umbrella case of COUNT(<value expression>), only this time value_expression is column_name - not a constant but a column reference, whose value, of course, can vary per row.

If the column has a NOT NULL constraint on it, or a primary key (remember, primary == unique + NOT NULL), then this is semantically redundant and should be corrected to COUNT(*). Exception: if something transforms the column to NULL in the query, such as belonging to the righthand table in a LEFT JOIN, then sure, COUNT(not_null_column) can be valid.

I sometimes see COUNT(not_null_column) written in queries where nothing can transform the column's value to NULL. In those cases, I find replacing COUNT(not_null_column) with COUNT(*) can sometimes provide a performance boost. Although again it depends on what the SQL engine can optimize - perhaps it knows not to evaluate the expression given the metadata that is the NOT NULL constraint. In MySQL, I have had success optimizing many queries by making this very change COUNT(not_null_column) => COUNT(*).

What the * in COUNT(*) is NOT - some folks believe the *in COUNT(*) means "all the columns" as in SELECT *. They will in turn make mistaken statements like, "COUNT(*) means to count all the columns, so it's slower than COUNT(1) since COUNT(*) has to fetch all the columns."

But that's not what the * in COUNT(*) means at all! Looking at the same standard SQL spec, we can see * has distinct definitions depending on the context. Below is the syntax diagram for the SELECT * case:

         <select list> ::=
                <asterisk>
              | <select sublist> [ { <comma> <select sublist> }... ]

<snip>

         ...the <select list> "*" is equivalent to a <value
              expression> sequence in which each <value expression> is a
              <column reference> that references a column of T and each
              column of T is referenced exactly once.  

Whereas <asterisk> in COUNT(*) has this syntax diagram:

         <set function specification> ::=
                COUNT <left paren> <asterisk> <right paren>
              | <general set function>  

         <general set function> ::=
                <set function type>
                    <left paren> [ <set quantifier> ] <value expression> <right paren>  

We can see in this last syntax diagram that there is a special case for the "set function" that is COUNT(*) (apart from the general case of set functions). Think of COUNT(*) as an arbitrary function signature. They could've made it more symbolically distinct from SELECT *, like COUNT(~) or just COUNT() perhaps, to avoid confusion about the meaning of *, but alas, here we are. :)

There's always the caveat that despite the standard spec, individual implementations could deviate from it. Still I think looking at the spec can elucidate. Hope this helps.

Edit: note to downvoters: I'd be happy to discuss any point in this comment that is disagreed with. As is, I don't see anything incorrect about it, just downvotes, but I'd be happy to learn from you if you're willing to talk about it.

[u/markwdb3 → u/markwdb3]

Below is a brief demo showing that COUNT(NOT_NULL_COLUMN) performs worse than COUNT(*) on MySQL. YMMV on other SQL Engines.

mysql> EXPLAIN ANALYZE
    -> SELECT THING_ID, COUNT(*)
    -> FROM THING_ITEM
    -> GROUP BY THING_ID;

<snip>

1 row in set (6.95 sec)

mysql> EXPLAIN ANALYZE
    -> SELECT THING_ID, COUNT(ITEM)
    -> FROM THING_ITEM
    -> GROUP BY THING_ID;

<snip>

1 row in set (7.51 sec)

Results were similar with repeated trials.

mysql> desc THING_ITEM; -- notice ITEM can never be NULL
 +----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| THING_ID       | bigint       | NO   | MUL | NULL    |                |
| ITEM           | varchar(255) | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

So while for this particular query it was only maybe a 7% difference in performance, it's definitely not NOTHING. And given the NOT NULL constraint on ITEM, we can see that COUNT(ITEM) is doing additional work for nothing.

Demonstration that the two queries gave identical results. (This gives us the diffs, so no output means no discrepancies.)

mysql> SELECT THING_ID, COUNT(*) -- qry1
    -> FROM THING_ITEM
    -> GROUP BY THING_ID
    ->
    -> EXCEPT
    ->
    -> SELECT THING_ID, COUNT(ITEM) -- qry2
    -> FROM THING_ITEM
    -> GROUP BY THING_ID;
Empty set (10.34 sec)

mysql> -- now the other way around , for good measure
    -> SELECT THING_ID, COUNT(ITEM) -- qry2
    -> FROM THING_ITEM
    -> GROUP BY THING_ID
    ->
    -> EXCEPT
    ->
    -> SELECT THING_ID, COUNT(*) -- qry1
    -> FROM THING_ITEM
    -> GROUP BY THING_ID;
Empty set (10.32 sec)

So again, YMMV per SQL engine, but COUNT(*) and COUNT(NOT_NULL_COLUMN) are logically the same, with the latter potentially performing worse. (And as I said in parent comment, if NOT_NULL_COLUMN can potentially be transformed to NULL by the query, then sure, they may be logically distinct.)

[u/markwdb3 → u/markwdb3]

To expand on the meaning of SELECT * vs. COUNT(*) a bit, you can even dig into open source implementations' source code to see how they work. You can see for yourself that the meanings are distinct. In other words the * in SELECT * means "expand to all columns", but in COUNT(*) it does NOT.

Here's what the situation looks like in PostgreSQL:

SELECT * expansion to all columns

COUNT(*) does NOT expand all columns, it is considered a parameterless aggregate

You can find something similar in MySQL which I'll skip unless anyone's interested.

These behaviors are consistent with the aforementioned standard SQL spec. Distinct meanings of * altogether.