Simon Krenger

Today a colleague approached me and asked about the difference between the following two statements, because they return different results:

select count(*) from mytable;
select count(name) from mytable;

• The count(*) operation actually counts all rows in the table, regardless of the values in the rows.
• When using count(name), the count operation counts all rows where “name” is not NULL.

So here is an example:

SQL> create table mytable(name varchar2(50),age number);

Table created.

SQL> insert into mytable (name,age) values ('A,21);

1 row created.

SQL> insert into mytable (name,age) values ('B',20);

1 row created.

SQL> insert into mytable (name,age) values ('C',22);

1 row created.

SQL> insert into mytable (age) values (25);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mytable;

  COUNT(*)
----------
         4

SQL> select count(name) from mytable;

COUNT(NAME)
-----------
          3

Because we have a NULL value in one of the rows, COUNT returns only the rows that are NOT NULL. In this case, it returns 3 instead of 4. And that is the difference between count(*) and count(name).