MySQL左联接不为联接表返回空值 [英] MySQL Left Join not returning null values for joined table
问题描述
请使用以下MySQL查询帮助我,该查询连接了两个表(A和B):
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左联接不为联接表返回空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!