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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

Is there any technical difference between them?

share|improve this question

migrated from Aug 18 '12 at 10:41

This question came from our site for professional programmers interested in conceptual questions about software development.

up vote 10 down vote accepted

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.

share|improve this answer
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
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
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 posting your answer, you agree to the privacy policy and terms of service.

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