MySQL 基础 ———— 连接查询

引言

本篇文章承接《数据库与SQL语句》专栏，进入DQL的重要环节，可以说，这一部分的内容应该占据SQL语言的大部分使用场景。

本篇的连接查询知识，和后面的一些重要的查询知识总结，共同构成了在工作中80%的MySQL应用场景。应该算是基础且重要的部分。

同时，我还希望能够通过更加简洁的语言总结和归纳出从需求快速定位SQL模板的口诀，比如有一个非常复杂的查询需求，那么如何通过有效的思考路径快速写出准确无误的SQL语句，也将是查询语句知识总结的重点！

一、笛卡尔积

首先，多表存储的目的是为了节省存储空间，避免不必要的重复数据存储于多个表中，同时便于维护。因此往往会通过“相同字段”相互关联的方式设计数据库表，这种关联是人为定义的，并不需要MySQL来进行某种支持。

基于此，在数学中有个叫做笛卡尔积的数学概念，它指的是一个集合中的元素和另一个集合中的元素分别匹配组成不同的条目，从而构成更大的集合。

在多表查询的时候，如何完全不加任何条件，只是单纯的将两表数据进行查询，就会出现笛卡尔乘积现象，即一张表中的所有数据去逐条匹配另一张表中的所有数据，其最终结果的记录条数一定是两表的记录数的乘积。比如：

SELECT * FROM student, class 

上述SQL语句会将两个表的所有记录全部匹配。

二、连接查询的分类

SQL标准根据不同的年份，分别推出了两套标准：SQL92 和 SQL 99。

SQL92，1992年推出，它包含的连接有：

内连接（等值、非等值、自连接）

外连接（废弃）

SQL99，1999年推出，它包含的连接有：

交叉连接（笛卡尔积）

自然连接（不推荐）

内连接（等值、非等值、自连接）

外连接（最常用左外连，不常用右外连，MySQL不支持全外连）

各连接查询结果示意图，红色部分代表查询结果：

2.1 SQL92等值连接

等值连接是内连接的一种。是基于笛卡尔积实现的一种最基本的多表查询方式。如：

SELECT * FROM emp e, dept d WHERE e.`dept_id` = d.`id`;

其特点是，先通过笛卡尔积将两表相乘，然后通过筛选条件进行等值筛选。

但通过等值筛选最好指定具体的查询列表，否则会将含义重复的列都查询出来，如上图中的 dept_id 和 id 都代表部门 id。查询列表的字段可以不指定表名，但效率低，另外注意，如果两表某个字段名相同，必须指定表名。形式是“表名.字段名”。

2.2 SQL92非等值连接

非等值连接也是内连接的一种。同样基于笛卡尔积。如，有 emp （左）和 salary_grade （薪水级别，右）表：

筛选薪水在 B 级别以上的员工：

SELECT 
  e.*,
  s.`grade_name`,
  s.`salary` 标准 
FROM
  emp e,
  salary_grade s 
WHERE e.`salary` >= s.`salary` 
  AND s.`grade_name` = 'B' ;

非等值连接用于表与表之间没有明确的对应关系，且通常会进行一个范围的筛选的情况。

2.3 SQL92自连接

自连接也是内连接的一种，其含义是表与其自身做笛卡尔积。emp 表如下：

其中 ，manager_id 代表上级领导的 emp_id ，查询员工及其上级领导的信息：

SELECT 
  e.`emp_id` AS 员工id,
  e.`emp_name` AS 员工姓名,
  m.`emp_name` AS 上级姓名,
  m.`emp_id` AS 上级id,
  e.`salary` AS 员工薪水,
  e.`dept_id` AS 部门id 
FROM
  emp e,
  emp m 
WHERE e.`manager_id` = m.`emp_id` ;

自连接通常在表中的记录本身存在级联关系的情况下使用，如省市表、员工表等。

2.4 SQL92 外连接

