MySQL的七种join

详细图文教程参见：

https://www.cnblogs.com/dinglinyong/p/6656315.html

https://www.cnblogs.com/fudashi/p/7491039.html

#1 内连接

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

#2 左外连接

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

#3 右外连接

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

#4 左连接

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

#5 右连接

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

#6 全连接

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

#7 两张表中都没有出现的数据集

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

-