SQL LEFT JOIN 返回 0 而不是 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1037653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL LEFT JOIN return 0 rather than NULL
提问by Karl
I want to join two tables, with the number of records for each type being counted. If there are no records of that type in the left table I want a 0 to be returned, not a null.
我想连接两个表，计算每种类型的记录数。如果左表中没有该类型的记录，我希望返回 0，而不是 null。
How can I do this?
我怎样才能做到这一点？
采纳答案by cjk
Use:
用：
ISNULL(count(*), 0)
回答by kaiyasit phanmakorn
You can use "CASE"
您可以使用“案例”
SELECT T1.NAME, CASE WHEN T2.DATA IS NULL THEN 0 ELSE T2.DATA END
FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID
回答by instanceof me
ISNULL(nullable, value_if_null)for MsSQL, COALESCE(nullable1, nullable2, ..., value_if_null)for MySQL.
ISNULL(nullable, value_if_null)对于 MsSQL，COALESCE(nullable1, nullable2, ..., value_if_null)对于 MySQL。
Edit:As I'm told, COALESCEworks for both, so I'd choose that to replace NULLcolumns.
编辑：据我所知，COALESCE两者都适用，所以我会选择它来替换NULL列。
Now I think that COUNT()ing NULLvalues returns 0in MySQL too, so I agree with Rashmi. Could you show us the query and the wanted result ?
现在我认为COUNT()ingNULL值0也会在 MySQL 中返回，所以我同意 Rashmi。你能告诉我们查询和想要的结果吗？
回答by SO User
I am not sure if I have understood your exact problem, but in sqlserver on a left join, you will get a count as 0 if your query is something like this:
我不确定我是否理解了您的确切问题，但是在左连接的 sqlserver 中，如果您的查询是这样的，您将得到计数为 0：
select t1.id, count(t2.id)
from table1 t1
left outer join table2 t2
on t1.id = t2.id
group by t1.id
回答by GaZ
COALESCE is more cross-compatible than ISNULL or NVL (it works on MSSQL, Oracle, MySQL, Derby, et al.). But I am not sure about the performance differences.
COALESCE 比 ISNULL 或 NVL 更具交叉兼容性（它适用于 MSSQL、Oracle、MySQL、Derby 等）。但我不确定性能差异。
回答by RichardOD
回答by yuchen zhong
COALESCE(XXX, 0)
E.g.
例如
SELECT branch1_id, branch1_name, COALESCE(devnum, 0) FROM
branch1 as S LEFT JOIN view_inner_zj_devnum as B ON S.branch1_id = B.bid1 GROUP BY branch1_id;
That works for me.
这对我行得通。
