In 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.
-
1In postgresql
select count(1/0) from xyzproduces 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 -
2COUNT(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
-
1Please 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
