第 6 章：多表查询

学习目标

学完本章后，你将能够：

理解多表查询的基本原理和应用场景
掌握不同类型的连接查询，包括交叉连接、内连接和外连接
使用自连接解决特殊的查询需求
选择合适的连接类型来满足不同的业务需求
避免多表查询中常见的性能问题

在实际工作中，数据通常分布在多个相互关联的表中，而我们需要从这些表中组合数据以获取有意义的信息。

6.1 交叉连接(CROSS JOIN)

交叉连接（也称为笛卡尔积）是最简单的一种连接类型，它将第一个表的每一行与第二个表的每一行组合，形成一个结果集。

基本语法

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
CROSS JOIN 表2;

或者：

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1, 表2;

第二种形式是隐式的交叉连接，在不指定连接条件的情况下使用逗号分隔表名。

交叉连接示例

假设我们有以下两个简单的表：

products 表（商品表）：

product_id	product_name
1	AI 零代码应用生成平台教程
2	OJ 在线判题项目教程
3	智能协同云图库项目教程

discounts 表（折扣表）：

discount_id	discount_name	discount_percent
1	新用户优惠	10
2	季末促销	20

执行交叉连接：

▼
sql
复制代码
SELECT p.product_name, d.discount_name, d.discount_percent
FROM products p
CROSS JOIN discounts d;

或：

▼
sql
复制代码
SELECT p.product_name, d.discount_name, d.discount_percent
FROM products p, discounts d;

结果：

product_name	discount_name	discount_percent
AI 零代码应用生成平台教程	新用户优惠	10
AI 零代码应用生成平台教程	季末促销	20
OJ 在线判题项目教程	新用户优惠	10
OJ 在线判题项目教程	季末促销	20
智能协同云图库项目教程	新用户优惠	10
智能协同云图库项目教程	季末促销	20

交叉连接生成的结果行数等于第一个表的行数乘以第二个表的行数。在本例中，3（products 表的行数）× 2（discounts 表的行数）= 6 行。

交叉连接的应用场景

交叉连接在以下场景中特别有用：

生成所有可能的组合：例如，生成所有商品与所有折扣的组合，以便进行促销活动规划。
填充辅助表或临时表：有时需要生成包含多个维度组合的数据。
某些特殊算法的实现：在某些数据挖掘或统计分析中需要计算所有可能的配对。

但在实际应用中，交叉连接需要非常谨慎的使用，因为它可能生成大量的数据，最终导致性能问题。我们通常会在交叉连接的结果上应用 WHERE 子句来过滤不必要的组合。

6.2 内连接(INNER JOIN)

内连接是最常用的连接类型，它只返回两个表中满足连接条件的行。这种连接类型能够帮助我们关联不同表中的相关数据。

基本语法

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
INNER JOIN 表2 ON 表1.列 = 表2.列;

也可以简写为：

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列;

因为 INNER JOIN 是默认的 JOIN 类型，所以 "INNER" 关键字通常被省略。

内连接示例

假设我们有以下两个表：

orders 表（订单表）：

order_id	user_id	order_date	total_amount
1	101	2025-06-01	299
2	102	2025-06-02	99
3	101	2025-06-05	199
4	103	2025-06-10	399

users 表（用户表）：

user_id	username	email	registration_date
101	鱼皮	yupi@example.com	2025-01-15
102	代码鸭	duck@example.com	2025-02-20
103	算法达人	algo@example.com	2025-03-25
104	面试鸭	interview@example.com	2025-04-30

执行内连接查询用户和他们的订单：

▼
sql
复制代码
SELECT o.order_id, u.username, o.order_date, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;

结果：

order_id	username	order_date	total_amount
1	鱼皮	2025-06-01	299
2	代码鸭	2025-06-02	99
3	鱼皮	2025-06-05	199
4	算法达人	2025-06-10	399

内连接仅返回两个表中有匹配的行。在本例中，用户 "面试鸭"（user_id = 104）没有任何订单，因此不会出现在结果中。

