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

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.

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

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.

Now - they should all perform identically.

In days gone by, though, COUNT(1) (or whatever constant you chose) was sometimes recommended over COUNT(*) because poor query optimisation code would make the database retrieve all of the field data prior to running the count. COUNT(1) was therefore faster, but it shouldn't matter now.

Since the expression 1 is a constant expression, they should always produce the same result, but the implementations might differ as some RDBMS might check whether 1 IS NULL for every single row in the group. This is still being done by PostgreSQL 11.3 as I have shown in this article.

I've benchmarked queries on 1M rows doing the two types of count:

-- Faster
SELECT count(*) FROM t;

-- 10% slower on PostgreSQL 11.3
SELECT count(1) FROM t;

One reason why people might use the less intuitive COUNT(1) could be that historically, it was the other way round.

The result will be the same, however COUNT(*) is slower on a lot of production environments today, because in production the db engines can live decades. I prefer to use COUNT(0), someone use COUNT(1), but definitely not COUNT(*) even if its lets say safe to use on modern db engines, I would not depend on the engine, especially if its only one character difference, also the code will be more portable.

1. count(any integer value) is faster than count(*) ---> gives all counts including null values

2. count(column_name) omits null

Ex-->

column name=> id

values => 1 1 null null 2 2

==> count(0), count(1), count(*) -----> result is 6 only

==> count(id) ----> result is 4

Let's say we have table with columns

Table 
-------
col_A  col_B

System returns all column (null and non-null) values when we query

select col_A from Table

System returns column values which are non-null when we query

select count(col_A) from Table

System returns total rows when we query

select count(*) from Table

Mysql5.6  > InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

12.19.1 Aggregate Function Descriptions

Official doc is the fastest way after I found many different answers.

COUNT(*), COUNT(1) , COUNT(0), COUNT('Y') , ...

All of the above return the total number of records (including the null ones).

But COUNT('any constant') is faster than COUNT(*).