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

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

Question

What is the difference between COUNT(column) and COUNT(*) when querying SQL tables?

Explanation

COUNT(column) counts the number of non-null values ​​in the specified column. In contrast, COUNT(*) counts all rows in a table, regardless of whether the specified column contains null values.

Example

Consider the following form:

id	name
1	John Doe
2	Jane Doe
3	NULL
4	NULL

Comparison of COUNT(name) and COUNT(*)

• COUNT(name): Excludes rows containing NULL values, resulting in 2.
• COUNT(*): Contains rows containing NULL values, resulting in 4.

NULL value handling

COUNT(column) ignores NULL values, while COUNT(*) treats them as valid entries. This behavior can affect query results, especially when applying conditions to the WHERE clause.

Additional information

Consider the following query:

SELECT name, COUNT(*)
FROM table
GROUP BY name
HAVING COUNT(*) > 2;

• Using COUNT(*), the result will contain a row of NULL values ​​and a count of NULL values.
• With COUNT(name), NULL values ​​will be excluded, so the row will not be returned in the result.

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