The difference between count (*) and count (columnName)

Question

I would like to know what are the differences between usage:

SELECT email, COUNT( email ) AS total FROM `newsletter` GROUP BY email having total>1 

or

 SELECT count(*) as total, email FROM 'newsletter' GROUP BY email having total > 1 

Both give the same results, but what else counts count(*) than emails?

Answer 1

There is at least one difference.

• They can return different results if the email can contain NULL .

See this article for more information.

Answer 2

COUNT (*) counts all rows WHEREAS COUNT (column name) counts only non-null numbers.

Answer 3

count(*) allows the database to use the index to count, so it can improve performance. as you said, the result is in most cases the same, but:

• count(column) only counts nonzero rows

so in colusion: if you need to deal with null values, use count(column) , otherwise use count(*) for better performance.

Answer 4

SELECT COUNT(*) counts all ROWS

SELECT COUNT(email) counts all VALUES (non-NULL) values

In your case, if all rows contain values, both results may be the same. But it can affect performance, but it depends on the volumne table, storage engine, indexes ...