mysql 交集，并集，差集

前言：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