mysql left join ,right join ,inner join
表A lefttable,表B righttable
left join: 与right join 相类似,反之亦然。
1. select lefttable.*,righttable.* from lefttable left join righttable on lefttable.id = righttable.id and righttable.name = "xxx" where lefttable.name = "XXX";
2. select lefttable.*,righttable.* from lefttable left join righttable on lefttable.id = righttable.id and leftable.name = "xxx" where lefttable.name = "XXX";
3. select lefttable.*,righttable.* from lefttable left join righttable on lefttable.id = righttable.id and righttable.name = "xxx" where righttable.name = "XXX";
说明:
1. left join : 以 lefttable为驱动表,驱动righttable 表,即使righttable 表没有与之相匹配的,也会显示出来,只是用null代替而已。
2. and righttable.name : 这里的条件只对被驱动表有效。如果针对的是驱动表则可忽视它的存在。
3. where : 针对的是联合查询得到的结果集。不满足条件就过滤掉。
inner join
1. select lefttable.*,righttable.* from lefttable inner join righttable on lefttable.id = righttable.id and righttable.name = "xxx" where lefttable.name = "XXX";
2. select lefttable.*,righttable.* from lefttable inner join righttable on lefttable.id = righttable.id and leftable.name = "xxx" where lefttable.name = "XXX";
3. select lefttable.*,righttable.* from lefttable inner join righttable on lefttable.id = righttable.id and righttable.name = "xxx" where righttable.name = "XXX";
说明:
标红的三个地方不像left join ,right join ,所启动的作用都与where 一样的,不满足条件就过滤掉。