MySQL实现交集查询和差集查询以及对称差查询
文章目录
- 0x00 集合操作
- 0x01 数据库中的集合查询
- 1、并集查询
- 2、交集查询
- 3、补集查询
- 0x02 MySQL的交集查询、差集查询和对称差查询的实现方式
- 1、交集查询
- 2、差集查询
- 3、对称差查询
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
