多表查询

本文笔记根据【b站 尚硅谷-宋红康 MySQL 课程】整理。笔记和代码存储在我的 GitHub 库中 github.com/isKage/sql-notes。

多表查询，关联查询，指对多个表进行查询。

前提：被查询的多表之间存在联系，即存在关键字段、相同字段（例如：外键）

1 笛卡尔积

1.1 错误的查询

如果直接查询，则会出现笛卡尔积错误。

1
2
3
SELECT last_name, department_name
FROM employees, departments;
# 2889 rows
1
2
3
SELECT COUNT(last_name) FROM employees;  -- 107 rows
SELECT COUNT(department_name) FROM departments;  -- 27 rows
SELECT 107 * 27;  -- 2889

即将两个表的所有行进行了组合，将所有非重复组合一同输出。

1.2 笛卡尔积

定义：对于集合 $A,\ B$ 称由 $A,\ B$ 则称 $A$ 和 $B$ 的笛卡尔积是 $A$ 和 $B$ 的所有可能组合。

则容易得知新的集合元素个数为 $|A| \times |B|$ ，其中 $|\cdot|$ 表示元素个数。

1.3 CROSS JOIN 交叉连接

笛卡尔积也称为交叉连接，可以使用 CROSS JOIN INNER JOIN JOIN链接二表。

1
2
3
4
5
6
7
8
9
10
11
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;

不通过 WHERE 或 ON 添加条件，则会出现笛卡尔积错误

1.4 WHERE 加入链接条件

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。格式为

1
2
3
SELECT 表1.列名, 表1.列名
FROM 表1, 表2
WHERE 表1.列名1 = 表2.列名2;  -- 有效的连接条件
  • 例：根据部门 id 筛选
1
2
3
4
# WHERE 加入条件
SELECT employees.last_name, departments.department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在表中有相同列时，在列名之前加上表名前缀。【推荐使用，方便标注各表各列】

2 多表查询概念

2.1 等值连接 & 非等值连接

2.1.1 键的类型

主键 PRI & 唯一标识 UNI & 外键 MUL

在SQL中，key 是用于标识表中列的属性，常见的类型包括 PRI、MUL 和 UNI。这些属性描述了列在表中的角色和约束。以下是它们的详细区别：

  • PRI (Primary Key)：表示该列为表的主键。

值唯一，不能有重复；不为空；唯一标识；可作为唯一索引；

  • UNI (Unique Key)：表示该列具有唯一约束。

值唯一，不能有重复；允许空值；也可作为唯一索引；

  • MUL (Multiple)：表示该列是外键或普通索引。

非唯一性，值可以重复；如果该列是外键，它引用另一个表的主键或唯一键；

使用 DESC 查询表的属性

1
DESC employees;

2.1.2 等值连接

一般通过外键寻找另一个表的唯一索引（PRI 或 UNI） 

1
2
3
4
-- 根据 job_id 外键连接 jobs 表的 job_title
SELECT employees.last_name, jobs.job_title, employees.job_id
FROM employees, jobs
WHERE employees.job_id = jobs.job_id;
  • 多个条件 AND 连接
1
2
3
SELECT employees.last_name, departments.department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id AND departments.department_name = 'Shipping';
  • 多个表中有相同列时，必须在列名之前加上表名前缀

【推荐使用，方便标注各表各列，提高查询效率】

  • 使用别名可以简化查询
1
2
3
SELECT e.last_name, d.department_name, e.department_id
FROM employees AS e, departments AS d
WHERE e.department_id = d.department_id;

一但使用了别名，这一个语句中必须使用别名替代

  • 连接多个表

例如：展示员工 last_name, department_name, city

1
2
3
4
5
6
SELECT e.last_name, d.department_name, lc.city
FROM employees AS e,
     departments as d,
     locations as lc
WHERE e.department_id = d.department_id
  AND d.location_id = lc.location_id;

连接 N 个表，至少需要 N-1 个条件

2.1.3 非等值连接

1
2
3
4
5
SELECT e.last_name, e.salary, j.grade_level AS GRADE
FROM employees AS e,
     job_grades AS j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal  -- 按工资分档
ORDER BY salary DESC;  -- 按照工资排序

2.2 自连接 & 非自连接

使用别名的方式把 1 张表虚拟成 2 张表，而后两表可以进行内连接和外连接

1
2
3
4
5
SELECT CONCAT(worker.last_name, ' works for '
           , manager.last_name) AS relationship -- 连接为长字符串
