5

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

Is there any technical difference between them?

10

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.

  • 1
    In postgresql select count(1/0) from xyz produces a "ERROR: division by zero" error. – thisfeller Aug 17 '12 at 20:18
  • Yes, is interesting to see how the different RDBMS handle an invalid expression. – StuartLC Aug 17 '12 at 20:22
  • Correct .. select count(1/0) returns 0 always. – Satish Pandey Aug 17 '12 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 Aug 17 '12 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 Aug 17 '12 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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