Wednesday, November 10, 2010

Difference between count(*) and count(1) ?


Difference between count(*) and count(1) ?

Count is one of very important functions used in  any database . But what many dont know that  is the result count we get from COUNT function might be different  based on how it is used. Let us consider the example of count(*) and Count(1) and understand how there is some difference the usage.

Say for a given table with data
X       y
------------
21      31
Null    32
Null    33
41      34
Null    Null

And this query:
     select count(*), count(x), count(1) from table;

Results: 4, 2, 4

* Count(*) counts all occurrences including nulls. This is a row count for a given table.
* Count(x) counts all non null occurrences of values in column x. If x has null values, count(x) will be less than count(*).
* Count(1) is similar to  count(x) .It counts all null values in a "pseudo column" created by the constant. Since the constant 1 will never be null, this would,  be like count(*) i.e row count .

2 comments:

  1. Hi Vinay,
    Here count(*) will count all the occurences so here all the occurences means total no of rows which is 5. but in Results it is showing as 4.

    ReplyDelete
  2. Correct. It should be 5 in first and last questions.

    ReplyDelete