Rule: Use count(1) to Express “Count Number of Rows”
Rule Code: CV04
Name: count_rows
Overview
In SQL, the count() function is used to count the number of rows in a result set. While both count(*) and count(1) can be used to achieve this, it is recommended to use count(1) as a convention for expressing “count number of rows”. The use of count(1) can be seen as more explicit and efficient because it avoids ambiguity, though functionally both expressions behave the same in most SQL engines. This convention ensures consistency across SQL codebases.
Explanation
Anti-pattern: Using count(*)
The use of count(*) is syntactically correct but can be considered less explicit. Some developers prefer to avoid using the asterisk (*) due to its use in other SQL contexts (e.g., select *), where it implies selecting all columns. Although SQL engines optimize count(*) to count rows, using count(1) can be more explicit, indicating the intent to count rows without confusion.
Example of Using `count(*)` (Anti-pattern):
select count(*)
from orders;
In this example, count(*) is used to count the number of rows in the orders table. While this is valid, it can be seen as less explicit than using count(1).
Best Practice: Use count(1) to Count Rows
For clarity and to follow best practices, it is recommended to use count(1) when counting rows. This syntax avoids ambiguity and explicitly expresses the intent to count rows without referring to column data or all columns with the *.
Refactored Example Using `count(1)` (Best Practice):
select count(1)
from orders;
In this refactored example, count(1) is used to count the number of rows in the orders table, providing a clearer and more consistent expression of the intent to count rows.
Conclusion
Using count(1) to count rows ensures that SQL queries are clear, explicit, and follow conventional best practices. While both count(*) and count(1) are functionally similar, the latter is preferred for expressing row counts, promoting consistency and avoiding ambiguity in SQL codebases.