mysql - 即使查询为 NULL，如何返回所有行

Question

我有以下代码:

SELECT t1.location as Locations,  
COUNT( t1.location ) AS Total,
tab.Responses, tab.location as tablocation 
FROM `trespondent` t1  
JOIN 
( SELECT location, COUNT( location ) AS Responses FROM  `trespondent`  
WHERE completion_status IN ('Started', 'Complete') GROUP BY location ) 
tab ON t1.location = tab.location GROUP BY t1.location

这个的输出是:

Locations   Total   Responses   tablocation
Berlin      57      43          Berlin
New York    132     118         New York

这很好。但是，我在数据库(巴黎)中有另一个“位置”，它总共有 55 个，但响应结果为 0(即 WHERE completion_status IN ('Started', 'Complete')。

我理想的输出是:

Locations   Total   Responses   tablocation
Berlin      57      43          Berlin
New York    132     118         New York
Paris       55      0           Paris

但是，由于语句产生“NULL”结果，我无法让 MySQL 返回该行，因此我很难做到。

我已经尝试使用 UNION 但我遇到了问题 - 我认为这是因为我正在使用子查询。欢迎任何和所有建议。

Answer 1

使用 LEFT JOIN 而不是 INNER JOIN 以及 COALESCE()功能如下

SELECT t1.location as Locations,  
COUNT( t1.location ) AS Total,
COALESCE(tab.Responses, 0), 
COALESCE(tab.location, t1.location) as tablocation 
FROM `trespondent` t1  
LEFT JOIN 
( SELECT location, 
COUNT( location ) AS Responses 
FROM  `trespondent`  
WHERE completion_status IN ('Started', 'Complete') 
GROUP BY location 
) tab ON t1.location = tab.location 
GROUP BY t1.location