MySQL_06：多表查询

一、多表查询讲解

1.主要笔记

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

  前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

• 多表查询存在笛卡尔积错误的问题

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

• 加入连接条件后，查询语法：

  • SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column1 = table2.column2; #连接条件
    1
    2
    3

  • 在 WHERE子句中写入连接条件。

• 正确写法：

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

• 代码示例：（包含：多表查找初始举例、笛卡尔积错误举例、表的别名、不明确的查询举例等见注释）

# 多表查询

#员工表、部门表、部门地点等
DESC employees;
DESC departments;
DESC locations;
# 查询员工名为’abel‘的人在那个城市工作
SELECT *
FROM employees
WHERE last_name='abel';

SELECT *
FROM departments
WHERE department_id=80;

SELECT *
FROM locations
WHERE location_id=2500;

# 多表查询实现

# 1. 错误的示范：出现笛卡尔积错误（每个员工都与每个部门匹配了一遍）
SELECT employee_id,department_name
FROM employees,departments#2889条数据
# from employees cross join departments #(和上面一样的效果)
# 错误原因：缺少了多表的连接条件

# 2.正确的方式：需要有连接条件

# 两个表的连接条件
WHERE employees.department_id=departments.department_id;


# 3.不明确的查询示范（下列查询会报错）
SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
# 原因：department_id在两个表里都存在，查询不明确报错ambiguous
# 改正确则改为：某表.department_id（）
# 结论：如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
# 建议：从sql优化的角度，建议多表查询时，每个字段前面都指明其所在的表
SELECT employees.employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;

# 4.可以给表起别名，在select和where中使用表的别名
# 注意：如果给表起了别名，一旦在select和where中使用表名的话必须使用别名，不能再使用原名
SELECT emp.employee_id,department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id=dept.department_id;
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

2.例题

• 练习：查询员工的employee_id，last_name,department_name,city

# 练习：查询员工的employee_id，last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id;
1
2
3
4

• 注意：如果有n个表实现多表的查询，则至少需要n-1个连接条件
• 原则上select每一个数据都指明是哪一个表的数据
• 如果给表起了别名，一旦在select和where中使用表名的话必须使用别名，不能再使用原名

3.笛卡尔积（或交叉连接）的理解

• 笛卡尔乘积是一个数学运算。假设我有两个集合 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：等值连接和非等值连接

上面的查询都是等值连接，非等值连接指条件语句不是等于

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
# where e.salary between j.lowest_sal and j.highest_sal;
WHERE e.salary>=j.lowest_sal AND e.salary<=j.highest_sal;
1
2
3
4

角度2：自连接和非自连接

背景：比如某表有一列条件为它的上级id，但是它的上级也存在于该表，这时候存在自连接

# 角度2：自连接和非自连接
SELECT * FROM employees;

# 练习：查询员工id，员工姓名及其管理者的id和姓名
SELECT 员工.employee_id,员工.last_name,上级.employee_id,上级.last_name
FROM employees 员工,employees 上级
WHERE 员工.manager_id=上级.employee_id;
1
2
3
4
5
6
7

角度3：内连接和外连接（重点）

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行 （上面写的都是内连接）

• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的

• 外连接分类

  • 左外连接
  • 右外连接
  • 满外连接

例题：查询所有员工的last_name,department_name

图一：

图二：

但是参考图一和图二可知，有一位员工是没有上级的，外连接查询则是把这位员工信息也显示出来

在sql92语法如下

# 练习：查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id =d.department_id(+); # 需要使用左外连接
# sql92语法实现外连接：使用 + （但是MySQL不支持sql92语法中外连接的写法！）
1
2
3
4
5

解释：该字段在MySQL中不适用，在SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接:Oracle 中如下

SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;
1
2
3
4
5
6
7

在sql99语法中

• sql99语法：使用join …on的方式实现多表查询，这种方式也能解决外连接的问题，MySQL是支持该方式的

⭕️：SQL99语法实现内连接

# SQL99语法实现内连接
# 1.0
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.department_id =d.department_id;
# 2.0
SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.department_id =d.department_id
JOIN locations l
ON d.location_id=l.location_id
1
2
3
4
5
6
7
8
9
10
11

如图：

⭕️：SQL99语法实现外连接

首先原则上使用99语法内连接如下（但是inner是可以省略的）

# 查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.department_id =d.department_id; 
1
2
3
4

所以外连接（用outer表示外连接，left为左，right为右）（并且语句中的outer是可以省略的，因为left和right已经确定了为外连接）

# 查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
#左外连接
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id =d.department_id; 

# 查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
# 右外连接
SELECT last_name,department_name
FROM employees e RIGTH OUTER JOIN departments d
ON e.department_id =d.department_id; 
1
2
3
4
5
6
7
8
9
10
11

⭕️：SQL99语法实现满外连接（FULL，但是MySQL不支持）

Oracle 中支持使用FULL，满外连接如下，但是mysql却不支持

SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id =d.department_id; 
1
2
3

关于mysql见如下

三、使用SQL99实现7种JOIN操作

UNION和UNION ALL的使用

• 合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
1
2
3

UNION操作符：UNION 操作符返回两个查询的结果集的并集，去除重复记录

UNION ALL操作符：UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重

注意：两者都能用的时候推荐使用union all ，因为union存在去重操作效率低

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

• UNION：会执行去重操作
• UNION ALL：不会执行去重操作

7种操作的实现

分别取名为左上图、左中图、左下图，右边以此类推，中间的叫中图，语句如下：

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3
4

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3
4

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3
4

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
1
2
3
4
5

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
1
2
3
4
5

#左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3
4
5
6
7
8
9
10

#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
1
2
3
4
5
6
7
8
9
10
11

SQL99语法新特性

附录：常用的 SQL 标准有哪些

• SQL 有两个主要的标准，分别是 SQL92 和 SQL99 。92 和 99 代表了标准提出的时间，SQL92 就是 92 年 提出的标准规范。当然除了 SQL92 和 SQL99 以外，还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、 SQL:2011 和 SQL:2016 等其他的标准。
• 这么多标准，到底该学习哪个呢？实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的 形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂， 但可读性更强。我们从这两个标准发布的页数也能看出，SQL92 的标准有 500 页，而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候，很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能，满 足日常工作的需求即可。
  L92 和 SQL99 以外，还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、 SQL:2011 和 SQL:2016 等其他的标准。
• 这么多标准，到底该学习哪个呢？实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的 形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂， 但可读性更强。我们从这两个标准发布的页数也能看出，SQL92 的标准有 500 页，而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候，很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能，满 足日常工作的需求即可。
• SQL92 和 SQL99 是经典的 SQL 标准，也分别叫做 SQL-2 和 SQL-3 标准。也正是在这两个标准发布之 后，SQL 影响力越来越大，甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言， 还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使 用。