count(id) on Char(0) column does not work!

Question

Possible Duplicate:
What is the difference between select count(*) and select count(any_non_null_column)?

I have a column with the type of char(0). some of the rows are NULL and some of them are empty(''). The question is that when I run the following query, the result will be 0. SELECT count(id) FROM test WHERE id IS NULL

but when I run the following query, the result is correct: SELECT count(*) FROM test WHERE id IS NULL

I have one column,why is that the case?

Not a dupe I think (at least not of that one: it is asking if count(*) is the same as count(a_not_null_column)) — Jack Douglas, Dec 16 '11 at 11:20

score 6 · Accepted Answer · answered Dec 16 '11 at 10:57

6

COUNT(somecolumn) ignores NULLs
COUNT(*) won't

You can see this with

SELECT count(id),count(*) FROM test WHERE id IS NULL

Coincidently, all aggregate functions ignore NULL except COUNT(*)

answered Dec 16 '11 at 10:57

gbn

69,809
8
163
243

So, SELECT COUNT(id) FROM test WHERE id IS NULL is guaranteed to return 0 – ypercubeᵀᴹ Dec 16 '11 at 13:34

count(id) on Char(0) column does not work!

1 Answers1