Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

marked as duplicate by gbn, Mark Storey-Smith, Phil, Paul White, Jon Seigel Nov 8 '12 at 17:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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
up vote 6 down vote accepted
  • 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(*)

share|improve this answer
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.