What is the difference between count1 and count* in Oracle?

In Oracle, both COUNT(*) and COUNT(1) are aggregate functions used to count the number of rows. However, there are some subtle differences between the two.

1. The COUNT(*) function is used to count the number of rows in a query result set, including all columns and NULL values. It does not take into account the specific values in the columns, only focusing on the presence of rows. Therefore, COUNT(*) will return the total number of rows in the result set, including rows with NULL values.
2. The COUNT(1) function is also used to count the number of rows in the query result set, but it does so by checking for non-null values in each row. Since COUNT(1) only focuses on the presence or absence of rows, rather than the specific column values, it can use a constant value of 1 to represent each row. Therefore, the efficiency of calculating COUNT(1) may be higher.

In summary, COUNT(*) and COUNT(1) are functionally equivalent and can both be used to count the number of rows. The difference is that COUNT(*) will include NULL values, while COUNT(1) only counts non-null values. In practical use, if NULL values do not need to be counted, COUNT(1) can be used for more efficient calculation.