SUM(col 1 col 2) IF col 1 contain null-CodePudding

Input: 
Boxes table:
 -------- ---------- ------------- -------------- 
| box_id | chest_id | apple_count | orange_count |
 -------- ---------- ------------- -------------- 
| 2      | null     | 6           | 15           |
| 18     | 14       | 4           | 15           |
| 19     | 3        | 8           | 4            |
| 12     | 2        | 19          | 20           |
| 20     | 6        | 12          | 9            |
| 8      | 6        | 9           | 9            |
| 3      | 14       | 16          | 7            |
 -------- ---------- ------------- -------------- 
Chests table:
 ---------- ------------- -------------- 
| chest_id | apple_count | orange_count |
 ---------- ------------- -------------- 
| 6        | 5           | 6            |
| 14       | 20          | 10           |
| 2        | 8           | 8            |
| 3        | 19          | 4            |
| 16       | 19          | 19           |
 ---------- ------------- -------------- 
Output: 
 ------------- -------------- 
| apple_count | orange_count |
 ------------- -------------- 
| 151         | 123          |
 ------------- -------------- 
Explanation: 
box 2 has 6 apples and 15 oranges.
box 18 has 4   20 (from the chest) = 24 apples and 15   10 (from the chest) = 25 oranges.
box 19 has 8   19 (from the chest) = 27 apples and 4   4 (from the chest) = 8 oranges.
box 12 has 19   8 (from the chest) = 27 apples and 20   8 (from the chest) = 28 oranges.
box 20 has 12   5 (from the chest) = 17 apples and 9   6 (from the chest) = 15 oranges.
box 8 has 9   5 (from the chest) = 14 apples and 9   6 (from the chest) = 15 oranges.
box 3 has 16   20 (from the chest) = 36 apples and 7   10 (from the chest) = 17 oranges.
Total number of apples = 6   24   27   27   17   14   36 = 151
Total number of oranges = 15   25   8   28   15   15   17 = 123

My answer:
SELECT 
SUM(b.apple_count  c.apple_count,0) AS apple_count, # IFNULL
SUM(b.orange_count c.orange_count,0) AS orange_count
FROM Boxes b
LEFT JOIN Chests c
ON b.chest_id = c.chest_id

The expected answer:
SELECT 
SUM(b.apple_count  COALESCE(c.apple_count,0)) AS apple_count, # IFNULL
SUM(b.orange_count COALESCE(c.orange_count,0)) AS orange_count
FROM Boxes b
LEFT JOIN Chests c
ON b.chest_id = c.chest_id

My question is, why do we have to use COALESCE() OR IFNULL() for this question?

What is the difference between 0 and null when using SUM()?

I thought SUM() is supposed to ignore null values in MySQL and add 6 to apple and 15 to orange even with the null in the chest table?

CodePudding user response：

1 0 = 1

1 null = null

This is the difference.