测试环境:mysql
table a :
table b:
内连接 :inner join
select * from a inner join b on a.id=b.id;
只会匹配相同的数据
外连接:
1、left join [左外连接]==left outer join [左外连接]
select * from a left join b on a.id=b.id;
select * from a left join outer b on a.id=b.id;
左边表全部保留,右边表没有的就为null
2、right join [右外连接]==right outer join [右外连接]
select * from a right join b on a.id=b.id;
select * from a right join outer b on a.id=b.id;
右边表全部保留,左边表没有的就为null
注意:select * from a left inner join b on a.id=b.id;--------没有这种语法
全连接:full outer join
select * from a full outer join b on a.id=b.id;
全外连接返回参与连接的两个数据集合中的全部数据
注意: 全连接mysql 不支持full outer join这种语法!!!!!
mysql怎么实现全连接:union
select * from a left join b on a.id=b.id union select * from a right join b on a.id=b.id;