Hello
I have a colleague who always writes ISNULL(COUNT(*),0), but I always thought that COUNT(*) could never return NULL.
But then I searched the interwebs and my findings 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 when COUNT(*) returns NULL?
It doesn't return NULL. The GROUP BY in your example makes it return no rows at all, which is not the same as a NULL in a column.
That example doesn't return NULL. It returns no rows at all because of the GROUP BY on an empty set.
COUNT(*) cannot return a NULL. So the ISNULL is unnecessary.
Are there any other cases when COUNT(*) returns NULL?
It doesn't return NULL in your case. It returns a result with zero rows.
Changing COUNT(*) to ISNULL(COUNT(*),0) doesn't affect that in any way.
There is one case where COUNT(*) itself can return NULL and that is when some deprecated session options are used to return NULL rather than an error on overflow.
As in the below example (on SQL Server 2022)
SET ANSI_WARNINGS ,ARITHABORT OFF
GO
SELECT COUNT(*)
FROM GENERATE_SERIES(CAST(1 AS BIGINT), CAST(2147483648 AS bigint))
In this eventuality wrapping in ISNULL(,0) would return the wrong result (0 rather than 2,147,483,648) - COUNT_BIG should just be used instead.
If a query used as a scalar subquery returns zero rows then the result of that would be NULL
e.g. in this case
SELECT (select count(*) from t1 where val1 like 'abc' group by val1)
could be replaced with
SELECT ISNULL((select count(*) from t1 where val1 like 'abc' group by val1),0)
but it probably doesn't make much sense to use a vector aggregate as a scalar subquery anyway and just removing the GROUP BY would be the best approach to guarantee a single row result
SELECT (select count(*) from t1 where val1 like 'abc')
ISNULL it not needed it will return a number
