MySQL Left Join 不返回连接表的空值 [英] MySQL Left Join not returning null values for joined table

问题描述

请帮助我执行以下连接两个表(A 和 B)的 MySQL 查询:

Please help me with the following MySQL query, which joins two tables (A and B):

SELECT * from A
left join B on A.sid = B.sid
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)
AND (rYear = 2011 or rYear is null)

roleCode 是表 A 中的一个字段，rYear 是表 B 中的一个字段

roleCode is a field in table A and rYear is a field in table B

结果集与预期不符.只返回了 185 行，但 A 表中有 629 行符合 where 条件.表 B 中没有匹配行的行不应该返回其 B 字段的空值吗?

The result set is not as expected. Only 185 rows are returned, but there are 629 rows in table A that match the where condition. Shouldn't the rows without a matching row in table B be returned with null values for their B fields?

推荐答案

您不应在 WHERE 子句中指定 rYear.这些限制了您加入后的结果.您应该在 ON 子句中指定 rYear 以从表 B 中取回带有 NULL 的记录.

You should not specify rYear in a WHERE clause. Those limit your results after the join. You should specify rYear in an ON clause to get back records with NULL from table B.

SELECT * from A
left join B 
on A.sid = B.sid 
AND (rYear = 2011 or rYear is null)
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)

这篇关于MySQL Left Join 不返回连接表的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！