计算PurchaseId，而不是*，我认为这应该返回预期结果：

SELECT C.postcode,COUNT(C.PurchaseId) AS TOTAL SALES
FROM CustomerSales C OUTER LEFT JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode;

SELECT C.postcode,COUNT(*) AS TOTAL_SALES
FROM CustomerSales C JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode

union

select c.postcode,'0' as TOTAL_SALES
FROM CUSTOMERSALES C 
where c.postcode not in(select cs.postcode from 
FROM CustomerSales C JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
);

我看到的问题是，您正在执行联接，然后计算返回的记录数，因此默认情况下没有销售的记录不会在联接中结束。

联接类型在这里很重要，我认为LEFT JOIN会显示第一个表中的所有数据，即使在联接表中未找到任何数据，然后您也将对联接表中的字段进行计数，如果未找到则返回NULL

类似这样的东西：

SELECT C.postcode,COUNT(I.name) AS TOTAL SALES
FROM CustomerSales C LEFT JOIN IceCream I
ON C.icecreamID = I.id AND
WHERE I.name = "Chocolate" AND C.saleyear = "2019"
GROUP BY C.postcode;

如果要 ALL 个邮政编码，则必须对LEFT个邮政编码进行DISTINCT到CustomerSales，然后到IceCream的连接：

SELECT p.postcode,COUNT(C.PurchaseId) AS TOTAL_SALES
FROM (
  SELECT DISTINCT postcode
  FROM CustomerSales
) p LEFT JOIN CustomerSales C 
ON C.postcode = p.postcode AND C.saleyear = '2019'
LEFT JOIN IceCream I ON C.icecreamID = I.id AND I.name = 'Chocolate'
GROUP BY p.postcode;

请清楚一点，您要使用LEFT JOIN，将I上的条件移至ON子句并更改COUNT()：

SELECT C.postcode,COUNT(i.id) AS TOTAL SALES
FROM CustomerSales C LEFT JOIN
     IceCream I
     ON C.icecreamID = I.id AND
        I.name = 'Chocolate'
WHERE C.saleyear = 2019
GROUP BY C.postcode;