mysql表连接方式

有两张表，部门表tb_dept，员工表tb_emp
部门表tb_dept，

员工表tb_emp

自然连接

默认两表相同的字段名连接，删除重复列

select *
from tb_dept as d
         natural join tb_emp as e

内连接

内连接与自然连接得到的结果的差别就是不会删除重复列

显示内连接

select *
from tb_dept as d
         inner join tb_emp as e
         on d.dno=e.dno

隐式内连接

select *
from tb_dept d ,tb_emp e
where     d.dno=e.dno

join连接

select *
from tb_dept as d
         join tb_emp as e
         on d.dno=e.dno

显示内连接，隐式内连接，join … on连接结果是一样的

外连接

左外链接

包含左边表的全部行

select *
from tb_dept as d
         left join tb_emp as e
         on d.dno=e.dno

左反连接

查询出左表独有的数据

select *
from tb_dept as d
         left join tb_emp as e
         on d.dno=e.dno
where e.dno is null

右外连接

和左外连接差不多，只不过是包含右边表的全部行

select *
from tb_dept as d
         right join tb_emp as e
         on d.dno=e.dno

右反连接

和左外连接差不多，只不过是查询出右表独有的数据

select *
from tb_dept as d
         right join tb_emp as e
         on d.dno=e.dno

全连接

查询出左右两表的所有数据

可以用外连接来实现全连接

select * from tb_dept as d left join tb_emp as e on d.dno = e.dno
union  
select * from tb_dept as d right join tb_emp as e on d.dno = e.dno

全外连接

左右表的共有数据之外的数据查询

可以用左右反连接来实现全反连接

select * from tb_dept as d left join tb_emp as e on d.dno = e.dno where e.dno is null
union  
select * from tb_dept as d right join tb_emp as e on d.dno = e.dno where e.dno is null