Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

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.