mysql left join错误写法
把left join 右边的表,在where中进行过滤,即使是使用不等于<>操作符,当右表不存关联的纪录时,会过滤掉左表的值,
总结来说,就是当把left join右边的表,写入where的条件时,能查询出纪录,只有满足下面条件
1.left join 左边和右边都存在相应的关联纪录
2.where 条件过滤掉left join 左边和右边表设置的条件
有一行纪录
SELECT t1.*,t2.name
from tableA t1
left outer join
tableB t2
on t1.id=t2.id
where t1.id='123'
没有纪录
select * from tableB where id = '123'
错误的写法:没有纪录
SELECT t1.*,t2.name
from tableA t1
left outer join
tableB t2
on t1.id=t2.id
where t1.id='123' and t2.type <> 'man'
正确的写法:有一行纪录
SELECT t1.*,t2.name
from tableA t1
left outer join
(select id,name from tableB where type <> 'man') t2 on t1.id=t2.id
on t1.id=t2.id
转载于:https://my.oschina.net/forrest420/blog/667106