MySQL / 系统课时
多表连接条件与结果去重
多表连接条件与结果去重
多表连接最常见的错误不是语法报错,而是结果行数异常:遗漏连接条件会形成笛卡尔积,一对多关系会自然扩张行数,过滤条件放错位置还可能改变外连接语义。本课建立一套从连接条件到去重方式的判断方法。
学习目标
- 能识别遗漏连接条件造成的笛卡尔积
- 能判断重复行来自错误连接还是正常的一对多关系
- 能正确区分
ON、WHERE与DISTINCT的职责
先确认每张表为什么能够连接
多表查询时,每增加一张表,都应该能够明确回答“它通过哪个键与已有结果关联”。如果两张表之间没有连接条件,数据库就会把左侧每一行与右侧每一行组合。
例如有 3 个用户和 4 张订单,缺少关联条件时将得到 行。这种组合就是笛卡尔积,通常不是业务真正需要的结果。

下面的示例同时演示正确连接和遗漏条件后的差异。
CREATE TEMPORARY TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TEMPORARY TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO users (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol');
INSERT INTO orders (id, user_id, amount) VALUES
(101, 1, 80.00),
(102, 1, 120.00),
(103, 2, 60.00),
(104, 2, 90.00);
SELECT u.name, o.id AS order_id
FROM users AS u
JOIN orders AS o
ON u.id = o.user_id
ORDER BY u.id, o.id;预期结果:
+-------+----------+
| name | order_id |
+-------+----------+
| Alice | 101 |
| Alice | 102 |
| Bob | 103 |
| Bob | 104 |
+-------+----------+Alice 和 Bob 各出现两次并不是错误,因为一个用户确实拥有两张订单。连接结果的粒度已经从“每个用户一行”变成“每个用户订单组合一行”。
区分错误重复与关系扩张
看到重复记录时,第一反应不应该是加 DISTINCT,而应先检查连接关系。通常需要依次判断:连接条件是否缺失、连接键是否唯一、一方是否天然存在多条匹配记录,以及最终业务需要的结果粒度是什么。
如果目标是“每个下过单的用户只显示一次”,可以使用 DISTINCT,因为此时确实只关心用户集合,而不关心具体订单。
SELECT DISTINCT u.id, u.name
FROM users AS u
JOIN orders AS o
ON u.id = o.user_id
ORDER BY u.id;预期结果:
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
| 2 | Bob |
+----+-------+DISTINCT 是对最终选择出的整行做去重。它不能修复错误的连接逻辑,而且在数据量较大时还可能增加排序或临时处理成本。
ON 与 WHERE 的条件位置
对内连接而言,很多过滤条件放在 ON 或 WHERE 中可能得到相同结果;但在外连接中,两者含义不同。ON 决定右表哪些记录能够参与匹配,WHERE 则在连接完成后过滤最终结果。
例如要查询全部用户,同时只关联金额不低于 100 的订单,应把订单金额条件放在 ON 中。
SELECT
u.name,
o.id AS order_id,
o.amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
AND o.amount >= 100
ORDER BY u.id, o.id;预期结果:
+-------+----------+--------+
| name | order_id | amount |
+-------+----------+--------+
| Alice | 102 | 120.00 |
| Bob | NULL | NULL |
| Carol | NULL | NULL |
+-------+----------+--------+如果把 o.amount >= 100 移到 WHERE 中,NULL 不满足该条件,Bob 和 Carol 都会被过滤,LEFT JOIN 在效果上就接近了内连接。
选择真正需要的去重方式
如果业务需要的是唯一实体集合,可以使用 DISTINCT;如果需要汇总一对多数据,应优先考虑 GROUP BY 和聚合函数;如果只是因为连接键写错而产生多余行,则应修正连接条件,而不是在结果末尾机械去重。
判断多表查询是否正确,核心不是“结果有没有重复”,而是确认每一行代表什么。只要连接条件完整、结果粒度符合业务含义,一对多关系产生多行就是正确结果;只有在业务确实要求唯一集合时,才需要显式去重。