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

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

Count is one of very important functions used in any database. But what many don’t 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.

CREATE MULTISET TABLE TEMP ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      X CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      Y CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
NO PRIMARY INDEX ;

INSERT INTO TEMP VALUES(,);

INSERT INTO TEMP VALUES(,'3');

INSERT INTO TEMP VALUES('2',);

INSERT INTO TEMP VALUES('1','1');

SELECT * FROM TEMP;

X	Y
2	?
?	3
?	?
1	1

SELECT COUNT(*), COUNT(x), COUNT(1) FROM TEMP;

Result Set:

Count(*)	Count(X)	Count(1)
4	2	4