Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
1  
Pretty sure this is true on SqlServer as well –  jcolebrand Sep 22 '11 at 2:11
    
Yes, it's definitely the same on SQL Server :) –  Mr.Brownstone Aug 15 '12 at 18:44

4 Answers 4

up vote 55 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
    
is it also a good idea to force-pick an indexed column if you know one? –  jcolebrand Sep 22 '11 at 2:11
    
@jcolebrand: personally I wouldn't. I can't see what benefit it adds –  gbn Sep 22 '11 at 4:24
    
Cool, thought I would ask. (PS with the new comment system you don't have to at me if we're the only two commentors on your post.) –  jcolebrand Sep 22 '11 at 14:21
2  
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 would not force-pick an indexed column. Sometimes indexes get dropped, re-arranged, etc. Columns can be dropped/renamed as well. –  datagod Aug 15 '12 at 18:23

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 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 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
    
The main question is not which one runs faster, but if they return the same value –  bernd_k May 4 '11 at 14:58
    
@bernd_k Understood. @gbn's answer was the exact answer and was already accepted by @Martin before I submitted my answer. I figured I would just add some more insight into COUNT(1) vs COUNT(*). –  RolandoMySQLDBA May 4 '11 at 15:07
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
    
When I was an C++ developer, I personally test this out with a Oracle OCP back in 2000. –  RolandoMySQLDBA May 4 '11 at 15:44

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.