Home / Questions / 130325
Accepted
A. Cedano
Asked: 2020-01-12 01:16:08 +0800 CST 2020-01-12 01:16:08 +0800 CST

What is the difference between COUNT(*), COUNT(1), and COUNT(column)?

  • 772

I've always wondered what the difference is between these three ways of using COUNTSQL (I also don't know if there is a difference depending on the database driver being used).

For example in MySQL, if I have a table with 100 records and I do this:

SELECT COUNT(*), COUNT(1), COUNT(columna) FROM tabla;

It throws me three columns with a correct count.

What is the difference between these three ways of using COUNT?

Is there one of them, based on objective tests, that is more recommended or does it not matter to use any of the forms?

sql

4 Answers

  1. Best Answer

    Regarding results:

    There is no difference between count(*)and count(1), for each returned row it will count 1 equally.

    But count(columna)it's different: it won't count rows where that column is NULL.

    Also, in the latter case you can count only the distinct ones:

    select count(distinct column_b)
from table;

    In terms of performance, it is difficult to say if one is better than another, since it depends on whether it is used alone or with more fields and on the database manager:

    count(*)in Oracle and PostgreSQL (possibly others as well, but I can't tell) it's detected as a "trivial" operation, the values ​​are known to be irrelevant, so using the table index you can get the value without actually looping through the rows. With count(1) the behavior is similar (the result for each row is a constant that is not null), so it is not necessary to obtain the data to know if there are NULL values ​​to discard.

    • 19

  2. COUNT(*)counts the records of SELECTthe query keeping the query columns in memory, that is, if the query has 20 columns and 300 records, it will save the 6000 spaces in memory with its data. which would delay the processing of the query.

    COUNT(columna)count records in which "column" is not NULL

    COUNT(n)(where n is a number) takes the value n (in this case) as a single column table where its value for each record is n for each record, no matter if it has 20 columns. for the example of the first case, it would be only 300 * 1 = 300 values, which would process the query faster.

    View Ref Toad World

    • 4

  3. COUNT(*) count the records of the SELECT.

    COUNT(columna)count records in which "column" is not NULL (column IS NOT NULL)

    COUNT(1)(with a number) I have never used it. Microsoft help does not consider it ( link )

    • 0
    1. count(1) = counts all the rows in the table.
    2. count(*) = counts all the rows of the table, in addition, it loads the entire structure of the columns that make up the table, this degrades the performance of the database and future processes.
    3. count(column), counts the records of the table associated with the indicated column, in addition, not considering the null values ​​for the mentioned column.

    I hope it serves you. Atte., Alex Eduardo Rivera Herrera.

    • 0