Problem solve Get help with specific problems with your technologies, process and projects.

COUNT(*) or COUNT(1)

How do you specify difference between count(*) and count(1) in a select statement? Which is best to use and when?

How do you specify difference between count(*) and count(1) in a select statement? Which is best to use and when?

The difference is simple: COUNT(*) counts the number of rows produced by the query, whereas COUNT(1) counts the number of 1 values. Note that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause. This also applies to literals in aggregate functions, such as COUNT(1). It's hard to imagine a scenario where the COUNT(*) and COUNT(1) values would be different, so please do let me know if you find one.

In general, you should always use COUNT(*). This is because the database can often count rows by accessing an index, which is much faster than accessing a table. If you use COUNT(column), the database must actually inspect the individual values in the column, since it will not count NULLs. Aggregate functions like COUNT and SUM always ignore NULLs.

One would like to assume that the database optimizer is smart enough to realize that the literal value 1 will never be NULL, and not need to inspect it on every row, but it also would not surprise me to find out that the actions of appending the 1s into every row produced by the FROM clause and counting the 1s are handled by two separate logic modules, and maybe the module doing the counting doesn't know where the 1s came from. So just use COUNT(*).

Dig Deeper on Oracle and SQL

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

COUNT(1) is actually never executed because the optimizer transforms it into COUNT(*).

I wrote an article that includes the proof that Oracle ends up always running COUNT(*) even if your query said COUNT(1):

Count(*) Vs Count(1) Again?
count(*) counts all the rows values, where count(1) only counts one row value and only return one value vs lets say the other five rows that are present. to display all rows individual use count(1) and group by id. count(*) means it will count all and display an entire value.