COUNT(*) vs COUNT(1) in SQL

In SQL, counting rows is a fundamental operation, and there are a couple of common ways to do it: COUNT(*) and COUNT(1). At first glance, these statements might seem different, but they actually perform very similar functions.

• COUNT(*): This statement counts all the rows in a table, including those with NULL values. It’s a straightforward way to get the total number of entries in a table.
  • SELECT COUNT(*) FROM table_name;
• COUNT(1): This statement also counts all the rows in a table, including those with NULL values. The 1 is a constant and doesn’t refer to any specific column in the table. It effectively works the same way as COUNT(*).
  • SELECT COUNT(1) FROM table_name;

Is There Any Difference?

Despite the different syntax, COUNT(*) and COUNT(1) generally produce the same results and performance in most database systems. Both commands are optimized by the SQL engine to count rows efficiently, without scanning the contents of each row. Here’s a deeper look into why they are similar:

1. Execution: Both COUNT(*) and COUNT(1) instruct the SQL engine to count the number of rows. The asterisk (*) and the constant (1) do not impact the performance because the engine treats them as counting all rows, including NULLs.
2. Optimization: SQL optimizers are designed to handle both COUNT(*) and COUNT(1) efficiently. In systems like MySQL, Oracle, and SQL Server, there’s no significant difference in execution time between the two. However, some versions of PostgreSQL might show a slight performance difference, with COUNT(*) occasionally being faster due to the way it handles the constant expression evaluation​.

Which One is Better?

Since COUNT(*) and COUNT(1) are functionally equivalent, choosing one over the other often comes down to personal or organizational preference. Here are a few points to consider:

• Readability: COUNT(*) is more commonly used and may be more immediately recognizable to other SQL users as counting all rows. It’s explicit in its intention, making your queries easier to read and understand.
• Consistency: If your team or project has a standard, it’s best to stick with it. Consistency in coding practices helps maintain clean and understandable code.

Given the minor differences in performance and the same results produced, COUNT(*) is generally recommended for its clarity and common usage.

Practical Examples

To illustrate, consider a table named Employees:

CREATE TABLE Employees (
    ID int primary key,
    FirstName varchar(50),
    LastName varchar(50),
    MiddleName varchar(50)
);

INSERT INTO Employees (ID, FirstName, LastName, MiddleName) VALUES
(1, 'John', 'Doe', NULL),
(2, 'Jane', 'Smith', 'A.'),
(3, 'Emily', 'Jones', 'B.'),
(4, 'Michael', 'Brown', NULL);

• Using COUNT(*):
  • SeLECT COUNT(*) AS TotalRows FROM Employees;
  • This will return 4, counting all rows including those with NULL values.
• Using COUNT(1):
  • SELECT COUNT(1) AS TotalRows FROM Employees;
  • This will also return 4, with the same effect as COUNT(*).