前言:mysql只有并集语句union,没有差集合交集,本文写了下交集和差集的实现。
一、创建二个表
DROP TABLE IF EXISTS t1 ;
CREATE TABLE t1 (name VARCHAR(30) , age int) ENGINE=innodb;
insert into t1 VALUES ('张三',33);
insert into t1 VALUES ('李四',44);
insert into t1 VALUES ('王五',55);
insert into t1 VALUES ('孙六',66);
DROP TABLE IF EXISTS t2 ;
CREATE TABLE t2 (name VARCHAR(30) , age int) ENGINE=innodb;
insert into t2 VALUES ('张三',33);
insert into t2 VALUES ('李四',44);
insert into t2 VALUES ('秦七',77);
二、并集
select * from t1
UNION
select * from t2
三、交集
#方式一
select * from t1 where EXISTS (
select * from t2 where t1.name= t2.name and t1.age= t2.age
);
#方式二 (不推荐)
select * from t1 where (t1.name ,t1.age) in (
select * from t2
)
#方式三
select * from (
select * from t1
union all
select * from t2
) t1 GROUP BY name,age HAVING COUNT(*)=2
四、差集 (只演示t1和t2的差集,t1有t2没有的)
select * from t1 where not EXISTS (
select * from t2 where t1.name = t2.name and t1.age = t2.age
)
select * from t1 where (name,age) not in ( select * from t2)
select t1.* from t1 LEFT JOIN t2 on t1.name = t2.name
and t1.age = t2.age where t2.name is null