MySQL中的七种JOIN实现

JOIN的七钟实现分为：

 1.A，B表共有

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

 2.A,B两表共有+A的独有：

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

3.A,B两表共有+B的独有：

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

 4.A的独有：

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

 5.B的独有:

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

6.A，B全有：

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

 7.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;