What is the difference between count(0), count(1).. and count(*) in mySQL/SQL?

Question

I was recently asked this question in an interview. I tried this in mySQL, and got the same results(final results). All gave the number of rows in that particular table. Can anyone explain the major difference between them.

Answer 1

Nothing really, unless you specify a field in a table or an expression within parantheses instead of constant values or *

Let me give you a detailed answer. Count will give you non-null record number of given field. Say you have a table named A

select 1 from A select 0 from A select * from A 

will all return same number of records, that is the number of rows in table A. Still the output is different. If there are 3 records in table. With X and Y as field names

select 1 from A will give you  1 1 1  select 0 from A will give you 0 0 0  select * from A will give you ( assume two columns X and Y is in the table ) X      Y --     -- value1 value1 value2 (null) value3 (null) 

So, all three queries return the same number. Unless you use

select count(Y) from A  

since there is only one non-null value you will get 1 as output

Answer 2

COUNT(*) will count the number of rows, while COUNT(expression) will count non-null values in expression and COUNT(column) will count all non-null values in column.

Since both 0 and 1 are non-null values, COUNT(0)=COUNT(1) and they both will be equivalent to the number of rows COUNT(*). It's a different concept, but the result will be the same.