When does COUNT (*) return NULL?

Question

Hi I have a colleague who always writes ISNULL(COUNT(*),0) , but I always thought that COUNT(*) could never return NULL .

But then I searched for interwebs, and my results allowed me to write this little piece of code:

 create table t1 ( val1 varchar(50), ) select count(*) from t1 where val1 like 'abc' group by val1 

Are there any other cases where COUNT (*) returns NULL ?

Answer 1

It does not return NULL . GROUP BY in your example causes it to not return any rows at all, which is not NULL in the column.

Answer 2

This example does not return NULL. It does not return any rows due to GROUP BY on an empty set.

COUNT (*) cannot return NULL. Therefore, ISNULL is not required.

Answer 3

ISNULL it is not needed, it will return the number