What is the difference between COUNT(1) and COUNT(*) in an SQL query.
For example:
SELECT COUNT(1) FROM USUARIOS;
and
SELECT COUNT(*) FROM USUARIOS;
What database are you talking about? In what database? When speaking of performance it is difficult to make definitive assertions. It may change according to the implementation, so SQL Server can do one thing and PostgreSQL give another. It can change from one version to another, it can change according to the configuration of that bank or the whole system. It may vary according to the data stored. Performance depends on the implementation detail.
If the database optimizer thinks it should be exactly the same.
Note that the syntax there is very simple. In more complex queries ( JOIN ) may not even give the expected result when using COUNT(1) . Otherwise the fact of having a constant in place of all fields will not differ because the count will be made on all lines that pass through the filter.
You have a excellent answer about this in the OS .
The queries of Count (*) or Count (column or anything else), have no difference in performance levels, even was one of the subjects discussed in the blog of Gustavo Maia in the category "SQL Server Myths", more details in:
More in good practice it is advisable to put the column name as long as the NOT NULL column.
The difference is simple:
The two selects have no difference in performance.
In both cases, it counts all values in the table, even if the columns are null. To count the rows, SQL scans the entire table (Table Scan) and then performs COUNT (Stream Aggregate) operations.