【MySQL】十六、外连接（左连接，右连接）_左外连接和右外连接详解

1. 什么是外连接，和内连接有什么区别？

内连接： 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来。A和B两张表没有主付之分，两张表是平等的。

外连接： 假设A和B表进行连接，使用外连接的话，A，B两张表中有一张是主表，一张是副表，主要查询主表中的数据，捎带着查询副表。当副表中数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的主要特点： 主表中的数据无条件的全部查询出来。

2. 外连接的分类

左外连接（左连接）：表示左边的这张表是主表；

右外连接（右连接）：表示右边的这张表是主表；

左连接有有连接的写法，右连接也会有对应的左连接的写法。

2.1 案例：找出每个员工的上级领导（所有员工必须查询出来）

在上一篇博客中，我们使用了内连接的自连接， 找出了每个员工的上级领导，但是在查询结果中，我们发现有些员工并没有上级领导，在查询结果中没有显示，这是因为有些员工的mgr（上级领导）为NULL，这与表中的任何empno（员工编号）都匹配不到。

再次，我们在回忆一下该案例的sql查询语句：

select a.ename as '员工名', b.ename as '领导名'
from emp a 
join emp b
on a.mgr = b.empno;
1
2
3
4

改成左外连接：

select a.ename as '员工名', b.ename  as '领导名'
from emp a 
left outer join emp b // outer 可以省略
on a.mgr = b.empno;
1
2
3
4

这里，左边的表 a 作为了主表，b作为了副表

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

改成右外连接：

select a.ename as '员工名', b.ename  as '领导名'
from emp b 
right outer join emp a // outer 可以省略
on a.mgr = b.empno;
1
2
3
4

查询结果与上面一致。

区分内连接和外连接的主要区别不是inner和outer关键字，因为这两个关键字可以省略，带有这两个关键之只是为了保证可读性更好！

区别内连接和外连接的主要区别是看有没有left和right关关键字，有这个两个关键字就是外连接，否则就是内连接！

在以后的时间开发中，外连接使用的最多！因为内连接查询出的数据会丢失一部分！

2.2 案例：找出哪个部门没有员工？

select * from emp;
1

emp 表中存储这部门编号deptno

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

select * from dept;
1

dept 表中存储着deptno（部门编号），dname（部门名称）

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

这里，我们可以使用外连接，以表dept为主表。

select d.deptno, e.ename, d.dname, d.loc 
from dept d 
left join emp e 
on d.deptno = e.deptno;
1
2
3
4

查询结果：

+--------+--------+------------+----------+
| deptno | ename  | dname      | loc      |
+--------+--------+------------+----------+
|     20 | SMITH  | RESEARCH   | DALLAS   |
|     30 | ALLEN  | SALES      | CHICAGO  |
|     30 | WARD   | SALES      | CHICAGO  |
|     20 | JONES  | RESEARCH   | DALLAS   |
|     30 | MARTIN | SALES      | CHICAGO  |
|     30 | BLAKE  | SALES      | CHICAGO  |
|     10 | CLARK  | ACCOUNTING | NEW YORK |
|     20 | SCOTT  | RESEARCH   | DALLAS   |
|     10 | KING   | ACCOUNTING | NEW YORK |
|     30 | TURNER | SALES      | CHICAGO  |
|     20 | ADAMS  | RESEARCH   | DALLAS   |
|     30 | JAMES  | SALES      | CHICAGO  |
|     20 | FORD   | RESEARCH   | DALLAS   |
|     10 | MILLER | ACCOUNTING | NEW YORK |
|     40 | NULL   | OPERATIONS | BOSTON   |
+--------+--------+------------+----------+
15 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

上面查询处了所有结果，最后我们可以使用where关键字进行条件过滤：

select d.deptno, e.ename, d.dname, d.loc // 这里包含了d中所有字段，我们可以也可以使用d.*表示
from dept d
left join emp e 
on d.deptno = e.deptno 
where ename is null;
1
2
3
4
5

+--------+-------+------------+--------+
| deptno | ename | dname      | loc    |
+--------+-------+------------+--------+
|     40 | NULL  | OPERATIONS | BOSTON |
+--------+-------+------------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6