SQL的交并差集

1. UNION (并集)

Union能够对两个或多个结果集进行链接，造成“并集”。子结果集全部的记录组合在一块儿造成新的结果集。相当于两个表数据上下连结。
基本语法形式：

SELECT <列名1> as <别名>, ……
  FROM <表名1>
 UNION
SELECT <列名2> as <别名>, ……
  FROM <表名2>;

限定条件：

• 子结果集要具备相同的结构。
• 字结果集的列数必须相同。
• 子结果集对应的数据类型必须能够兼容。
• 每一个子结果集不能包含order by和compute子句。

-- 删除重复行
SELECT <列名1> as <别名>, ……
  FROM <表名1>
 UNION
SELECT <列名2> as <别名>, ……
  FROM <表名2>;

-- 不删除重复行
SELECT <列名1> as <别名>, ……
  FROM <表名1>
 UNION ALL
SELECT <列名2> as <别名>, ……
  FROM <表名2>;

Example

-- 保留重复行
SELECT product_id, product_name
  FROM Product
 UNION ALL
SELECT product_id, product_name
  FROM Product2;

2. INTERSECT (交集) (MySQL不支持)

InterSect能够对两个或多个结果集进行链接，造成“交集”。返回左边结果集和右边结果集中都有的记录。
基本语法形式：

SELECT <列名1> as <别名>, ……
  FROM <表名1>
 INTERSECT
SELECT <列名2> as <别名>, ……
  FROM <表名2>;

限定条件：

• 子结果集要具备相同的结构。
• 字结果集的列数必须相同。
• 子结果集对应的数据类型必须能够兼容。
• 每一个子结果集不能包含order by和compute子句。

3. EXCEPT (差集) (MySQL不支持)

Except能够对两个或多个结果集进行链接，造成“差集”。返回第一个查询结果集合中已经有的记录，而第二个查询结果集中没有的记录。
oracle用的语法为 minus

基本语法形式：

SELECT <列名1> as <别名>, ……
  FROM <表名1>
 EXCEPT -- minus
SELECT <列名2> as <别名>, ……
  FROM <表名2>;

限定条件：

• 子结果集要具备相同的结构。
• 字结果集的列数必须相同。
• 子结果集对应的数据类型必须能够兼容。
• 每一个子结果集不能包含order by和compute子句。

在MySQL中，求差集也可以用 NOT IN

SELECT * FROM Product 
WHERE sale_price > 2000 
AND product_id NOT IN (
SELECT product_id FROM Product 
WHERE sale_price<1.3*purchase_price)

但 NOT IN 在处理大数据时的表现并不好，此时我们还可以用 join 来求差集

select A.id, B.id from A
left join B
on A.id=B.id
where B.id IS NULL

4. 求对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差.

但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差.

-- 使用 NOT IN 实现两个表的差集
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

参考来源：
DataWhale-SQL: Task04：集合运算 - 4.1.表的加减法
SQL的JOIN、并集、差集、交集