Does `COUNT(*)` Always Return a Value, Even with Zero Matches?

Is

*`COUNT()` always guaranteed result, even if there is no match? **

It is important to understand the behavior of COUNT(*) when using queries like the following:

<code class="language-sql">SELECT COUNT(*) as num FROM table WHERE x = 'y'</code>

Query explanation

This query counts the number of rows in the table table where the x column is equal to y.

*Answer: Yes, `COUNT()` always returns a result**

Unlike other aggregate functions such as MAX, SUM, or MIN, which return a NULL value when no records match the condition, COUNT(*) always returns a result.

*Cause: `COUNT()` counts NULL values**

The reason for this behavior is that COUNT(*) evaluates all rows in the table, including rows with NULL values. Therefore, COUNT(*) always returns 0 even if no row matches the condition in the WHERE clause.

Additional notes:

• Some databases may require using COUNT(*) instead of COUNT() to ensure NULL values ​​are calculated.
• Adding a GROUP BY clause to the query will cause COUNT(*) to return zero rows if there are no matching groups.

Exceptions

There are rare exceptions to this rule, for example:

• The database is configured to return NULL or an error for aggregate functions that have no matching rows.
• Corrupted or invalid data prevents queries from being processed correctly.

The above is the detailed content of Does `COUNT(*)` Always Return a Value, Even with Zero Matches?. For more information, please follow other related articles on the PHP Chinese website!