MySQL 入门教程 MySQL交集和差集的实现方法

2024-02-25 开发教程 MySQL 入门教程匿名 4℃

在MySQL中，只支持Union(并集)集合运算，而对于交集Intersect和差集Except并不支持。那么如何才能在MySQL中实现交集和差集呢？

一般在MySQL中，我们可以通过in和not in来间接实现交集和差集，当然也有一定局限性，面对少量数据还可以，但数据量大了效率就会变得很低。

创建table1

/*DDL 信息*/------------
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建table2

/*DDL 信息*/------------
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入

INSERT INTO t1 VALUES(1,'小王',10);
INSERT INTO t1 VALUES(2,'小宋',20);
INSERT INTO t1 VALUES(3,'小白',30);
INSERT INTO t1 VALUES(4,'hello',40);
INSERT INTO t2 VALUES(1,'小王',10);
INSERT INTO t2 VALUES(2,'小宋',22);
INSERT INTO t2 VALUES(3,'小肖',31);
INSERT INTO t2 VALUES(4,'hello',40);

SELECT t1.* FROM t1
id  name    age
1   小王      10
2   小宋      20
3   小白      30
4   hello   40

SELECT t2.* FROM t2
id  name    age
1   小王  10
2   小宋  22
3   小肖  31
4   hello   40

使用not in 求差集，但效率低

SELECT t1.* FROM t1
WHERE
name NOT IN
(SELECT name FROM t2)
id  name    age
3   小白      30

SELECT t1.id, t1.name, t1.age
FROM t1
LEFT JOIN t2
ON t1.id = t2.id
WHERE t1.name != t2.name
   OR t1.age != t2.age;
id  name    age
2   小宋      20
3   小白      30

求交集，此时只有id name age 所有都一样才是符合要求的。

SELECT  id,  NAME,  age, COUNT(*)
    FROM (SELECT id, NAME, age
        FROM t1
        UNION ALL
        SELECT id, NAME, age
        FROM t2
        ) a
    GROUP BY id, NAME, age
    HAVING COUNT(*) > 1
    id  NAME    age COUNT(*)
    1   小王      10  2
    4   hello   40  2

union all和union的区别

UNION和UNION ALL的功能都是将两个结果集合并为一个，但是这两个关键字不管从使用还是效率上来说，都是有一定区别的。

使用上：

1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，而Union All则不会去除重复记录。

2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是将两个结果合并后就返回，并不会进行排序处理。

效率上：

从效率上说，UNION ALL的处理效率要比UNION高很多，所以，如果可以确认合并的两个结果集中，且不包含重复数据和不需要进行排序的话，推荐使用UNION ALL。

"MySQL 入门教程"的目录

MySQL 入门教程 MySQL交集和差集的实现方法