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

查看:1254
本文介绍了MySQL左联接不为联接表返回空值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

请使用以下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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