SQL join 知识点

SQL join 知识点

本文内容摘自于某培训机构讲义

A、B两表共有

select * from t_emp a inner join t_dept b on a.deptId = b.id;

A、B两表共有+A的独有

 select * from t_emp a left join t_dept b on a.deptId = b.id;

A、B两表共有+B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id;

A的独有

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

B的独有

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

AB全有

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join

这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id

UNION

SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

A的独有+B的独有

SELECT  * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL

UNION

SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;