SQL查询无匹配记录时如何让select count返回0而非null

问题根源 #

你的现有SQL存在两层问题：

1. 当reference_id = 300000009798620没有匹配的记录时，外层WHERE条件筛选后没有结果行，自然不会返回任何数据，更不会返回count为0的行
2. 子查询本身count(*)不会返回null，但如果是关联逻辑异常触发空值返回，可通过标准SQL空值处理函数包裹解决

解决方案 #

方案1：无论有没有匹配记录都返回统计值（无匹配时返回id为null、count为0的行） #

适用于需要强制返回统计值的场景，兼容所有支持标准SQL的数据库：

SELECT 
  t.Id,
  COALESCE(c.total_count, 0) AS record_count
FROM 
  (SELECT COUNT(*) AS total_count FROM table1 WHERE reference_id = 300000009798620) c
LEFT JOIN 
  (SELECT DISTINCT Id FROM table1 WHERE reference_id = 300000009798620) t
ON 1=1;

方案2：仅在有匹配记录时返回结果，保证count不为null #

如果不需要无匹配时强制返回行，仅要求返回结果中的count列永远为数字，直接用空值处理函数包裹统计逻辑即可：

SELECT DISTINCT Id,
  COALESCE(
    (SELECT count(*) FROM table1 WHERE reference_id = 300000009798620),
    0
  ) AS record_count
FROM table1
WHERE reference_id = 300000009798620;

如果你使用MySQL数据库，也可以用IFNULL替换COALESCE，二者效果完全一致。

内容的提问来源于stack exchange，提问作者osfar