【MySQL】九、表的内外连接

前言

​ 在 mysql 中，内连接（inner join）和外连接（left jon、right join 和 full join）是用于将多个表中的数据进行关联的操作。

​ 它们之间的区别其实很简单，内连接只返回满足连接条件的行，而 外连接则可以返回不满足连接条件的行，并且根据不同的外连接类型，返回不同的结果集。

Ⅰ. 内连接

​ 内连接（inner join）实际上就 等价于利用 where 子句对两种表形成的笛卡儿积进行筛选，也就是说，之前我们在学复合查询的时候，用的都是内连接，这也是在开发过程中使用的最多的连接查询。

​ 内连接是通过匹配两个表之间的共同值来返回结果集。只有在两个表中都存在匹配的行时，才会返回这些行。内连接只返回满足连接条件的行，其他不满足条件的行将被排除在结果集之外。

​ 其语法如下所示：

select 字段 from 表1 inner join 表2 on 连接条件 [and 其他条件];

​ 下面不废话，直接通过案例来学习！

案例：显示SMITH的名字和部门名称

​ 我们先用之前的 where 子句对两张表进行笛卡尔积：

mysql> select ename, dname from emp, dept where emp.deptno<=>dept.deptno and ename<=>'SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

​ 接着我们再用内连接的语法来查询一下：

mysql> select ename, dname from emp inner join dept on emp.deptno<=>dept.deptno and ename<=>'SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

​ 可以看到效果其实是一样的，只不过其实我们 更推荐使用内连接的语法，因为这样子的话可读性会提高，可以让 where 子句去专门筛选条件，而让内连接的工作交给 inner join 语法来完成！

​ 比如说下面这样子，将筛选名字的工作交给 where 来解决，而内连接还是用 inner join 语法来解决，达到直观的分离作用！

mysql> select ename, dname from emp inner join dept on emp.deptno<=>dept.deptno where ename<=>'SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

Ⅱ. 外连接

​ 外连接（left jon、right join 和 full join）则允许返回不满足连接条件的行。外连接根据连接条件从一个表中选择所有的行，并从另一个表中选择满足条件的行。如果在另一个表中没有匹配的行，则返回 NULL 值。

​ 下面我们先给出一个案例表，用于待会测试左外连接和右外连接的区别的：

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

1、左外连接

​ 左外连接（left join）返回左表中的所有行，以及右表中满足连接条件的行。如果在右表中没有匹配的行，则返回 NULL 值。简单地说，左侧的表完全显示我们就说是左外连接。

​ 其语法如下所示：

select 字段名 from 表名1 left join 表名2 on 连接条件;

​ 可以看到其实就是将内连接的 inner 改成了 left 以及下面右外连接的 right 罢了！

​ 其实我们也可以不加 left 或者 right，这就变成了全外连接（full join），不过我们这里就不介绍全外连接了，因为比较简单，懂了左外连接和右外连接自然就明白全外连接了！

案例：查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

​ 因为学生的个人信息和成绩分别在两张表中，那么肯定就需要进行连接，而因为题目要求如果没有成绩，依然要显示个人信息，那么使用的就是外连接，就不会说没有成绩，然后个人信息也没了！

​ 这里我们选用左连接来做测试，其中个人信息表肯定是左表，而成绩表就作为右表：

mysql> select stu.id, name, grade from stu left join exam on stu.id<=>exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | jack |    56 |
|    2 | tom  |    76 |
|    3 | kity |  NULL |
|    4 | nono |  NULL |
+------+------+-------+
4 rows in set (0.00 sec)

​ 结果是符合预期的，如果不存在成绩，可能是因为没去考试，但是个人信息还是显示出来了！

2、右外连接

​ 右外连接（right join）则返回右表中的所有行，以及左表中满足连接条件的行。如果在左表中没有匹配的行，则返回 NULL 值。简单地说，右侧的表完全显示我们就说是右外连接。

​ 其实我们也可以不用右外连接的，因为可以使用左外连接，然后将两个表的顺序一换，其实就是右外连接了，但是 mysql 还是提供了右外连接的语法，那么我们还是了解一下！

​ 其语法如下所示：

select 字段名 from 表名1 right join 表名2 on 连接条件;

案例：对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来

​ 因为这里提到成绩都要显示出来，那么就是用 stu 表去 right join stu 表，下面我们用右外连接来测试一下：

mysql> select stu.id, name, grade from stu right join exam on stu.id<=>exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | jack |    56 |
|    2 | tom  |    76 |
| NULL | NULL |     8 |
+------+------+-------+
3 rows in set (0.00 sec)

练习：列出部门名称和这些部门的员工信息，同时列出没有员工的部门

​ 题目说到同时列出没有员工的部门，也就是说，部门表是一定要显示出来信息的，但是员工就不需要！

​ 下面我们分别用左右外连接来做一下这个练习：

-- 左外连接
mysql> select dept.dname, emp.* from dept left join emp on dept.deptno<=>emp.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| RESEARCH   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| SALES      | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| SALES      | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| RESEARCH   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| SALES      | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| SALES      | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| ACCOUNTING | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| RESEARCH   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| ACCOUNTING | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| SALES      | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| RESEARCH   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| SALES      | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| RESEARCH   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| ACCOUNTING | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
| OPERATIONS |   NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)-- 右外连接
mysql> select dept.dname, emp.* from emp right join dept on dept.deptno<=>emp.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| RESEARCH   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| SALES      | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| SALES      | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| RESEARCH   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| SALES      | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| SALES      | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| ACCOUNTING | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| RESEARCH   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| ACCOUNTING | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| SALES      | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| RESEARCH   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| SALES      | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| RESEARCH   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| ACCOUNTING | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
| OPERATIONS |   NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.00 sec)

​ 可以看到 OPERATIONS 部门就没有员工！