In MySql we can count the total number of records by using count(1) or count(*).
Is there any technical difference between them?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityIn MySql we can count the total number of records by using count(1) or count(*).
Is there any technical difference between them?
They are the same. This has often been asked in Stackoverflow and here
count(*), count(0), count(1), count(-1)
all return the count.
In fact, in SQL Server, the expression isn't even evaluated.
Edit In fact, in SQL Server, a COUNT(ALL ...)
CONSTANT expression doesn't appear to be evaluated at all, however, a COUNT(DISTINCT ...)
is*.
e.g.
select count(ALL 1/0) from xyz; -- Succeeds
but
select count(DISTINCT 1/0) from xyz; -- Divide by Zero
and at least one exception is NULL
select count(1) from xyz; -- Operand data type void type is invalid for count operator.
FWR in MySQL count(1/0)
returns 0 irrespective of the number of rows.
select count(1/0) from xyz
produces a "ERROR: division by zero" error.
– thisfeller
Aug 17 '12 at 20:18
select count(1/0)
returns 0 always.
– Satish Pandey
Aug 17 '12 at 20:32