CODEWITHSUNDEEP
mysqlsql
Dhruv
Dhruv

Reputation: 1089

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.

Upvotes: 59

Views: 119701

Answers (10)

Kleber
Kleber

Reputation: 1

I guess it pretty much depends on the DB. I've always used count(0) but is giving me a different row count on SQL server than count(). And, curiously, count(1) matches count().

Beats me !

Upvotes: 0

Onns
Onns

Reputation: 363

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.

Upvotes: 1

Abhijit
Abhijit

Reputation: 353

  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

Upvotes: 1

Peter Csak
Peter Csak

Reputation: 31

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.

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220762

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.

Upvotes: 6

Vijay Vj
Vijay Vj

Reputation: 367

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

Upvotes: 0

isha verma
isha verma

Reputation: 9

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(*).

Upvotes: -1

Bren
Bren

Reputation: 2206

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

Upvotes: 70

eftpotrm
eftpotrm

Reputation: 2271

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.

Upvotes: 19

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 52

Related Questions