======================================================
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):**

.. code-block:: sql

    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):**

.. code-block:: sql

    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.

Groups:
-------

- `all <../..>`_
- `convention <../..#convention-rules>`_