I believe the problem is a difference in how distinct gets used in different contexts. For the following queries
select count(distinct id) from #temp_test;
select distinct id from #temp_test;
select count(*) from (select distinct id from #temp_test) a;
the results are 3, 4 rows returned, and 4. So NULL is a distinct value but not counted as a distinct value. Maybe somebody else can answer why this makes sense.
Good work Tom.. If you use 'select count(id)' in the last select query instead of 'select count(*)' you will get the result as 3. If we don't use the field name in count(distinct) clause, it will not eliminate NULL.
Tom, a value of NULL indicates that the value does not exists and is unknown. For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.Check the following link:
In case of count(DISTINCT), it returns the number of unique non null values. See the following link for count(DISTINCT):