How does MySQL handle the SUM function?-CodePudding

SELECT 
   COUNT(id) as t,
   IF(pageId is NULL, 1, 0) as tD
FROM table_name;

t	td
0	1

SELECT 
   COUNT(id) as t,
   SUM(IF(pageId is NULL, 1, 0)) as tD
FROM table_name;

t	td
0	null

Why does the second query return null if the first query returns 1?

CodePudding user response：

The first query is not legal SQL. You're using an aggregate function (count) but have a non aggregated column (pageId). MySQL will allow this if only_full_group_by is off. You should turn it on to avoid bad habits. See https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html and https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

In the first query you asked for a count of all rows, so it has to return a row. You also asked for the value of pageId. There are no rows, so pageId is null, so pageId is null is true.

In the second query you asked to sum all the rows, but there are no rows to sum so you get null. What you're summing doesn't matter. sum(1) is also null.

If there are no matching rows, or if expr is NULL, SUM() returns NULL.

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_sum