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