MySQL实现交集查询和差集查询以及对称差查询

MySQL实现交集查询和差集查询以及对称差查询

0x00 集合操作

集合操作的种类有三种，分别是：

1、并集操作（UNION）
2、交集操作（INTERSECT）
3、差集操作（EXCEPT）

参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。

假设两个集合分别是A和B，这三种操作的示意图如下：

0x01 数据库中的集合查询

集合操作在数据库中的应用是集合查询，同样有三种：
1、并集查询
2、交集查询
3、补集查询

以table1和table2两张表为例，进行查询：

table1：
table2：

1、并集查询

在Oracle、SQL Server、MySQL数据库中，均可以使用UNION关键字进行并集查询

SELECT * FROM table1 
UNION
SELECT * FROM table2

或

SELECT * FROM table1 
UNION ALL
SELECT * FROM table2

UNION和UNION ALL谓词的差别：
UNION：将多个查询结果合并起来时，系统自动去掉重复元组。
UNION ALL：将多个查询结果合并起来时，保留重复元组。

UNION相当于在进行联合查询时，使用了SELECT DISTINCT语句。

2、交集查询

在Oracle和SQL Server中，使用INTERSECT关键字进行交集查询

SELECT * FROM table1 
INTERSECT
SELECT * FROM table2

在MySQL中，不支持INTERSECT关键字，因此查询会报错

3、补集查询

在Oracle中，使用MINUS关键字进行补集查询

SELECT * FROM table1 
MINUS
SELECT * FROM table2

在SQL Server中，使用EXCEPT关键字进行补集查询

SELECT * FROM table1 
EXCEPT
SELECT * FROM table2

在MySQL中，不支持EXCEPT关键字，因此查询会报错

0x02 MySQL的交集查询、差集查询和对称差查询的实现方式

因为MySQL不支持MINUS/EXCEPT和INTERSECT关键字，所以解决方案是使用JOIN关键字来达到相同的效果。

 在MySQL中可以用内连接（INNER JOIN）和左外连接（LEFT JOIN）代替交集操作（INTERSECT）和差集操作（EXCEPT）

1、交集查询

内连接INNER JOIN可以对两个或多个结果集进行连接，返回左边结果集和右边结果集中都有的记录，形成“交集”。

SELECT table1.* FROM table1 
INNER JOIN table2
ON table1.model = table2.model

或

SELECT table1.* FROM table1 
JOIN table2
ON table1.model = table2.model

使用INNER JOIN来连接结果集，有4个限定条件。
　　(1)子结果集要具有相同的结构。
　　(2)字结果集的列数必须相同。
　　(3)子结果集对应的数据类型必须可以兼容。
　　(4)每个子结果集不能包含order by和compute子句。

2、差集查询

左连接LEFT JOIN的含义是求两个表table1和table2的交集外加左表剩下的数据。从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后保留右表中不存在的数据，形成“差集”。

SELECT table1.* FROM table1 
LEFT JOIN table2
ON table1.model = table2.model
WHERE table2.model is NULL

3、对称差查询

在集合操作中还有一种对称差操作，示意图如下

在MySQL中可以利用UNION、LEFT JOIN、RIGHT JOIN关键字实现对称差查询

SELECT table1.* FROM table1 
LEFT JOIN table2
ON table1.model = table2.model
WHERE table2.model is NULL
UNION
SELECT table2.* FROM table1 
RIGHT JOIN table2
ON table1.model = table2.model
WHERE table1.model is NULL