使用多个连接条件

内连接可以有多个连接条件，使用 AND 连接：

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
INNER JOIN 表2 ON 表1.列1 = 表2.列1 AND 表1.列2 = 表2.列2;

连接多个表

可以连接三个或更多的表：

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
INNER JOIN 表2 ON 表1.列 = 表2.列
INNER JOIN 表3 ON 表2.列 = 表3.列;

例如，如果我们有一个 products 表（产品表），可以连接三个表查询订单、用户和产品信息：

▼
sql
复制代码
SELECT o.order_id, u.username, p.product_name, o.order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN products p ON o.product_id = p.product_id;

内连接是日常数据库操作中最常用的连接类型，它能够有效地关联多个表中的相关数据。

6.3 外连接(OUTER JOIN)

外连接用于检索一个表中的所有行，以及另一个表中满足连接条件的行。外连接分为左外连接、右外连接和全外连接三种类型。

左外连接(LEFT OUTER JOIN)

左外连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则结果中右表的列将包含 NULL 值。

基本语法

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
LEFT [OUTER] JOIN 表2 ON 表1.列 = 表2.列;

关键字 "OUTER" 是可选的，通常省略。

左外连接示例

使用前面的 orders 和 users 表：

▼
sql
复制代码
SELECT u.user_id, u.username, o.order_id, o.order_date, o.total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

结果：

user_id	username	order_id	order_date	total_amount
101	鱼皮	1	2025-06-01	299
101	鱼皮	3	2025-06-05	199
102	代码鸭	2	2025-06-02	99
103	算法达人	4	2025-06-10	399
104	面试鸭	NULL	NULL	NULL

注意尽管用户 "面试鸭"（user_id = 104）没有任何订单，也会出现在结果中。这就是左外连接与内连接的区别：左外连接会包含左表中的所有行，即使它们在右表中没有匹配的行。

右外连接(RIGHT OUTER JOIN)

右外连接与左外连接相反，它返回右表中的所有行，以及左表中满足连接条件的行。

基本语法

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
RIGHT [OUTER] JOIN 表2 ON 表1.列 = 表2.列;

右外连接示例

▼
sql
复制代码
SELECT u.user_id, u.username, o.order_id, o.order_date, o.total_amount
FROM orders o
RIGHT JOIN users u ON o.user_id = u.user_id;

这个查询的结果与前面的左外连接示例相同，只是表的顺序不同。在许多情况下，左外连接和右外连接可以互相转换，选择哪一种主要取决于查询的可读性和表的顺序。

全外连接(FULL OUTER JOIN)

全外连接返回两个表中的所有行。当左表和右表中的行不匹配时，结果中相应的列将包含 NULL 值。

基本语法

▼
sql
复制代码
SELECT 列1, 列2, ...
FROM 表1
FULL [OUTER] JOIN 表2 ON 表1.列 = 表2.列;

注意：MySQL 不直接支持 FULL OUTER JOIN，但可以通过 LEFT JOIN 和 RIGHT JOIN 的结合使用 UNION 来模拟。

全外连接示例（MySQL 中的模拟）

▼
sql
复制代码
SELECT u.user_id, u.username, o.order_id, o.order_date, o.total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id

UNION

SELECT u.user_id, u.username, o.order_id, o.order_date, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id IS NULL;

这个查询结合了左外连接和右外连接的结果，保证两个表中的所有行都包含在结果中。

外连接的应用场景

外连接在以下场景中特别有用：

查找"缺失"的记录：例如，哪些用户没有下订单。

▼
sql
复制代码
SELECT u.user_id, u.username
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

生成报表：即使没有匹配的行，也需要显示所有主实体（如用户、部门等）。
数据完整性检查：检查引用完整性或寻找孤立的记录。

6.4 自连接

自连接是一种特殊的连接类型，表与自身进行连接。这种连接在处理具有层次结构或需要比较表中不同行的情况时非常有用。

基本语法

