MySQL交集和差集的實現(xiàn)方法

在MySQL中，只支持Union(并集)集合運算，而對于交集Intersect和差集Except并不支持。那么如何才能在MySQL中實現(xiàn)交集和差集呢？

一般在MySQL中，我們可以通過in和not in來間接實現(xiàn)交集和差集，當(dāng)然也有一定局限性，面對少量數(shù)據(jù)還可以，但數(shù)據(jù)量大了效率就會變得很低。

創(chuàng)建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  

創(chuàng)建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的區(qū)別

UNION和UNION ALL的功能都是將兩個結(jié)果集合并為一個，但是這兩個關(guān)鍵字不管從使用還是效率上來說，都是有一定區(qū)別的。

使用上：

1、對重復(fù)結(jié)果的處理：UNION在進行表鏈接后會篩選掉重復(fù)的記錄，而Union All則不會去除重復(fù)記錄。

2、對排序的處理：Union將會按照字段的順序進行排序；UNION ALL只是將兩個結(jié)果合并后就返回，并不會進行排序處理。

效率上：

從效率上說，UNION ALL的處理效率要比UNION高很多，所以，如果可以確認合并的兩個結(jié)果集中，且不包含重復(fù)數(shù)據(jù)和不需要進行排序的話，推薦使用UNION ALL。

相關(guān)閱讀：

不同場景下 MySQL 的遷移方案

MySQL FAQ系列整理

原文地址：https://blog.csdn.net/mine_song/article/details/70184072