Take the 2-minute tour ×
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's 100% free, no registration required.

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
add comment

migrated from programmers.stackexchange.com Aug 18 '12 at 10:41

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

1 Answer

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

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.

share|improve this answer
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. –  StuartC 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. –  Emmad Kareem Aug 17 '12 at 21:42
show 2 more comments

Your Answer

 
discard

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.