What is the difference between select count(*) and select count(any_non_null_column)?

• COUNT(*) will include NULLS
• COUNT(column_or_expression) won't.

This means COUNT(any_non_null_column) will give the same as COUNT(*) of course because there are no NULL values to cause differences.

Generally, COUNT(*) should be better because any index can be used because COUNT(column_or_expression) may not be indexed or SARGable

From ANSI-92 (look for "Scalar expressions 125")

Case:

a) If COUNT(*) is specified, then the result is the cardinality of T.

b) Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function.

The same rules apply to SQL Server and Sybase too at least

Note: COUNT(1) is the same as COUNT(*) because 1 is a non-nullable expression.

In any recent (ie 8.x+) version of Oracle they do the same thing. In other words the only difference is semantic:

select count(*) from any_table

is easily readable and obvious what you are trying to do, and

select count(any_non_null_column) from any_table

is harder to read because

1. it is longer
2. it is less recognizable
3. you have to think about whether any_non_null_column really is enforced as not null

In short, use count(*)

In a recent version there is indeed no difference between count(*) and count(any not null column), with the emphasize on not null :-) Have incidentally covered that topic with a blog post: Is count(col) better than count(*)?