COUNT(column) vs. COUNT(*): When Should I Use Which in SQL?

*Detailed explanation of the difference between COUNT(column) and COUNT() in SQL**

In SQL queries, the count() function is used for data aggregation, but there is a subtle but important difference between the two commonly used forms count(column) and count(*).

COUNT(column): exclude NULL values ​​

count(column) syntax counts the number of non-NULL values ​​in the specified column, effectively ignoring rows with NULL column values. This feature is useful when you need to determine the number of non-null values ​​in a column.

*COUNT(): Contains NULL value**

In contrast, count(*) counts the number of rows in the result set, including rows containing NULL values. Unlike count(column), it treats NULL as a valid count item. This feature is useful when you need to get the total number of rows regardless of whether a specific column contains missing data.

Example illustrating the difference

Consider a table named #bla containing the following records:

<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)</code>

Use count(column) to run the following query:

<code class="language-sql">select count(id),count(id2)
from #bla</code>

The result is:

<code>3   2</code>

Among them, count(id) only counts three non-NULL values ​​in the id column, while count(id2) counts two non-NULL values ​​in the id2 column.

However, replacing count(*) with count(column) results in 7, as shown in the following query:

<code class="language-sql">select count(*)
from #bla</code>

In this case, count(*) contains seven rows from the table, regardless of whether they contain NULL values. This distinction between counting non-NULL values ​​and counting rows becomes particularly important when dealing with datasets that contain missing data.

The above is the detailed content of COUNT(column) vs. COUNT(*): When Should I Use Which in SQL?. For more information, please follow other related articles on the PHP Chinese website!