▼
sql
复制代码
SELECT a.列1, b.列2, ...
FROM 表 a
JOIN 表 b ON a.列 = b.列;

在自连接中，我们使用别名（如 a 和 b）来区分同一个表的不同实例。

自连接示例

假设我们有一个 employees 表，其中包含员工及其管理者的信息：

employee_id	name	position	manager_id
1	鱼皮	CEO	NULL
2	代码鸭	CTO	1
3	算法达人	开发经理	2
4	面试鸭	高级开发工程师	3
5	老鱼	产品经理	1

使用自连接查询每个员工及其管理者的名称：

▼
sql
复制代码
SELECT e.employee_id, e.name AS employee_name, 
       m.employee_id AS manager_id, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

结果：

employee_id	employee_name	manager_id	manager_name
1	鱼皮	NULL	NULL
2	代码鸭	1	鱼皮
3	算法达人	2	代码鸭
4	面试鸭	3	算法达人
5	老鱼	1	鱼皮

这个查询使用了左外连接，以确保 CEO（没有管理者）也会出现在结果中。

自连接的应用场景

自连接在以下场景中特别有用：

层次结构数据：例如组织结构、类别层次、评论和回复等。
查找相似记录：例如，找出同一个用户的不同订单，或比较同一个表中的不同行。

▼
sql
复制代码
-- 找出同一天购买了多个商品的用户
SELECT DISTINCT a.user_id, a.order_date
FROM orders a
JOIN orders b ON a.user_id = b.user_id 
               AND a.order_date = b.order_date
               AND a.product_id <> b.product_id;

计算序列或连续记录：例如，查找连续登录的用户。

自连接是一种强大的技术，可以解决许多复杂的查询需求，尤其是那些涉及同一实体内部关系的问题。

练习题

练习 1：内连接应用

社区有一个 posts 表记录帖子信息，包含 post_id, title, content, user_id, created_at 等字段；还有一个 users 表记录用户信息，包含 user_id, username, email 等字段。请编写一个查询，关联这两个表，显示每篇帖子的标题、创建时间和作者用户名。

答案：

▼
sql
复制代码
SELECT 
    p.post_id, 
    p.title, 
    p.created_at, 
    u.username AS author
FROM posts p
INNER JOIN users u ON p.user_id = u.user_id;

练习 2：左外连接应用

使用练习 1 中的表，找出所有用户及其发帖数量，包括那些从未发过帖的用户（发帖数为 0）。

答案：

▼
sql
复制代码
SELECT 
    u.user_id, 
    u.username, 
    COUNT(p.post_id) AS post_count
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
GROUP BY u.user_id, u.username;

练习 3：自连接应用

假设有一个 course_prerequisites 表，记录了项目课程的前置课程关系，包含 course_id, course_name, prerequisite_id 等字段，其中 prerequisite_id 引用了同一个表中的另一个课程。编写一个查询，显示每门课程及其前置课程的名称。

答案：

▼
sql
复制代码
SELECT 
    c1.course_id, 
    c1.course_name, 
    c2.course_id AS prerequisite_id, 
    c2.course_name AS prerequisite_name
FROM course_prerequisites c1
LEFT JOIN course_prerequisites c2 ON c1.prerequisite_id = c2.course_id;

编程导航教程

第 6 章：多表查询

学习目标

6.1 交叉连接(CROSS JOIN)

基本语法

交叉连接示例

交叉连接的应用场景

6.2 内连接(INNER JOIN)

基本语法

内连接示例

使用多个连接条件

连接多个表

6.3 外连接(OUTER JOIN)

左外连接(LEFT OUTER JOIN)

基本语法

左外连接示例

右外连接(RIGHT OUTER JOIN)

基本语法

右外连接示例

全外连接(FULL OUTER JOIN)

基本语法

全外连接示例（MySQL 中的模拟）

外连接的应用场景

6.4 自连接

基本语法

自连接示例

自连接的应用场景

练习题

练习 1：内连接应用

练习 2：左外连接应用

练习 3：自连接应用