TSQL Count function and distinct missing NULL values

Question

According to MSDN, the TSQL COUNT(*) function includes any NULL values in the result unless DISTINCT is also used (source: http://msdn.microsoft.com/en-us/library/ms175997.aspx)

However, in my query NULL values are being ignored. To test this, I created a small table and filled it with some data:

CREATE TABLE tbl (colA varchar(1), colB varchar(10))
INSERT tbl VALUES
('Y', 'test1'),
('Y', 'test2'),
(null, 'test3'),
(null, 'test4'),
('N', 'test5')

I then ran the following 2 queries on it:

SELECT count(*) FROM tbl
WHERE colA <> 'N'

and

SELECT DISTINCT colA FROM tbl
WHERE colA <> 'N'

Both results ignore the NULL values. I get 2 and 'Y' as the results respectively. I'm at a loss to why this is happening. Could someone please clue me in?

Results simulated in SQL Fiddle: http://sqlfiddle.com/#!3/8f00b/9

Answer 1

Nulls are weird.

null <> 'N' evaluates to false.
null = 'N' also evaluates to false.

You need to handle null explicitly:

SELECT count(*) FROM tbl
WHERE (colA <> 'N') or (colA is null)

Answer 2

declare @tbl as Table (colA varchar(1), colB varchar(10)) 
insert @tbl values 
  ('Y', 'test1'), ('Y', 'test2'), (null, 'test3'), (null, 'test4'), ('N', 'test5')
select * from @tbl

select
  count(*) as 'Rows', -- All rows.
  count(colA) as [colA Values], -- Rows where   colA   is not NULL.
  count(colB) as [colB Values],  -- Rows where   colB   is not NULL.
  count(distinct colA) as [Distinct colA], -- Number of distinct values in   colA .
  count(distinct colB) as [Distinct colB], -- Number of distinct values in   colB .
  -- NULL never equals anything, including another NULL.
  case when 42 = NULL then 'Yes' else 'Unknown' end as [Answer Is NULL?],
  case when NULL = NULL then 'Yes' else 'Unknown' end as [NULL = NULL],
  -- Use   is NULL   to explicitly check for   NULL .
  case when NULL is NULL then 'Yes' else 'Unknown' end as [NULL is NULL]
  from @tbl