MySQL实现差集(Minus)和交集(Intersect)

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。