@TOC

前言

上数据库的时候遇到的比较难懂的，今天记录一下。

在实际应用场景中，往往一个事务需要查询多个数据表。通过两个表之间称之为 “外键” 的列来建立连接关系。

关于外键：mysql外键（FOREIGN KEY）简单讲解与演示

连接的使用可以使得我们能够进行一些复杂的查询，下面细?

连接的概念

连接即连接两张（或者多张）数据表，比如我们有：

1. 员工信息数据表EMP，主键为员工ID，有一字段DEPTNO（department NO）表示员工所在的部门的部门id
2. 部门信息数据表DEPT，主键为部门id（DEPTNO）

那么通过部门id（DEPTNO）作为外键，将两张表关联到一起。其中部门信息表是父表，员工信息表是子表

如果不使用连接，我们希望查询某一员工的信息，同时查询其部门的信息，正常逻辑下，我们需要做两次查询：

1. 查询员工信息
2. 根据员工信息中的部门id字段（DEPTNO），查询部门信息数据表，获取部门信息

这样查询的显然 非常沙口 开销非常大，而且复杂化了事务的逻辑，所以我们希望可以通过SQL来简化查询，即一句查两表。

内连接 INNER JOIN

内连接是较为常用的连接，其作用是获取两张表中相关匹配字段的数据，听起来有点抽象，还是拿上面的员工和部门数据表举例子：

我们获取员工的信息，只能知道其部门id，而不知道其部门名字，现在我们希望通过连接两张表，获取员工信息（名字）的同时，获取其所在部门的名称：

比如我们查询员工id为3258的员工的名字和其所在部门的名字：

SELECT ENAME, DNAME
FROM emp, dept
WHERE emp.DEPTNO=dept.DEPTNO
AND emp.EMPNO=3258;
复制代码

值得注意的是：因为通过 DEPTNO 字段对两表建立连接，所以有WHERE条件emp.DEPTNO=dept.DEPTNO。当我们使用 FROM 指定多个表的时候，连接就已经发生了。

当然也可以显式的通过 INNER JOIN 语句指定作用域，然后通过 ON 进行筛选：

SELECT emp.ENAME, dept.DNAME
FROM emp INNER JOIN dept
ON emp.DEPTNO=dept.DEPTNO
AND emp.EMPNO=3258;
复制代码

连接中的重命名

因为表的名字过于长，所以可以通过重命名的方式，来简化。直接在 FROM 子句选择数据表的时候，每个表后面加上新名字即可：

SELECT e.ENAME, d.DNAME
FROM emp e, dept d
WHERE e.DEPTNO=d.DEPTNO
AND e.EMPNO=3258;
复制代码

我 连 我 自 己

还是回到刚刚的例子，因为员工数据库中的 MGR 字段指定了该员工的上司的员工id，我们希望查询到员工的名字和其上司的名字，那么逻辑上还是两步：

1. 查询员工信息中的 MGR 字段，确定上司的员工id
2. 再次查询员工信息表，通过 MGR 找到上司的名字

注意和上述的连接有点不同。上文的连接是 先查数据表A再查数据表B，而此处的连接是 查询两次数据表A。

先查数据表A再查数据表B，我们连接AB即可
那么查询两次数据表A，我们连接AA即可，即自己连接自己。

SELECT worker.ENAME, manager.ENAME
FROM emp worker, emp manager
WHERE worker.MGR=manager.EMPNO
AND worker.EMPNO=3258;
复制代码

WHERE子句的必要性

如果在连接两张表的时候，没有通过 WHERE 子句指定关联的键（比如上文中的连接就是通过外键 DEPTNO 进行两张表的关联），那么将会返回两张表中所有元素的笛卡尔积（即自由配对结果）

比如如果表a有16行，表B有5行，那么不使用联接将导致80行输出。

外连接

刚才提到内连接是通过外键关系来确定最后的结果集合的，比如有如下的两张表：

注意到左边 dept 表中明明有 DEPTNO = 10， 20，30，40，50，可是联合之后，只存在 20，30，50 的记录，不存在 10，40 的记录。

