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

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 .