我们手写的语句是这样的
而机器读取顺序是这样的
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 AB全有
MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.idUNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。
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;
