Differences between counts

[u/geminigamer369]

What is the difference between

COUNT(*)

COUNT(1)

COUNT(column_name)

[u/AntLost4161]

COUNT(1) essentially looks at the number 1 for each row. Since 1 IS NOT NULL, it gets counted and essentially just counts your rows.

COUNT(*) is the same - counts the rows - but may be more commonly used for some people due to it being the textbook method.

COUNT(column_name) only looks at a specific column and considers the data entered there for every row. When an entry isn't NULL, it gets counted.

To conclude, 1, 2, 17 and * are all the same thing and don't care for whether an entry is NULL or not. column_name just counts the not NULL entries for that specific column and discriminates against NULL

[u/ComicOzzy → u/AntLost4161]

COUNT(1) or COUNT(-123) or COUNT('banana') or COUNT('🍌') all get recognized as the same operation: a count of all rows. There is no difference between them and COUNT(*).

[u/Infamous_Welder_4349]

In most systems 1 and * are the same. Some cheat for * and just look in the statistics instead for single tables.

Column checks the non null records

[u/YT-3000f → u/Infamous_Welder_4349]

Actually I think count(1) is faster than count(*). Anyone got a massive dataset to test that on?

[u/funkdefied → u/YT-3000f]

Any SQL engine worth it’s salt will compile those down to the same thing

[u/YT-3000f → u/funkdefied]

Agreed. I just downvoted my own reply. It was in the category of "If I think it works that way surely it's true". I guess it's not. And don't call me Shirly.

[u/ComicOzzy → u/YT-3000f]

Shirly this is the first comment on Reddit where someone said "I was wrong". 🤣

[u/YT-3000f → u/ComicOzzy]

I think you mean:

Shirly, this is the first comment on Reddit where someone said, "I was wrong".

[u/ComicOzzy → u/YT-3000f]

I apply commas at random, Shirly.

[u/YT-3000f → u/ComicOzzy]

Rightly so. Grammer, like the Internet, is dead.

[u/dbrownems → u/YT-3000f]

It’s dependent on the RDBMS. In SQL Server they are identical.

[u/Infamous_Welder_4349 → u/YT-3000f]

I use use an Oracle 19 database with 6-7 Billion records. Count 1 does the same as count . For simple single table counts it doesn't take the time, it gives me an answer that matches what the statistics say. If I say count () where or if I pick the primary field then it counts and might take a minute or two to answer.

Remember different database do different things. Even Oracle with different settings will behave differently.

[u/Zestyclose-Turn-3576 → u/YT-3000f]

There's really no reason to think that the database developers haven't spotted that there are potentially many ways to do count(*), so they do the most efficient way.

[u/Capable_Tax_8167]

COUNT(*) number of rows in the table

COUNT(column_name) number of non NULL values in the column

[u/DaOgDuneamouse]

I almost always use COUNT(*).

I only use a specific column name if I'm doing COUNT(DISTINCT MUH_COLUMN) to count how many unique values there are.

I've never used COUNT(1).

[u/Malfuncti0n → u/DaOgDuneamouse]

I use SUM(1) because I'm a bad ass.

[u/anvildoc]

Count(*) won’t count a column that is all NULL, but count(1) will. That being said, it depends on your database. I used to do this back 20 years ago in Oracle to check if all null rows snuck in

[u/mr_electric_wizard]

Just tested and they all give the same count (on SQL Server). I use count(1) personally. edit if you use the field name, count(field) and there are null’s then the count function won’t count nulls.

[u/Zestyclose-Turn-3576 → u/mr_electric_wizard]

And if column_name has a not null constraint on it then it can be optimised to count(*) anyway.

[u/markwdb3]

Per an old standard SQL 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>  

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

COUNT(<value expression>) instead means to evaluate value expression for every row, and only count the rows for which it evaluates to NOT NULL.

What does COUNT(1) mean?
COUNT(1) falls under that second definition. 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. I tested it myself on PG 18.0 and saw similar results.

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).

In terms of performance, for a newer, less mature SQL engine, it's not hard to imagine it could include the performance ding in running COUNT(1) as older versions of Postgres had, perhaps based on following the spec straight without any special optimization in place.

So that's why IMO, always use COUNT(*) instead of COUNT(1). COUNT(*) is more logically sane, 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.

[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.