MySQL | 七种JOIN

七种 join

准备两张表

1.内连接

select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

2.左连接

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

3.左表独有

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

4.右连接

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

5.右表独有

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;

6.全连接

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id

7.全连接除去交集

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
union
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;