有两张表,部门表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