Tuesday, November 8, 2022

DIFFERENCE BETWEEN COUNT(*), COUNT(1) AND COUNT(column)

The difference between COUNT(*) and COUNT(1) is none. They are exactly the same. They do the same thing with the same amount of work to the Database. 

However, there is a difference between COUNT(*) and COUNT(column). COUNT(*) counts the number of records in a table while COUNT(column) counts all the data in that given column, except null values. 

Example:

Suppose we have a table called CUSTOMER and to show it, we run the following query,



And we get this,


Notice the homephone column has two values as null.


Now, we can tell the difference if we run the following query,

We will be able to see this result,


And also the following query,


The result will be the one below,


In Oracle PL/SQL, the COUNT() function is used to count the number of rows in a result set that match a specified condition. There is a common misunderstanding about the differences between using COUNT(*), COUNT(1), and COUNT(column_name). Let's clarify these differences with examples:

  1. COUNT(*):
    • COUNT(*) counts the total number of rows, regardless of whether there are NULL values in any columns.
    • It's usually used when you want to count the number of rows without regard to any specific column.

Example:

sql
SELECT COUNT(*) FROM employees;
  1. COUNT(1):
    • COUNT(1) is functionally the same as COUNT(*).
    • It's used to indicate that you're counting rows without any reference to columns.

Example:

sql
SELECT COUNT(1) FROM employees;
  1. COUNT(column_name):
    • COUNT(column_name) counts the number of rows where the specified column has non-NULL values.
    • It's often used when you want to count non-NULL values in a specific column.

Example:

sql
SELECT COUNT(salary) FROM employees;

In all these cases, the result will be the same: the number of rows in the employees table.

When choosing between COUNT(*) and COUNT(1), there's generally no significant performance difference, as modern database optimizers often treat them interchangeably.

However, if you're concerned about performance and you want to count non-NULL values in a specific column, using COUNT(column_name) is more meaningful and efficient. It also gives you a clearer indication of what you're actually counting.

To summarize:

  • Use COUNT(*) or COUNT(1) when you want to count all rows.
  • Use COUNT(column_name) when you want to count non-NULL values in a specific column.

Remember that the exact performance characteristics might vary depending on the database system you're using, but the differences are usually negligible.

Hope you all have a wonderful day!! Above all, do not stress.






on