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

Question

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.

What are the differences between these two statements, if any?

Answer 1

• 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.

Answer 2

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(*)

Answer 3

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(*)?

Answer 4

In the book Oracle8i Certified Professional DBA Certification Exam Guide (ISBN 0072130601), page 78 says COUNT(1) will actually run faster that COUNT(*) because certain mechanisms are called into play for checking the data dictionary for the every column's nullability (or at least the first column with non-nullability) when using COUNT(*). COUNT(1) bypasses those mechanisms.

MySQL cheats for 'SELECT COUNT(1) on tblname;' on MyISAM tables by reading the table header for the table count. InnoDB counts every time.

To test whether COUNT(1) will run faster than COUNT(*) in a database agnostic way, just run the following and judge the running time for yourself:

SELECT COUNT(1) FROM tblname WHERE 1 = 1;
SELECT COUNT(*) FROM tblname WHERE 1 = 1;
SELECT COUNT(column-name) FROM tblname WHERE 1 = 1;

This makes the COUNT function operate on the same level playing field regardless of storage engine or RDBMS.