MySql只支持Union(并集)集合运算,好像也是4.0以后才有的;但是对于交集Intersect、差集Except,就没有实现了。
一般的解决方案用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。