基础篇6、多表查询

---

尚硅谷MySQL数据库教程-讲师：宋红康

我们缺乏的不是知识，而是学而不厌的态度

---

1、一个案例引发的多表连接

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1.1、案例说明

从多个表中获取数据：

employees 表获取 employee_id 和 department_id 字段，departments 表获取 department_name 字段。

#案例：查询员工的姓名及其部门名称
SELECT last_name, 
       department_name
FROM employees, departments;
1
2
3
4

查询结果：

mysql> SELECT last_name, department_name FROM employees, departments;
+-------------+----------------------+
| last_name   | department_name      |
+-------------+----------------------+
| King        | Payroll              |
| King        | Recruiting           |
| King        | Retail Sales         |
| King        | Government Sales     |
| King        | IT Helpdesk          |
| King        | NOC                  |
| King        | IT Support           |
| King        | Operations           |
| King        | Contracting          |
| King        | Construction         |
| King        | Manufacturing        |
| King        | Benefits             |
| King        | Shareholder Services |
| King        | Control And Credit   |
| King        | Corporate Tax        |
| King        | Treasury             |
| King        | Accounting           |
| King        | Finance              |
| King        | Executive            |
| King        | Sales                |
| King        | Public Relations     |
| King        | IT                   |
| King        | Shipping             |
| King        | Human Resources      |
| King        | Purchasing           |
| King        | Marketing            |
| King        | Administration       |
......
| Gietz       | Administration       |
+-------------+----------------------+
2889 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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

分析错误情况：

mysql> SELECT COUNT(employee_id) FROM employees;
+--------------------+
| COUNT(employee_id) |
+--------------------+
|                107 |
+--------------------+
1 row in set (0.01 sec)
#输出107行

mysql> SELECT COUNT(department_id)FROM departments;
+----------------------+
| COUNT(department_id) |
+----------------------+
|                   27 |
+----------------------+
1 row in set (0.00 sec)
#输出27行

mysql> SELECT 107*27 FROM dual;
+--------+
| 107*27 |
+--------+
|   2889 |
+--------+
1 row in set (0.00 sec)
#输出2889
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

1.2、笛卡尔积（或交叉连接）的理解

笛卡尔乘积是一个数学运算。

假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。

组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。

在 SQL99 中也是使用 CROSS JOIN表示交叉连接。

它的作用就是可以把任意表进行连接，即使这两张表不相关。

在MySQL中如下情况会出现笛卡尔积：

#查询员工姓名和所在部门名称
SELECT last_name, department_name FROM employees, departments;
SELECT last_name, department_name FROM employees CROSS JOIN departments;
SELECT last_name, department_name FROM employees INNER JOIN departments;
SELECT last_name, department_name FROM employees JOIN departments;
1
2
3
4
5

1.3、案例分析与问题解决

• 笛卡尔积的错误会在下面条件下产生：
  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接
• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
• 加入连接条件后，查询语法：

#在 WHERE子句中写入连接条件。
SELECT table1.column, 
       table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件
1
2
3
4
5

#案例：查询员工的姓名及其部门名称
mysql> SELECT last_name, department_name FROM employees JOIN departments WHERE employees.department_id = departments.department_id;
+-------------+------------------+
| last_name   | department_name  |
+-------------+------------------+
| Whalen      | Administration   |
| Hartstein   | Marketing        |
| Fay         | Marketing        |
| Raphaely    | Purchasing       |
......
| Gietz       | Accounting       |
+-------------+------------------+
106 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

• 在表中有相同列时，在列名之前加上表名前缀。

2、多表查询分类讲解

2.1、等值连接 vs 非等值连接

2.1.1、等值连接

SELECT employees.employee_id, 
       employees.last_name,
       employees.department_id, 
       departments.department_id,
       departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
1
2
3
4
5
6
7