MYSQL 交集 差集 并集

一 MYSQL

只有并集没有交集差集的关键字

二 表结构

a 　　　　　　　　　　　　　　b

         

三 并集

  3.1 UNION  不包含重复数据

SELECT NAME  FROM a UNION
SELECT NAME  FROM b;

 

 3.2 UNION ALL  包含重复数据

四  差集

     找出在a表中存在的id 但是在b表中不存在的id

4.1 : 利用 union

SELECT ID FROM (

-- 并集
SELECT DISTINCT a.id AS ID FROM a  
UNION ALL
SELECT DISTINCT B.ID AS ID FROM  b
)TEMP GROUP BY ID HAVING COUNT(ID) = 1;

 

4.2 ：子查询 not in

SELECT id FROM a WHERE id NOT IN (SELECT id FROM b);

4.3 : 子查询 not exists

SELECT id FROM a WHERE  NOT EXISTS (SELECT id FROM b WHERE a.id = b.id);

4.4 ：左连接判断右表IS NULL

SELECT a.id FROM a LEFT JOIN  b ON a.id = b.id WHERE b.id IS NULL ORDER BY a.id 

 

五  交集 INTERSECT :

SELECT ID FROM (
-- 并集 
SELECT DISTINCT a.id AS ID FROM a  
UNION ALL
SELECT DISTINCT B.ID AS ID FROM  b
)TEMP GROUP BY ID HAVING COUNT(ID) != 1;