SQL92标准的外连接目前已经基本废弃，简单了解即可，mysql无法执行这样的语句。

-- 左外连
SELECT * FROM emp e, dept d
WHERE e.`dept_id` = d.`id`(+);

-- 右外连
SELECT * FROM emp e, dept d
WHERE e.`dept_id`(+) = d.`id`;

2.5 SQL99 交叉连接

交叉连接是SQL99 标准下的笛卡尔积实现，采用 CROSS JOIN 关键字：

SELECT * FROM emp e CROSS JOIN dept d;

等价于：

SELECT * FROM emp e, dept d;

同时，SQL99  的连接使用 ON 关键字进行等值筛选：

SELECT 
  * 
FROM
  emp e 
  CROSS JOIN dept d 
    ON e.`dept_id` = d.`id` ;

2.6 SQL99 自然连接

自然连接关键字是 NATURAL JOIN ，会按照同名、同值字段，自动进行等值连接。但是限制较多，且经常需要配合 USING 关键字指定具体字段来使用，这里忽略介绍，不推荐使用。

2.7 SQL99 内连接

SQL99 对使用 INNER JOIN 来描述等值、非等值和自连接，其中 INNER 可以省略。连接条件必须使用 ON 来约束，下面以自连接进行举例，等值与非等值省略。（表结构见2.3 节），

SELECT 
  e.`dept_id`,
  e.`emp_name`,
  m.`emp_name`,
  m.`emp_id`,
  e.`salary`,
  e.`dept_id` 
FROM
  emp e 
  INNER JOIN emp m 
    ON e.`manager_id` = m.`emp_id` ;

2.8 SQL99 外连接（重点）

SQL99 的外连接总共分为三类：左外连接、右外连接、全外连接。使用关键字 OUTER JOIN 连接两表，其中 OUTER 可以省略。

左外联使用 LEFT JOIN ... ON ... 来关联两表，右外联使用 RIGHT JOIN ... ON ... 来关联两表，全外联使用 FULL JOIN ... ON ...

来关联两表，但是全外联 MySQL不支持，可以通过：左外连 UNION 右外连 替代。

左外连会将 JOIN 左边的表作为主表，将右边的表中与主表有关联的数据查询出来，没有关联关系的，则不查询；右外连正好相反。来看下面的两张表，还是 emp (员工表) 和 dept (部门表)：

                  

左外连：

SELECT 
  * 
FROM
  emp e 
  LEFT JOIN dept d 
    ON e.`dept_id` = d.`id` ;

emp 作为主表被全查了出来，并且将 dept 中相关联记录查询出，没有关联的两个部门：财务、市场部并没有被查询出来。

右外连：

SELECT 
  * 
FROM
  emp e 
  RIGHT JOIN dept d 
    ON e.`dept_id` = d.`id` ;

dept 作为主表自然也是全部查出，就连没有员工的两个部门：财务、市场部，也一并被查询出来。

因此，我们可以看出，一般情况下，只用 LEFT JOIN ，就可以完成两种不同的效果，只需要将表的前后位置调换即可。在实际开发当中 LEFT JOIN 也是要比 RIGHT JOIN 使用频率更多。

总结

1、笛卡尔积是实现连接查询的数学模型，它代表两表相乘。

2、SQL92语法可以查询最简单的等值、非等值和自连接，三者都被称为内连接，即两表集合的交集。

3、SQL99语法全面支持了外连接，可读性更强，其中左外连接查询是学习重点。

4、SQL99 的连接查询必须通过 ON 关键字来指定连接条件，与SQL99 的连接查询不同，将连接条件从 WHERE 子句中剥离出来是SQL 99 语法的重要标志。

鸣谢：

《SQL92&SQL99实现多表查询》

《MySQL基础系列教程》

《Mysql实现全外部连接（mysql无法使用full join的解决办法）》

《MySql（十二）Sql92和Sql99的区别》

《sql92和sql99》