Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 64 down vote accepted
  • 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.

share|improve this answer
3  
Just for completeness: Oracle will use an index-scan on an indexed not-null column if count(*) is used. – a_horse_with_no_name Feb 6 '12 at 8:26
    
I thought the three possible options were COUNT(*), COUNT(<constant>) and COUNT(<column name>) and that all three could be prefixed with ALL or DISTINCT (defaulting to ALL if omitted). I'm just wondering what expression can be used where you say _or_expression? – onedaywhen yesterday

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
7  
The exam guide is wrong. In Oracle count(*) = count(1) (at least after version 7). See asktom.oracle.com/pls/asktom/… (Already referenced by @JackPDouglas) – Leigh Riffel May 4 '11 at 15:23
2  
Interesting. COUNT(*) shouldn't check columns at all as per ANSI spec. Was asked on SO for SQL Server some time ago too stackoverflow.com/questions/1221559/count-vs-count1/… – gbn May 4 '11 at 15:25
    
@gbn, @Leigh Riffel, @bernd_k Thanks for chiming in and reminding me to read and learn more, especially since I haven't been working with Oracle for a while. – RolandoMySQLDBA May 4 '11 at 17:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.