4.5 数据表的外连接

本次课程我们将继续的学习数据表的连接。因为数据表的连接是分为内连接和外连接的。内连接的语法，还有一些练习，我们都是学习到了。那么本次课程咱们就开始学习数据表的外连接语法。首先我来解释一下为什么要使用外连接这种语法。咱们首先看一条记录，比如说陈浩这条记录之前，我们是插入到员工表里边的，是给他分配了具体的部门，但是我们要做外连接的练习，那我就可以把这个部门编号给设置为null. 原先它有具体的部门编号，但是我把它部门编号给改成null。那么改成null以后，就代表说，陈浩是一名临时的人员，他没有固定的部门编制。于是问题就来了。我想查询每名员工和他部门的名称的时候，如果使用内连接这种语法，连接员工表和部门表，就会漏掉陈浩这条记录。因为我们的连接条件是员工的部门编号，等于部门表的部门编号，所以说符合这个条件的记录会保留下来。不符合这个条件的记录，会筛选掉现在陈浩这个人，他没有部门deptno值。这个值是now值，所以你用内连接查询员工表和部门表的时候，陈浩这条记录会被筛选掉的，他不会出现在结果集里边。虽然说陈浩没有部门，但他也是属于这个公司的人，所以说我们想查询公司里面每名员工和他部门名称的时候，陈浩这个记录，是要保留下来的。

1. 外连接简介

外连接和内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录 ，不符合连接条件的记录是绝对不会出现在结果集里边的，而外连接就显得很特殊了。无论是否符合连接条件，数据是都会出现在结果集里边的。

比如说我们刚才想查询每名员工的部门信息，因为陈浩这个人没有部门编号。如果用内连接去查询员工表和部门表，那么陈浩就不符合连接条件，也就不会出现在结果集里边了，查询就漏掉了一名员工。如果换做外连接去查询，sql语句就得写成下面这个样子，在from子句里边写表连接的时候，连接的还是员工表和部门表。但是这个连接的关键字从join变成了left join

外连接分为左外连接和右外连接

left join就是左外连接

select 
e.deptno,d.dname,e.ename
from t_emp e left join t_dept d on e.deptno=d.deptno

2 左连接和右连接

左外连接就是保留左表所有的记录与右表做连接，如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接

如果用右外连接 right join来实现查询部门所有员工

select
e.deptno,d.dname,e.ename
from t_dept d right join t_emp e on d.deptno=e.deptno

外连接练习1：查询每个部门名称和人数

select
d.deptno,d.dname,count(e.ename)
from t_dept d left join t_emp e on d.deptno=e.deptno
GROUP BY d.deptno

外连接练习2：查询每个部门名称和人数?如果没有部门的员工用NULL代替

这道题要用关键字去做union关键字是把多个查询语句的结果进行合并的。当然是排除那些重复部分的内容。 我们去查询的时候，就是写好一个select语句，用括号给括起来。然后你要想跟其他的结果进行合并的时候，那后面再写上查询语句。然后两个查询语句用union关键字去连接就行了。有第三个这个查询的结果集，想要合并的话，那后面再往下写就行了。所以这个union合并多少个结果集的无所谓，关键就是这些结果集的字段的数量和字段的名称必须要相同。

UNION关键字可以将多个查询语句的结果集进行合并

（查询语句）  UNION  （查询语句)  UNION (查询语句)....

(select
d.deptno,d.dname,count(e.ename)
from t_dept d left join t_emp e on d.deptno=e.deptno
GROUP BY d.deptno)
union
(select
d.deptno,d.dname,count(e.ename)
from t_dept d right join t_emp e on d.deptno=e.deptno
GROUP BY d.deptno)

外连接练习3：查询每名员工编号，姓名，部门，月薪，工资等级，工龄，上司编号，上司姓名，上司部门？

select
e.empno as "员工编号",
e.ename as "姓名",
d.dname as "部门",
sal+ifnull(comm,0) as "月薪",
s.grade as "工资等级",
floor(datediff(now(),e.hiredate)/365) as "工龄",
t.empno as "上司编号",
t.ename as "上司姓名",
t.deptno as "上司部门"
from t_emp e left join t_dept d on e.deptno=d.deptno join t_salgrade s on e.sal between s.losal and s.hisal
left join 
(select
e3.empno,d2.dname,d2.deptno,e3.ename
from t_emp e3 join t_dept d2 on e3.deptno=d2.deptno) t on e.mgr=t.empno

FLOOR():向下取整

AS:命名别名

 

3. 外连接的注意事项

内连接只保留符合条件的记录，所以查询条件写在ON 子句和WHRER子句中的效果是相同的。但是外连接里，条件是写在WHERE子句里，符合条件的记录是会被过滤掉的，而不是保留下来

比如要 查询10部门的员工编号，员工姓名，部门名称

把条件写在 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

把条件写在 on中，不符合条件的也保留下来了

select 
e.empno,e.ename,d.dname
from t_emp e left join t_dept d on e.deptno=d.deptno
and e.deptno=10