MySQL / 系统课时

多表连接条件与结果去重

多表连接条件与结果去重

多表连接最常见的错误不是语法报错,而是结果行数异常:遗漏连接条件会形成笛卡尔积,一对多关系会自然扩张行数,过滤条件放错位置还可能改变外连接语义。本课建立一套从连接条件到去重方式的判断方法。

学习目标

  • 能识别遗漏连接条件造成的笛卡尔积
  • 能判断重复行来自错误连接还是正常的一对多关系
  • 能正确区分 ON、WHERE 与 DISTINCT 的职责

先确认每张表为什么能够连接

多表查询时,每增加一张表,都应该能够明确回答“它通过哪个键与已有结果关联”。如果两张表之间没有连接条件,数据库就会把左侧每一行与右侧每一行组合。

例如有 3 个用户和 4 张订单,缺少关联条件时将得到 3×4=123 \times 4 = 12 行。这种组合就是笛卡尔积,通常不是业务真正需要的结果。

多表连接正确关联与笛卡尔积对比

下面的示例同时演示正确连接和遗漏条件后的差异。

sql
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;

预期结果:

text
+-------+----------+
| name  | order_id |
+-------+----------+
| Alice |      101 |
| Alice |      102 |
| Bob   |      103 |
| Bob   |      104 |
+-------+----------+

Alice 和 Bob 各出现两次并不是错误,因为一个用户确实拥有两张订单。连接结果的粒度已经从“每个用户一行”变成“每个用户订单组合一行”。

区分错误重复与关系扩张

看到重复记录时,第一反应不应该是加 DISTINCT,而应先检查连接关系。通常需要依次判断:连接条件是否缺失、连接键是否唯一、一方是否天然存在多条匹配记录,以及最终业务需要的结果粒度是什么。

如果目标是“每个下过单的用户只显示一次”,可以使用 DISTINCT,因为此时确实只关心用户集合,而不关心具体订单。

sql
SELECT DISTINCT u.id, u.name
FROM users AS u
JOIN orders AS o
    ON u.id = o.user_id
ORDER BY u.id;

预期结果:

text
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
+----+-------+

DISTINCT 是对最终选择出的整行做去重。它不能修复错误的连接逻辑,而且在数据量较大时还可能增加排序或临时处理成本。

ON 与 WHERE 的条件位置

对内连接而言,很多过滤条件放在 ON 或 WHERE 中可能得到相同结果;但在外连接中,两者含义不同。ON 决定右表哪些记录能够参与匹配,WHERE 则在连接完成后过滤最终结果。

例如要查询全部用户,同时只关联金额不低于 100 的订单,应把订单金额条件放在 ON 中。

sql
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;

预期结果:

text
+-------+----------+--------+
| 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 和聚合函数;如果只是因为连接键写错而产生多余行,则应修正连接条件,而不是在结果末尾机械去重。

判断多表查询是否正确,核心不是“结果有没有重复”,而是确认每一行代表什么。只要连接条件完整、结果粒度符合业务含义,一对多关系产生多行就是正确结果;只有在业务确实要求唯一集合时,才需要显式去重。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

多表连接条件与结果去重

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通