多表连接的背景、笛卡尔积的错误与如何正确地多表查询

1. 案例说明

在实际开发中，绝大多数情况都要同时查询多张有关联的表，这些同时查询的表之间通过关联字段联系起来。

例如，现在数据库中有 3 张表，分别是：员工表 employees 、部门表 departments 和位置表 locations 。员工表 employees 和部门表 departments 这两张表有一列相同的字段——“部门编号” department_id ，因此这两张表是通过“部门编号” department_id 关联起来的。而部门表 departments 和位置表 locations 这两张表有一列相同的字段——“位置编号” location_id ，因此这两张表是通过“位置编号” location_id 关联起来的。如下图所示：

2. 传统查询的弊端

【需求】查询名字为 Abel 的员工所在的城市。

【分析】

• 先在员工表 employees 中查询到 Abel 的“部门编号” department_id ；
• 再去部门表 departments 中查询该“部门编号” department_id 所对应的“位置编号” location_id ；
• 最后去位置表 locations 中查询该“部门编号” department_id 对应的城市。

# 第1步
SELECT employee_id, last_name, department_id 
FROM employees
WHERE last_name = 'Abel';
1
2
3
4

查询结果：

查询到员工 Abel 的“部门编号” department_id 为 80。

# 第2步
SELECT * 
FROM departments
WHERE department_id = 80;
1
2
3
4

查询结果：

查询到“部门编号” department_id 为 80 的“位置编号” location_id 为 2500。

# 第3步
SELECT location_id, city, state_province, country_id
FROM locations
WHERE location_id = 2500;
1
2
3
4

查询结果：

通过3次SQL查询，我们才终于查询到 Abel 所在的城市为 Oxford。

【问题】在实际的Web应用中，如果采用上述的查询方法，需要在客户端和服务器后台数据库进行三次网络通信和传输。这将大大降低查询效率，造成网络资源的浪费。必须想办法进行优化。

幸运的是，在SQL中通过多表查询操作，上面的需求可以仅通过一次查询就可以实现。进行一次查询的效率将远远高于查询三次，所以在SQL的查询优化中，后端工程师一定要牢记一条查询优化原则：

【查询原则】使用尽可能少的查询次数获得正确的查询结果。

3. 为什么要分多张表

(1)去除冗余字段，提高信息密度。

(2)去除冗余字段，提高硬盘利用率。

(3)去除冗余字段，一次硬盘IO包含的数据条目数增加，提高查询速度。

(4)当一张表被一个事务占用查询时，这张表就处于锁定状态，其他事务无法对这张表进行任何操作。分开多少表，有利于多个事务同时进行查询，更好地并发。

4. 错误的多表查询方式：笛卡尔积

【例子1】查询员工编号 employee_id 和其对应的部门名称 department_name 。

【分析】员工编号 employee_id 在员工表 employees ，部门名称 department_name 在部门表 departments 。需要跨越 2 张表进行查询。

# 错误的多表查询
SELECT employee_id, department_name
FROM employees, departments;
1
2
3

查询结果：

查询结果有 2889 条数据，这很明显是错误的。因为员工表 employees 中一共才只有 107 条数据，按理说应该返回 107 条数据才对。2889 条记录是如何产生的呢？

仔细看查询结果表不难发现，员工编号 employee_id 为 100 的员工对应的部门名称 department_name 有好多个，仿佛他在所有部门都工作一样。为了验证一下这个猜想，不妨查询一下部门表 departments 一共有多少条数据：

SELECT * 
FROM departments;
1
2

查询结果：

部门表 departments 一共有 27 条数据，不难验证：
$$107\times 27=2889$$
因此，结论就是，上述错误的多表查询方式，就是把每一个员工 (共107个) 都与所有的部门名称 (共27个) 都匹配了一次。在SQL中，出现这样的错误称为出现笛卡尔积错误。

【注意】笛卡尔积本身没有错误，笛卡尔积是一种数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

2.笛卡尔积与交叉连接 CROSS JOIN

在SQL中，笛卡尔积也称为交叉连接，用关键字 CROSS JOIN 表示。如下代码所示：

SELECT employee_id, department_name
FROM employees CROSS JOIN departments;
1
2

查询结果：

可以看到，交叉连接 CROSS JOIN 与笛卡尔积是等价的。

5. 正确的多表查询

1.出现笛卡尔积错误的原因

上述例子中出现笛卡尔积错误的原因是：没有指明多表的连接条件。

2.多表查询的正确方式

继续看上述的例子：查询员工编号 employee_id 和其对应的部门名称 department_name 。正确的查询方式是加上连接条件 ：

SELECT employee_id, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;
1
2
3

其中，着重号 ` 可以省略。

查询结果：

这里返回了 106 条记录，还少了 1 个人。原因是在员工表 employees 中，有一个员工的部门编号为 (NULL) 。

【例子2：三表查询】查询员工的员工编号 employee_id 、 姓名 last_name 、部门名称 department_name 和所在城市 city 。

【分析】这个需求需要 3 张表共同查询。

SELECT emp.`employee_id`, emp.`last_name`, dep.`department_name`, loc.`city`
FROM employees emp, departments dep, locations loc
WHERE emp.`department_id` = dep.`department_id` AND dep.`location_id` = loc.`location_id`;
1
2
3

查询结果：

==【总结】如果有 $n$ 个表需要查询，则至少需要 $n-1$ 个连接条件。==否则肯定会出现笛卡尔积的错误。