SQL语句表连接之外连接（左外，右外）

# 外连接示例。左外连接时，左表所有记录保持，右表无法匹配时出NULL值匹配
SELECT e.empno,e.ename,d.dname FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno;
# 查询每个部门的名称和人数
SELECT d.dname,COUNT(e.deptno) FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno GROUP BY d.deptno;
# 查询每个部门的名称和人数,如果临时员工，部门名称用NULL代替
(SELECT d.dname,COUNT(e.deptno) FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno GROUP BY d.deptno) UNION
(SELECT d.dname,COUNT(*) FROM t_dept d RIGHT JOIN t_emp e ON d.deptno=e.deptno GROUP BY e.deptno);
# 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门
SELECT e.empno,e.ename,d.dname,e.sal+IFNULL(e.comm,0),s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate)/365),z.mgrno,z.mname,z.mdname
FROM t_emp e LEFT JOIN t_dept d ON d.deptno=e.deptno 
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal 
LEFT JOIN (SELECT e.empno mgrno,e.ename mname,d.dname mdname FROM t_emp e JOIN t_dept d ON d.deptno=e.deptno) z ON e.mgr=z.mgrno;
# 外链接中ON会保留左表全部，WHERE按条件筛选
SELECT e.empno,e.ename,d.dname FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno WHERE e.deptno=10;