这是因为右边emp表中不存在DEPTNO为10和40的记录，所以内连接只选择 DEPTNO互相匹配的记录。

左连接

使用左连接，那么将会获得左表中所有的字段，不论关系字段DEPTNO是否匹配。

对于左表中的某些记录，右表中不存在与之对应的记录（比如不存在DEPTNO为40的记录），那么会在结果中创建虚拟行，该虚拟行对应右表的所有字段为NULL

示例：
还是以员工信息数据表和部门信息数据表为例，有如下的两张表：

我们通过左连接，选取所有字段：

SELECT *
FROM dept LEFT JOIN emp
ON dept.DEPTNO=emp.DEPTNO;
复制代码

明明右表只有16条记录，为何多了一条？因为右表中不存在 DEPTNO=40 的记录，但是左连接的特性是选取所有的记录，不管是否匹配，所以最终结果会存在 “虚拟行”，即对应右表字段全为NULL

注：也可使用 LEFT OUTER JOIN ，效果类似

右连接

和左连接类似，返回右表中所有的记录，不管左表是否存在对应关系的记录。就不细?了。

小结

连接的引入大大方便了SQL的查询。通过两表的关系字段（比如 DEPTNO 部门id字段），可以同时查询两张表并且获得联合的查询结果。

其中连接又分为

1. 内连接 INNER JOIN
2. 外连接：左 LEFT OUTER JOIN
3. 外连接：右 RIGHT OUTER JOIN

其中内连接只返回双方都存在的所有记录，而左右连接则是分别返回左右表的所有记录，不管另一方是否存在与之对应的记录。

图片引自 菜鸟教程

习题

还是员工与部门的例子。假设有如下的表：

1

Find the name and salary of employees in Luton.

找到所有在 Luton （地名，dept表的LOC字段定义） 工作的员工的 名字 和 工资 字段。

SELECT ENAME, SAL
FROM emp, dept
WHERE emp.DEPTNO=dept.DEPTNO
AND LOC="Luton";
复制代码

2

Join the DEPT table to the EMP table and show in department number order.

将 dept 表和 emp 表连接，然后按照部门id的顺序输出

SELECT * FROM 
emp JOIN dept 
ON dept.DEPTNO=emp.DEPTNO 
ORDER BY dept.DEPTNO;
复制代码

3

List the names of all salesmen who work in SALES

列出所有在 SALES （部门名称，dept表的DNAME字段定义）工作的 销售员（岗位，emp表的JOB 字段定义）的名字

SELECT ENAME
FROM emp, dept
WHERE emp.DEPTNO=dept.DEPTNO
AND emp.JOB="SALESMAN"
AND dept.DNAME="SALES";
复制代码

4

List all departments that do not have any employees.

列出没有人的部门

这题比较难，需要使用左连接，连接部门数据表和员工数据表，找出所有部门，然后看是否出现NULL字段（即右表没有对应记录，表示没有员工）

SELECT DNAME
FROM dept LEFT JOIN emp
ON dept.DEPTNO=emp.DEPTNO
WHERE emp.DEPTNO IS NULL;
复制代码

对。就是40号部门没有人。。。。

5

For each employee whose salary exceeds his manager’s salary, list the employee’s name and salary and the manager’s name and salary.

列出所有员工的名字，工资，他上司的名字，他上司的工资。根据：这些员工的工资超过他们的上司

这题需要自己连接自己即可，需要对两个表重命名以区分
注：员工数据库中的 MGR 字段指定了该员工的上司的员工 id

SELECT worker.ENAME, worker.SAL, manager.ENAME, manager.SAL
FROM emp worker, emp manager
WHERE worker.MGR=manager.EMPNO
AND worker.SAL>manager.SAL;
复制代码

没有人的工资超过他们的上司。。。快进到吊路灯

6

List the employees who have BLAKE as their manager.

列出所有上司名字叫 BLAKE 的员工的信息

SELECT worker.*
FROM emp worker, emp manager
WHERE worker.MGR=manager.EMPNO
AND manager.ENAME="BLAKE";
复制代码