Select COUNT(*) and COUNT(column) are different things!

Select COUNT(*) and COUNT(column) are different things!

by Tanel Poder Posted on August 21, 2009

Every now and then I see someone wondering why Oracle is “returning wrong results” for some count queries when counting using COUNT(column_name) instead of COUNT(*) or COUNT(<constant>).

Oracle is actually returning correct results, its just that sometimes the people asking the questions haven’t realized that COUNT(column) is something semantically different from COUNT(*).

COUNT(*) operation countsall rows fed to it by execution plan branch under it.

COUNT(*) operation统计表对应的段中所有的数据行（包括其上各列的值为空值null的数据行）

COUNT(column) operation on the other handcountsall non-null values in that column from rows fed to it by execution plan branch under it.

COUNT(column) operation统计表对应的段中其列上的值不为空值null的数据行。

注释：COUNT(column)的一般形式为COUNT(column1||column2||......)。

And here’s a little example:

SQL> select count(*) from v$session;

 COUNT(*)
----------
        23

SQL> select count(username) from v$session;

COUNT(USERNAME)
---------------
              1  <<-- only one non-null value in that column

SQL> select count(nvl(username,'blah')) from v$session; <<-- lets replace NULLs with non-nulls

COUNT(NVL(USERNAME,'BLAH'))
---------------------------
                         23

参考：
oracle count 0     谷歌