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

*COUNT () and COUNT (column name) in SQL: subtle but important differences**

When using SQL queries, it is crucial to understand the subtleties of different aggregate functions such as COUNT(*) and COUNT(column name). This article aims to clarify the subtle but important differences between these two functions.

*Comparison of COUNT (column name) and COUNT ()**

COUNT (column name) counts the number of non-null values ​​in the specified column. This function determines the number of values ​​that exist in a column that meet specified criteria.

COUNT(*), on the other hand, counts all rows in the table, regardless of whether they contain null values. It is sometimes called the "universal quantifier" because it considers every row in the table, effectively returning the total number of rows.

Practical example

Consider the following query that counts the number of duplicate values ​​in a table:

select column_name, count(column_name)
from table
group by column_name
having count(column_name) > 1;

In this query, the COUNT(column_name) function is used to count the number of occurrences of non-null values ​​in the specified column (column_name). If a value appears non-null more than once, the row is considered a duplicate.

If we replace the COUNT(column_name) function with COUNT(*) in this query, the results will be slightly different. The modified query returns an additional row in the result set that contains a null value for the column_name column and the total number of rows in the table (including those rows that contain null values).

Demo

To illustrate this difference, consider the following SQL code:

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

The results of executing this query are as follows:

<code>7   3   2</code>

The COUNT(*) function returns 7, indicating the total number of rows in the table. The COUNT(id) function returns 3, which represents the number of rows with non-null values ​​in the id column. The COUNT(id2) function returns 2, indicating the number of rows with non-null values ​​in the id2 column.

To summarize, the main difference between COUNT(Column Name) and COUNT() is that COUNT() includes null values ​​in its count, while COUNT(Column Name) excludes them. Understanding this distinction is critical for accurate data analysis and query optimization.

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