3

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
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(*)

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.