difference between count(*) and count(columnName)

Question

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

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 same results but what else is counting count(*) than the emails?

See http://dba.stackexchange.com/q/2511/630 explains in relation to the ANSI standard — gbn, Jan 10 '12 at 11:16
possible duplicate of [In SQL, what's the difference between count(column) and count(*)?](http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count) and many others — gbn, Jan 10 '12 at 11:18

Sergio Tulentsev · Accepted Answer · 2012-01-10T11:26:25.323

5

There's at least one difference.

They may return different results if email can contain NULL.

For more information, see this article.

edited Jan 10 '12 at 11:26

answered Jan 10 '12 at 11:15

Sergio Tulentsev

226,338
43
373
367

+1 fot the link, but why do you think that count(email) cannot use the coverage indexes? – newtover Jan 10 '12 at 11:18
@newtover: I didn't know about that, but Peter says so, and he knows far more than I do :-) – Sergio Tulentsev Jan 10 '12 at 11:20
Peter says that you will just need another index for the query he describes, which is though not the case for the query of the OP. – newtover Jan 10 '12 at 11:24
@newtover: indeed. Removed that item. – Sergio Tulentsev Jan 10 '12 at 11:27

score 2 · Answer 2 · answered Jan 10 '12 at 11:14

2

COUNT(*) counts all rows WHEREAS COUNT(columnname) counts non-NULLs only.

answered Jan 10 '12 at 11:14

Sudhir Bastakoti

99,167
15
158
162

score 2 · Answer 3 · answered Jan 10 '12 at 11:18

count(*) allows the database to use an index for counting, so it can do performance-improvements. as you said, the result is the same in most cases, but:

count(column) only counts non-null-rows

so in colusion: if you have to mess around with null-values, use count(column), otherwise use count(*) for better performance.

score 1 · Answer 4 · answered Jan 10 '12 at 11:15

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 make a difference in performance, but this depends on table volumne, storage engine, indexes...

difference between count(*) and count(columnName)

4 Answers4