FROM employees AS worker,
     employees AS manager
WHERE worker.manager_id = manager.employee_id;

将 employees 表虚拟为 表 worker 和 manager，然后自连接

2.3 内连接 & 外连接

  • 内连接：合并的表存在联系，即存在相同列。结果中不包含一个表与另一个表不匹配的行

内连接使用 INNER JOIN

  • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回不满足条件的行

外连接使用 LEFT JOIN RIGHT JOIN，特别地，对 MySQL 而言全外连接需要使用 UNION 方法

不满足条件的行 —— 相应的列为空 NULL

当返回不满足条件的行来自左表时，则为左外连接，左边的表也称为主表 ，右边的表称为从表

当返回不满足条件的行来自右表时，则为右外连接，右边的表也称为主表 ，左边的表称为从表

3 多表查询代码

使用 SQL99 语法实现

3.1 基础语法 JOIN ON

标准格式

1
2
3
4
5
6
7
8
SELECT table1.column, 
	   table2.column,
	   table3.column
FROM table1
	JOIN table2 
		ON table1 和 table2 的连接条件
    JOIN table3 
        ON table2 和 table3 的连接条件
  • 各个条件相互独立
  • 使用 JOIN CROSS JOIN INNER JOIN 含义相同，均代表内连接

下面看具体内连接案例：

3.2 内连接

1
2
3
4
5
6
SELECT table1.column,
       table2.column,
FROM table1
	JOIN table2
		ON 条件
WHERE 其他;
  • 例：查询 last_name, department_name, city 员工为 'Chen'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT e.last_name, d.department_name, lc.city
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id
         JOIN locations lc
              ON d.location_id = lc.location_id;
WHERE e.last_name = 'Chen';

>>>
+-----------+-----------------+---------+
| last_name | department_name | city    |
+-----------+-----------------+---------+
| Chen      | Finance         | Seattle |
+-----------+-----------------+---------+
1 row in set (0.00 sec)

3.3 外连接

3.3.1 左外连接 LEFT JOIN ON

1
2
3
4
5
6
-- A 为主表，B 表向 A 表插入
SELECT 字段列表
FROM A表 
	LEFT JOIN B表
		ON 关联条件
WHERE 其他;
  • 例：employees 表中 Grant 对应 department_id 为 NULL 故在表 departments 检索不到
1
2
3
4
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
         LEFT OUTER JOIN departments d
                         ON e.department_id = d.department_id;

3.3.2 右外连接 RIGHT JOIN ON

1
2
3
4
5
6
-- B 为主表，A 表向 B 表插入
SELECT 字段列表
FROM A表 
	RIGHT JOIN B表
		ON 关联条件
WHERE 其他;
  • 例：employees 表中没有处于 departments 表中 Treasury 部门的，故向 departments 插入时补空
1
2
3
4
SELECT d.department_name, e.last_name, e.department_id
FROM employees e
         RIGHT OUTER JOIN departments d
                          ON e.department_id = d.department_id;

3.3.3 满外连接 FULL JOIN ON

1
满外连接的结果 = 左右表匹配到的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据

标准语法

1
2
3
4
5
6
-- 互相charity
SELECT 字段列表
FROM A表 
	FULL JOIN B表
		ON 关联条件
WHERE 其他;

注：MySQL 不支持 FULL JOIN ，但可以通过合并左外连接和右外连接的方式实现。

可以简单理解：FULL JOIN <=> LEFT JOIN UNION RIGHT JOIN

4 合并查询结果 UNION

UNION 可以将多个 SELECT 语句得到的结果合并成一个表输出：

  • 匹配：多个 SELECT 语句得到的结果集列数和数据类型必须相同
  • 去重与否：使用 UNION 会去除重复数据；使用 UNION ALL 不会去除重复数据
1
2
3
SELECT column, ... FROM table1
UNION [ALL]  -- UNION 或者 UNION ALL
SELECT column, ... FROM table2
  • 例：查询部门编号 department_id > 90 或 邮箱 email 包含 a 的员工信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 法一：使用 WHERE
SELECT last_name, email, department_id
FROM employees
WHERE email LIKE '%a%'
   OR department_id > 90;

-- 法二：使用 UNION
SELECT last_name, email, department_id
FROM employees
WHERE email LIKE '%a%'
UNION
SELECT last_name, email, department_id
FROM employees
WHERE department_id > 90;

5 JOIN 实现的几种关系（总结）

5.1 内连接：A 交 B

A 表与 B 表内连接，相当于 $A \cap B$

