MySQL COUNT() and nulls

Question

am i right to say COUNT(expr) where expr is not * will count ouly non nulls? COUNT(*) will always count all rows right? what if all columns are null?

Answer 1

Correct. COUNT(*) is all rows in the table, COUNT(Expression) is where the expression is non-null only.

If all columns are NULL (which indicates you don't have a primary key, so this shouldn't happen in a normalized database) COUNT(*) still returns all of the rows inserted. Just don't do that.

You can think of the * symbol as meaning "in the table" and not "in any column".

Answer 2

count(*) is not for non-null columns, it's just the way to ask to count all rows. Roughly equivalent to count(1).

Answer 3

just checked:

select count(*)

returns 1 with one record filled with NULLs

select count(field)

returns 0.

I don't see the point in the record with NULL values. Such record must not exist.