MySQL INTERSECT 怎样找出两组查询的共同记录

如果需求是“只保留两组查询都查到的记录”，MySQL 可以直接使用 INTERSECT：左侧查询和右侧查询返回的相同行会留下，默认按 DISTINCT 去重；只有明确写成 INTERSECT ALL 时，重复次数才会参与结果。先记住一个边界：它比较的是两边 SELECT 出来的完整行，不会自动按主键或列名猜测匹配关系。

本文以 MySQL 8.4 手册语义说明，MySQL 8.0 需要至少 8.0.31 才能使用这组集合运算。官方地址：https://dev.mysql.com/doc/refman/8.4/en/intersect.html。下面的 CTE 和查询都是原创示例，用来推导结果口径，不把示例输出当作本机执行截图。

先还原共同记录的查询现场

先假设我们要找“报名名单”和“签到名单”中同时出现的用户。第一眼容易把 INTERSECT 当成“按 user_id 关联”，但它真正看到的是每条查询输出的整行。因此，先把两侧都投影成同一个业务对象，再观察结果。

-- 两侧都只返回用户编号，比较单位就是 user_id
WITH signup AS (
    SELECT 101 AS user_id UNION ALL
    SELECT 102 UNION ALL
    SELECT 103
), checked_in AS (
    SELECT 102 AS user_id UNION ALL
    SELECT 103 UNION ALL
    SELECT 104
)
SELECT user_id FROM signup
INTERSECT
SELECT user_id FROM checked_in
ORDER BY user_id; -- 最终展示顺序由结果级排序决定

按集合语义，交集是 102 和 103。101 只在报名侧出现，104 只在签到侧出现，都不会进入交集。这里的关键线索是：两侧返回一列、列的业务含义一致，所以“共同记录”就是共同的用户编号。

从列投影定位为什么结果不全

如果把 status、source 等附加字段也选出来，比较单位就从“用户编号”变成了完整元组。例如左侧返回 (102, '报名')，右侧返回 (102, '签到')，即使编号相同，整行仍然不同，交集可能为空。

-- 只想按 user_id 求交集，两边都不要混入展示字段
SELECT user_id
FROM signup_record
WHERE campaign_id = ? -- 由应用参数绑定活动范围
INTERSECT
SELECT user_id
FROM checkin_record
WHERE campaign_id = ? -- 右侧也必须限定同一业务范围
ORDER BY user_id;

两侧必须有相同数量的列，对应位置要有可比较的类型和相同的业务含义。不要用 SELECT * 隐含决定比较契约：表结构增加字段后，交集语义也可能跟着变化。若业务对象是“用户 + 活动”，就让两侧都返回 user_id, campaign_id，不要只返回用户编号而把活动范围留在想象中。

用 DISTINCT 与 ALL 验证重复记录口径

接下来排查最容易误读的重复情况。默认的 INTERSECT 等价于 INTERSECT DISTINCT，一条共同元组最多保留一次；INTERSECT ALL 则按两侧出现次数的较小值保留。

-- 默认去重：共同的 102 只保留一条
WITH left_rows AS (
    SELECT 102 AS user_id UNION ALL SELECT 102 UNION ALL SELECT 103
), right_rows AS (
    SELECT 102 AS user_id UNION ALL SELECT 104
)
SELECT user_id FROM left_rows
INTERSECT DISTINCT
SELECT user_id FROM right_rows;

-- 保留重复次数：102 在两侧各出现一次，所以仍只保留一条
WITH left_rows AS (
    SELECT 102 AS user_id UNION ALL SELECT 102 UNION ALL SELECT 103
), right_rows AS (
    SELECT 102 AS user_id UNION ALL SELECT 104
)
SELECT user_id FROM left_rows
INTERSECT ALL
SELECT user_id FROM right_rows;

若右侧也有两个 102，INTERSECT ALL 才会让结果保留两条；可以把每个元组的结果次数理解为 min(左侧次数, 右侧次数)。需要唯一名单时省略修饰词或写 DISTINCT；需要对账、配额或多重集合次数时才选择 ALL，不要把 ALL 当成“右侧出现过就把左侧重复行全部留下”。

补上查询范围、括号和最终排序

共同记录只对两组输入负责，不会替应用自动补上租户、活动或时间范围。左右查询应分别写清授权范围，并通过驱动参数绑定值。若还混用 UNION 或 EXCEPT，要用括号表达业务分组，因为 MySQL 会优先计算 INTERSECT。

-- 先限定两个来源，再明确交集参与 UNION 的哪一侧
(SELECT user_id
 FROM signup_record
 WHERE tenant_id = ? AND campaign_id = ?)
INTERSECT
(SELECT user_id
 FROM checkin_record
 WHERE tenant_id = ? AND campaign_id = ?)
UNION
SELECT user_id
FROM manual_allowlist
WHERE tenant_id = ?
ORDER BY user_id; -- 结果级排序不要依赖来源表顺序

最终 ORDER BY 应放在整个集合表达式上。若要先对某一侧单独使用 LIMIT 或排序，应把那一侧包在括号中，明确“先截取候选，再参与交集”的意图。空结果并不一定是语法问题，先检查两边的范围条件、列投影和真实数据是否确实没有共同元组。

复查结果并记录可解释的口径

到这里可以按五项复查：服务器版本支持 INTERSECT；左右方向和范围正确；列数、位置、类型和业务含义一致；重复结果选择了 DISTINCT 或 ALL；最终结果有稳定排序。对定期报表或对账任务，还应记录查询时间、过滤条件和集合修饰词，因为源表变化后，同一 SQL 可能得到不同结果。

最短结论是：先把两侧查询投影成同一个业务对象，再决定唯一交集还是次数交集，最后用范围条件、括号和结果排序固定口径。

常见问题

为什么两个查询都有同一个 ID，INTERSECT 还是没有结果？

检查两侧是否还选了状态、来源等其他列。INTERSECT 比较完整行，同一个 ID 配上不同字段并不相等；如果业务只按 ID 判断，就让两侧都只返回 ID。

INTERSECT 和 INNER JOIN 怎样选择？

只需要两个查询结果的共同元组时，INTERSECT 更直接；需要同时读取一侧的明细列、按关联条件扩展结果时，再考虑 JOIN。两者的列投影和重复语义不能混为一谈。

能不能依赖结果自然顺序？

不能。集合运算的输出顺序不应当作为业务规则，展示或导出前请在整个结果上明确写 ORDER BY。