1
2
3
4
SELECT A.column, B.column
FROM A
	JOIN B
		ON 条件;

5.2 左外连接：A 交 (A 交 B)

A 表作为主表，B 表作为从表插入 A ，相当于 $A + A \cap B$

1
2
3
4
SELECT A.column, B.column
FROM A
	LEFT JOIN B
		ON 条件;

5.3 右外连接：B 交 (A 交 B)

B 表作为主表，A 表作为从表插入 B ，相当于 $B + A \cap B$

1
2
3
4
SELECT A.column, B.column
FROM A
	RIGHT JOIN B
		ON 条件;

左外连接和右外连接本质相同

5.4 WHERE 找出空值 IS NULL：只属于 A

A 表作为主表，B 表插入，但去除完美匹配的，即 A 表中与 B 表无关的部分。相当于 $A - A \cap B$

1
2
3
4
5
SELECT A.column, B.column
FROM A
	RIGHT JOIN B
		ON 条件
WHERE B.column IS NULL;
  • 例：查询 部门表中无员工的部门
1
2
3
4
5
SELECT d.department_name, d.department_id, e.department_id
FROM departments d
         LEFT JOIN employees e
                   ON d.department_id = e.department_id
WHERE e.department_id IS NULL

5.5 UNION 合并：A 并 B

UNION 可以将多个 SELECT 语句得到的结果合并成一个表输出，相当于 $A \cup B$

1
2
3
4
5
6
7
8
9
10
SELECT column, ... 
FROM A
	LEFT JOIN B
		ON A.col = B.col
WHERE B.col IS NULL
UNION ALL
SELECT column, ... 
FROM A
    RIGHT JOIN B
    	ON A.col = B.col
  • 例：两张方法，推荐第一种【先手动去重，再使用 UNION ALL 效率高】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 法一：利用 WHERE d.department_id IS NULL 手动去重
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;

-- 法二：直接使用 UNION 自动去重
SELECT employee_id, last_name, department_name
FROM employees e
         LEFT JOIN departments d
                   ON e.department_id = d.department_id
UNION
SELECT employee_id, last_name, department_name
FROM employees e
         RIGHT JOIN departments d
                    ON e.department_id = d.department_id;

5.6 找出空值后 UNION 合并：对称差

查询所有匹配失败的行数据，相当于 $A \cup B - A \cap B$

1
2
3
4
5
6
7
8
9
10
11
SELECT column, ... 
FROM A
	LEFT JOIN B
		ON A.col = B.col
WHERE B.col IS NULL
UNION ALL
SELECT column, ... 
FROM A
    RIGHT JOIN B
    	ON A.col = B.col
WHERE A.col IS NULL  -- 全部加上 IS NULL 判断
  • 例：查询所有失败行数据
1
2
3
4
5
6
7
8
9
10
11
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;

6 特殊等值连接

6.1 自然连接

NATURAL JOIN 会自动查询两张表中所有相同的字段，然后进行等值连接

1
2
3
4
-- 这是 SQL99 新语法
SELECT employee_id, last_name, department_name
FROM employees e
         NATURAL JOIN departments d;

注：自然连接使用 AND 连接各个条件。即只有当所有相同列名的值都相等时，才会放入结果集。

例如：当表 employees 和表 departments 有相同列 department_id 和 manager_id 时

1
2
3
4
5
6
-- 上面自然连接等价于
SELECT employee_id, last_name, department_name
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id
                  AND e.manager_id = d.manager_id;  -- 是 AND 而不是 OR

6.2 USING 连接

当有多个同名字段时，USING 可以指定同名字段进行连接

1
2
3
4
5
6
7
8
9
10
SELECT employee_id, last_name, department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;  -- 利用 USING 简化

-- 可以简化为
SELECT employee_id, last_name, department_name
FROM employees e
         JOIN departments d
              USING (department_id);  -- 指定 department_id

6.3 总结：等值连接的三种写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 法一：WHERE 限制条件
SELECT employee_id, last_name, department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

-- 法二：JOIN ... ON + 条件
SELECT employee_id, last_name, department_name
FROM employees e
         JOIN departments d
              ON e.department_id = d.department_id;
                  
-- 法三：USING 指定同名字段
SELECT employee_id, last_name, department_name
FROM employees e
         JOIN departments d
              USING (department_id);  -- 指定 department_id
  • WHERE 的使用没有限制，目的就是增加约束条件
  • ON 只能和 JOIN 连用
  • USING 只能和 JOIN，且要求字段必须同名