MySQL中实现差集与交集的SQL方法

准备测试数据

为了直观展示差集与交集的运算结果，我们需要先构建两张结构相同但数据存在差异的测试表 t1 和 t2。

1. 清理环境：如果之前运行过相关脚本，请先删除旧表以避免冲突。

drop table if exists t1;
drop table if exists t2;

2. 创建表结构：定义主键及昵称、播放量等字段。

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    nickname VARCHAR(20),
    playNum VARCHAR(20)
);

CREATE TABLE t2 (
    id INT PRIMARY KEY,
    nickname VARCHAR(20),
    playNum VARCHAR(20)
);

3. 插入测试数据：模拟部分ID一致但内容不同，以及仅存在于单表的记录。

-- 向 t1 插入数据
INSERT INTO t1 VALUES (1, '1', '10');
INSERT INTO t1 VALUES (2, '2', '20');
INSERT INTO t1 VALUES (3, '3', '30');

-- 向 t2 插入数据
INSERT INTO t2 VALUES (1, '1', '10'); -- ID=1 完全相同
INSERT INTO t2 VALUES (2, '2', '200'); -- ID=2 内容不同
INSERT INTO t2 VALUES (3, '33', '300'); -- ID=3 内容不同

以下是执行上述操作后的表状态示意：

实现两表交集

交集是指同时存在于两张表中的记录。由于MySQL没有INTERSECT关键字，我们可以利用 UNION ALL 将所有记录合并，然后通过分组统计来找出重复出现的行。

具体操作步骤

1. 合并数据：使用 UNION ALL 将 t1 和 t2 的所有行垂直堆叠在一起。
2. 分组计数：对合并后的结果集按所有列进行分组。
3. 筛选交集：在 HAVING 子句中过滤出出现次数大于1的行，即为交集。

SELECT id, nickname, playNum, COUNT(*)
FROM (
    SELECT id, nickname, playNum FROM t1
    UNION ALL
    SELECT id, nickname, playNum FROM t2
) a
GROUP BY id, nickname, playNum
HAVING COUNT(*) > 1;

在上述示例中，只有第一行数据完全一致，因此它将作为唯一的交集结果显示。执行效果如下：

实现两表差集

差集通常指属于第一个表但不属于第二个表的记录。MySQL中常用的实现方式是左连接（LEFT JOIN），通过匹配主键或特定条件，找出右侧表中为NULL的记录。

具体操作步骤

1. 左连接关联：以 t1 为主表，使用 LEFT JOIN 关联 t2。
2. 设定匹配条件：在 ON 子句中指定关联条件（如 id 相等）。
3. 过滤非匹配项：利用 WHERE 子句筛选出关联失败或内容不一致的行。若需严格判断“完全不存在”，通常检查右表主键是否为空；若要找出“内容有差异”的差集，则直接对比字段值。

以下SQL展示了找出 t1 中存在而 t2 中不相同（针对nickname和playNum）的记录：

SELECT t1.id, t1.nickname, t1.playNum
FROM t1 
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.nickname != t2.nickname 
OR t1.playNum != t2.playNum;

在此逻辑下，ID为2和3的记录因为内容不匹配而被筛选出来作为“差集”。注意，这种写法返回的是内容上有差异的行。如果需求是查找仅在一方存在的实体记录，应使用 WHERE t2.id IS NULL 的形式。查询结果示意如下：

总结

虽然MySQL缺少原生的集合操作命令，但通过灵活的 SQL 组合技巧完全可以达成目标。UNION ALL + HAVING 能够高效处理基于全字段匹配的交集查询；而 LEFT JOIN 则是提取差集数据的经典方案。理解这些底层原理有助于在实际开发中应对各种复杂的去重与比对场景。

以上就是MySQL实现差集与交集的详细教程，更多关于SQL优化的资料请关注本站其它相关文章！