COUNT(*) vs. COUNT(column_name): What\'s the Difference and When Should You Use Each?

Understanding the Differences Between COUNT(*) and COUNT(column_name)

When working with SQL queries, it's essential to grasp the distinction between COUNT(*) and COUNT(column_name) to ensure accurate results.

COUNT(*)

COUNT(*) counts all rows in the result set, regardless of whether they contain NULL values. This is the most inclusive count, as it considers every row that meets the query criteria.

COUNT(column_name)

COUNT(column_name) counts only those rows where the specified column is not NULL. This function ignores rows with missing values for the given column.

Performance Implications

COUNT() can be more computationally intensive than COUNT(column_name) when there are many NULL values in the table. Since COUNT() must check each row regardless, it may perform slower in such scenarios.

Alternative to COUNT(*)

COUNT(1) is an alternative to COUNT(*) that also returns the total number of rows. However, it always evaluates to a non-NULL value, regardless of other column values.

Experimental Demonstration

To illustrate the difference in results, consider the following table and query:

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
    COUNT(*) AS a,
    COUNT(x) AS b,
    COUNT(1) AS c
FROM table1;

Result:

a   b   c
3   2   3

As you can see, COUNT() returns 3 as it counts all rows, while COUNT(x) returns 2, excluding the NULL value row. COUNT(1), being indistinguishable from COUNT(), also returns 3.

The above is the detailed content of COUNT(*) vs. COUNT(column_name): What's the Difference and When Should You Use Each?. For more information, please follow other related articles on the PHP Chinese website!