数据库的高级查询六：表连接查询：外连接（左外连接，右外连接，UNION关键字，连接中ON与WHERE的不同）

外连接

目录

一：外连接引入：

二：左外连接，右外连接

三：外连接案例

案例1：查询每个部门的名称和部门的人数、

案例2：查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替：UNION关键字

案例3：查询每名员工的编号，姓名，部门，月薪，工资等级，上司编号，上司姓名，上司部门（这个例子有一个非常重要的点）

四：外连接注意事项：条件写在ON子句中，写在WHERE子句中的不同

1.内连接只保留符合条件的记录，所以查询条件写在ON子句中，还是写在WHERE子句中，效果是一样的；

2.但在外连接中，查询条件写在ON子句中，还是写在WHERE子句中，效果就不一样了；

---

一：外连接引入：

如下：

● t_emp在左侧，称为左表；t_dept在右侧，称为右表；；；

● LEFT JOIN是，在左表和右表连接的时候，保留左表的记录；

● 如果右表有符合连接条件的记录，就正常连接；如果右表没有符合连接条件的记录，右表就出null值去跟左表连接；

如：案例如下

---

二：左外连接，右外连接

外连接包括：左外连接和右外连接。

如下面两种结果是一样的： 

---

三：外连接案例

案例1：查询每个部门的名称和部门的人数、

我的做法：

SELECT COUNT(*),d.deptno
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno
GROUP BY d.deptno;

标准做法：

SELECT d.dname,COUNT(e.deptno),d.deptno
FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno
GROUP BY d.deptno;

---

案例2：查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替：UNION关键字

我的做法：

SELECT COUNT(e.deptno),d.dname
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptno=d.deptno
GROUP BY d.dname;

改进后的我的做法：

SELECT d.dname,d.deptno,COUNT(*)
FROM (SELECT deptno noo,COUNT(*) FROM t_emp GROUP BY deptno) e LEFT JOIN t_dept d ON e.noo=d.deptno
GROUP BY d.dname;

标准做法：使用UNION关键字：UNION关键字把多个查询语句的结果集进行合并（当然会排出重复部分的内容）！！！！！！！！！！！

UNION合并不同的查询语句的结果集，这些不同的结果集需要字段的名称相同，字段的数量也需要相同；只有这样才能合并。

因为，员工表中的陈浩没有部门，部门表中的OPERATIONS没有员工，为了两个都能保留下来，需要分别左右连接，，

然后，使用UINON关键字，将左右连接的结果集进行合并。

---

案例3：查询每名员工的编号，姓名，部门，月薪，工资等级，上司编号，上司姓名，上司部门（这个例子有一个非常重要的点）

我的解决方案：核心就是各种表连接，（心里想着先笛卡尔积然后筛选的过程）；缺点：一连到底，有点乱，模块性不强。。。

SELECT e1.empno,e1.ename,d1.dname,(e1.sal+IFNULL(e1.comm,0)),s1.grade,FLOOR(DATEDIFF(NOW(),e1.hiredate)/365),e1.mgr,e2.ename,d2.dname
FROM t_emp e1 LEFT JOIN t_dept d1 ON e1.deptno=d1.deptno 
JOIN t_salgrade s1 ON (e1.sal+IFNULL(e1.comm,0)) BETWEEN s1.losal AND s1.hisal 
LEFT JOIN t_emp e2 ON e1.mgr=e2.empno 
LEFT JOIN t_dept d2 ON e2.deptno=d2.deptno;

标准做法：“模块化”处理了，更清晰

SELECT e.empno,e.ename,d.dname,e.sal+IFNULL(e.comm,0),s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate)/365),t.empno,t.ename,t.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT e1.empno,e1.ename,d1.dname FROM t_emp e1 JOIN t_dept d1 ON e1.deptno=d1.deptno) t 
ON e.mgr=t.empno;

标准做法中需要注意的地方：十分重要的一个点，，，知道可以这样做以后，以后在做表连接的时候可以更加灵活和模块化了，，能懂，OK！！！！！！！！！

---

四：外连接注意事项：条件写在ON子句中，写在WHERE子句中的不同

1.内连接只保留符合条件的记录，所以查询条件写在ON子句中，还是写在WHERE子句中，效果是一样的；

2.但在外连接中，查询条件写在ON子句中，还是写在WHERE子句中，效果就不一样了；

在外连接中，条件写在WHERE子句中，不符合条件的记录是会被过滤掉的；；；（还好，可以勉强理解，毕竟ON子句是依附在LEFT JOIN或者RIGHT JOIN或者JOIN子句上的，其本身就是JOIN子句的附属品；；；；但是WHERE子句是独立的。）

---

注：（20210406）经过实测，外连接必须要使用ON，如果不使用ON会报错！！！！！！或许外连接不使用ON，会让MySQL产生迷惑，，，