MySQL COUNT() and nulls

Question

Am I correct in saying:

COUNT(expr)
WHERE expr IS NOT *

Will count only non nulls?

Will COUNT(*) always count all rows? And What if all columns are null?

user_11313771 · Answer

Using MySQL I found this simple way:

SELECT count(ifnull(col,1)) FROM table WHERE col IS NULL;

This way will not work:

SELECT count(col) FROM table WHERE col IS NULL;

user_651601 · Answer

If you want to count only the nulls you can also use COUNT() with IF.

Example:

select count(*) as allRows, count(if(nullableField is null, 1, NULL)) as missing from myTable;

You can change the if condiditon to count what you actually want. So you can have multiple counts in one query.

user_17261361 · Answer

select count(*) as 'total', sum(if(columna is null, 1, 0)) as 'nulos' from tabla;