The COUNT() function in SQL is an aggregate function that returns the number of rows that match a specified criterion. It is commonly used to count rows in a table or the number of non-NULL values in a column.

COUNT(1) vs COUNT(*)

There is often confusion between COUNT(1) and COUNT(*). Both are used to count the number of rows in a table, and they produce the same result. The difference lies in their semantics:

• COUNT(*): Counts all rows in the table, including rows with NULL values.

• COUNT(1): Assigns the value 1 to each row and counts the number of times 1 appears, which is equivalent to the number of rows in the table.

Example

Consider a table named orders with the following columns: order_id, customer_id, order_value, and payment_date.

To count the number of rows in the orders table using COUNT(*):

This query will return the total number of rows in the table, including rows with NULL values.

To count the number of rows using COUNT(1):