COUNT(*) vs. COUNT(Column): What's the Difference in SQL Row Counting?

*The difference between COUNT() and COUNT(Column) in SQL**

In SQL, the aggregate functions COUNT(*) and COUNT(column) provide different mechanisms for counting rows and columns of data in a table, respectively.

COUNT() Counts all rows in the specified table, excluding NULL values. This means that COUNT() returns the total number of non-NULL rows, regardless of which columns contain data.

On the other hand, COUNT(column) counts the number of rows in which the specified column is not NULL. This allows selective counting of non-NULL values ​​in specific columns.

*Replace COUNT(column)** with COUNT(

)

Replacing all instances of COUNT(column_name) in the query with COUNT(*) will have the following effects:

• The result set will contain an extra row containing the NULL value for column_name and a count representing the number of NULL values ​​in the column.

Demo

To illustrate this difference, let’s reproduce the scenario outlined in the original question:

<code class="language-sql">create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)

select count(*),count(id),count(id2)
from #bla</code>

Result:

<code>7   3   2</code>

In this example, the COUNT(*) column counts all seven rows, including the two rows where both id and id2 are NULL. The COUNT(id) column only counts three rows where id is not NULL, while COUNT(id2) counts two rows where id2 is not NULL.

The above is the detailed content of COUNT(*) vs. COUNT(Column): What's the Difference in SQL Row Counting?. For more information, please follow other related articles on the PHP Chinese website!