This is a very good feature of the count function, to still return 0 if there is nothing to count. This makes it different from other aggregates, which will return null if there is nothing to consider. For example,

use AdventureWorks;

go

declare @one_or_more table

(

DepartmentID smallint,

[Name] nvarchar(50)

);

declare @void table (DepartmentID smallint, [Name] nvarchar(50));

-- insert 10 records into @one_or_more and leave @void empty

insert into @one_or_more

select

top 10 DepartmentID, [Name]

from HumanResources.Department;

select count(*) RecordCount, min(A.DepartmentID) DepartmentID

from @one_or_more A, @void B;

go

resulting in

RecordCount DepartmentID

----------- ------------

0 NULL

illustrates the point. The cartesian product does not return anything because the @void has no records, the min(A.DepartmentID) is therefore null though the value of the first department in the @one_or_more is actually 1, but the count shines here as it still returns 0, not null. I am not saying that implementation of the count is better, but I find this feature extremely useful.

This is a very good question, really liked it.

Oleg