In MySql we can count the total number of records by using count(1) or count(*).

Is there any technical difference between them?

1 Answer 1


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


select count(ALL 1/0) from xyz; -- Succeeds


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.

  • 1
    In postgresql select count(1/0) from xyz produces a "ERROR: division by zero" error.
    – thisfeller
    Commented Aug 17, 2012 at 20:18
  • Yes, is interesting to see how the different RDBMS handle an invalid expression.
    – StuartLC
    Commented Aug 17, 2012 at 20:22
  • Correct .. select count(1/0) returns 0 always.
    – Satish Pandey
    Commented Aug 17, 2012 at 20:32
  • 2
    COUNT(xxx) return number of rows where xxx is present, you can use COUNT(column) to count number of non-NULL columns ("SELECT count(xxx) FROM table" is conceptually same as "count of rows in 'SELECT xxx FROM table'"). In that light, COUNT(*) being same as COUNT(1) has its logic, as well as 0 returned for COUNT(1/0) in some systems (no record contains 1/0).
    – herby
    Commented Aug 17, 2012 at 20:43
  • 1
    Please note that In SQL Server (at least), when you use SELECT COUNT(DISTINCT a) FROM T, the expression is evaluated and is not ignored as mentioned.
    – NoChance
    Commented Aug 17, 2012 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.