SQL Count(*) VS SQL Count(1): Various Options Summary


The performance difference between SQL Count(*) and SQL Count(1) seems quite arguable. Either side has its own convincing reasons. I just keep a note on it.

Different Online Opinions

The most popular one you might find if you search SQL Count() VS SQL Count(1) online is this stackvoerflow one: Count(*) vs Count(1) in which the accepted answer suggest that there is actually no difference between SQL Count() and SQL Count(1) and the guy use official documentation as proofs.

Interestingly, I found another post in this link: SQL Tutorial  in which the author mentioned one tip: PERFORMANCE TUNING WITH SQL COUNT, quoted from that post:

Since the SQL COUNT function will return the same results regardless of what NOT NULL field(s) you include as the SQL COUNT function parameters (ie: within the brackets), you can change the syntax of the SQL COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.
For example, based on the example above, the following syntax would result in better performance:
SELECT department, COUNT(1) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Now, the SQL COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Personally, I found the above argument is also very convincing, so if anyone could share your comments and clarification, that would be really great!

This post is not finished yet, I will continue to update it if I found any new and more convincing conclusion about SQL Count(*) VS SQL Count(1). If you got any comments or could point me to some other helpful material, that would be highly appreciated! Thank!


Written on November 13, 2014