Now that we know the theory behind these
COUNTexpressions, what’s the difference between
COUNT(1). There is none, effectively. The
COUNT(1)evaluates a constant expression for each row in the group, and it can be proven that this constant expression will never evaluate to
NULL, so effectively, we’re running
COUNT(*), counting ALL the rows in the group again.
There should be no difference, and parsers / optimisers should be able to recognise this and not do the extra work of checking every expression evaluation for
Interestingly, in one of the four major RDBMS platforms (not including DB2), there is a performance difference of about 10%.