COUNT(column) vs. COUNT(*): When Should You Use Each SQL Count Function?

*SQL counting function: Differences and application scenarios between COUNT(column) and COUNT()**

In SQL databases, counting operations are basic and commonly used functions. However, the choice of COUNT(column) and COUNT(*) will directly affect the results. This article will dive into the key differences between these two counting functions.

COUNT(column)The function counts the number of non-NULL values ​​in the specified column. If a row contains a NULL value in that column, the row will be excluded from the count. This feature is useful when counting unique values ​​or specific data points.

On the other hand, the COUNT(*) function counts the total number of rows in the selected grouping regardless of whether the column contains NULL values. It counts both non-NULL values ​​and NULL values.

This difference becomes apparent when dealing with tables containing NULL values. For example, consider the following query:

<code class="language-sql">SELECT column_name, COUNT(column_name)
FROM table
GROUP BY column_name
HAVING COUNT(column_name) > 1;</code>

This query retrieves row groups where the value of column_name occurs more than once. However, if we replace COUNT(column_name) with COUNT(*), we run into potential problems.

With COUNT(*), the query will count all rows in each group, including those containing NULL values ​​in column_name. This may result in an extra row in the output containing NULL values ​​and their count of NULL values.

To illustrate this, consider a table containing the following data:

id	id2
NULL	NULL
1	NULL
NULL	1
1	NULL
NULL	1
1	NULL
NULL	NULL

The following query using COUNT(id) will return the correct results:

<code class="language-sql">SELECT id, COUNT(id)
FROM table
GROUP BY id
HAVING COUNT(id) > 1;</code>

Output:

id	COUNT(id)
1	3

However, if we use COUNT(*) we will get extra rows:

<code class="language-sql">SELECT id, COUNT(*)
FROM table
GROUP BY id
HAVING COUNT(*) > 1;</code>

Output:

id	COUNT(*)
1	3
NULL	2

As shown above, the result using COUNT(*) contains an extra row where id is NULL and a count of 2, indicating the number of rows containing NULL id values.

Therefore, the choice of COUNT(column) and COUNT(*) depends on the specific data and desired results. COUNT(column) is more suitable for counting non-NULL values, while COUNT(*) is used for counting all rows, including those containing NULL values.

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