Tell me more ×
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.
up vote 6 down vote favorite

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 programmers.stackexchange.com Aug 18 '12 at 10:41

1 Answer

up vote 9 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. 

select count(1/0) from xyz

won't return an error. 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 1 more comment

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.