Question:
I've always wondered what the difference is between these three ways of using COUNT in SQL (I also don't know if there is a difference depending on which database driver is 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?
Answer:
Regarding results:
There is no difference between count(*) and count(1) , for each row returned it will count 1 equally.
But count(columna) is 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 too, but I can't tell) is detected as a "trivial" operation, the values are known to be irrelevant, so using the table index you can get the value without actually have to traverse 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.