58

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?

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

  • 4
    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 Jul 27 '16 at 10:26
  • 2
    @onedaywhen COUNT(1) as a useless example, it's the same as COUNT(*). COUNT(CASE WHEN a>b THEN 1 END) as an example that count rows where a>b. – ypercubeᵀᴹ Oct 28 '16 at 18:39
16

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

9

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

1

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.

  • 8
    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
  • 3
    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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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