COUNT(*) vs. COUNT(column_name): What\'s the Difference in MySQL?

Understanding the Difference between COUNT(*) and COUNT(column_name)

MySQL provides two count functions: COUNT(*) and COUNT(column_name). Both functions count the number of rows in a result set, but they differ in their handling of NULL values.

COUNT(*)

• Counts all rows in the result set, including rows with NULL values in any column.
• Syntax: COUNT(*)

COUNT(column_name)

• Counts only those rows where the specified column (column_name) is NOT NULL.
• Syntax: COUNT(column_name)

Key Differences

Feature	COUNT(*)	COUNT(column_name)
NULL Values	Includes rows with NULL values	Excludes rows with NULL values
Performance	May be slower in some cases even if the column has no NULL values	Generally slower due to the need to check each value

COUNT(1)

COUNT(1) is equivalent to COUNT(*). This is because 1 can never be NULL. Hence, it also counts all rows in the result set.

Example

Consider the following table named "table1":

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);

Now, let's compare the results of the three count functions:

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() counts all three rows, regardless of the NULL value in the "x" column. COUNT(x) excludes the row with the NULL value, resulting in a count of 2. COUNT(1) yields the same result as COUNT() since 1 cannot be NULL.

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