SQL笔记| 用SQL进行集合运算

集合论是SQL语言的根基，因为这个特性，SQL也被称为面向集合的语言。

集合运算的几个注意事项

集合运算的参数是集合，从数据库实现层面上来说就是表或者视图。
注意事项1：SQL能操作具有重复行的集合，可以通过可选项ALL来支持。
关系数据库里的表允许存在重复的行，称为多重集合（multiset,亦称bag）。
SQL集合运算符也提供了允许重复和不允许重复的两种用法。如果直接使用INION或INTERSECT,结果里就不会出现重复的行。如果结果里想留下重复行，则可以加上可选项ALL，写作UNION ALL。ALL 的作用和SELECT子句里的DISTINCT可选项刚好相反。集合运算符为了排除掉重复行，会默认发生排序，而加上ALL可选项后，就不会再排序了，所以性能会提升。这是用于优化查询性能的非常有效的方法。
注意事项2：集合运算符的优先级
标准SQL规定，INTERSECT比UNION和EXCEPT的优先级更高。因此，当同时使用UNION和INTERSECT,又想让UNION优先执行时，必须用括号明确地指定运算顺序。
注意事项3：各个DBMS供应商在集合运算的实现程度上参差不齐
注意事项4：除法运算没有标准定义
四则运算里的和（UNION)、差（EXCEPT)、积（CROSS JOIN）都被引入了标准SQL了，但商（DIVIDE BY）因为各种原因迟迟没能标准化。

比较表和表：检查集合相等性至基础篇

在迁移数据库的时候，或者需要比较备份数据和最新数据的时候，我们需要调查两张表是否是相等的。这里说的“相等”指的是行数和列数以及内容都相同，即“是同一个集合”的意思。

-- 如果查询结果于tbl_A及tbl_B的行数一致，则两张表是相等的
SELECT COUNT(*) AS row_cnt 
 FROM (SELECT * FROM tbl_A UNION SELECT * FROM tbl_B) TMP ;

如果表tbl_A和tbl_B是相等的，那么排除掉重复行后，两个集合是完全重合的。前面的SQL语句可以用于包含NULL数据的表，而且不需要指定列数、列名和数据类型等就能使用，还是很方便的。S UNION S = S 这是UNION的一个非常重要的性质，在数学上我们称之为幂等性（idempotency）。

比较表和表：检查集合相等性之进阶篇

在集合论里，判定两个集合是否相等时，一般使用下面这2种方法。
1.（A包含B）且（B包含A）则（A=B）
2.(A∪B)=(A∩B)则（A=B）
如果集合A和B想等，那么A UNION B = A =B以及 A INTERSECT B = A =B 都成立。相反，如果A≠B,UNION 和 INTERSECT的结果就不相同了。UNION的执行结果的行数肯定会变多。因此，只需要判断（A UNION B）EXCEPT (A INTERSECT B)的结果集是不是空集就可以了。如果A=B，则这个结果集是空集，否则，这个结果集里肯定有数据。

-- 两张表想等时返回“相等”，否则返回“不相等”
SELECT CASE WHEN COUNT(*) =0 THEN '相等' ELSE '不相等' END AS result 
 FROM  ((SELECT * FROM tb1_A UNION SELECT * FROM tb1_B)  
   	EXCEPT (SELECT * FROM tb1_A UNION SELECT * FROM tb1_B)
      ) TMP ;
-- 用于比较表与表的diff
(SELECT * FROM tb1_A EXCEPT SELECT * FROM tb1_B)  
   UNION ALL (SELECT * FROM tb1_B EXCEPT SELECT * FROM tb1_A)

用差集实现关系除法运算

SQL里没有能直接进行关系除法运算的运算符，可以使用以下3种方法：
1.嵌套使用NOT EXISTS。
2.使用HAVING子句转换成一对一关系。
3.把除法变成减法。集合论里的减法指的是差集运算。

-- 通过差集进行关系除法运算（有余数）
SELECT DISTINCT emp FROM EmpSjills ES1 
 WHERE NOT EXISTS (SELECT skill FROM Skills EXCEPT SELECT  skill FROM EmpSkills ES2 WHERE ES1.emp=ES2.emp);

这里的解题思路是先把处理的单位分割成以员工为单位，然后将除法运算还原成更加简单的减法运算。这个解法与“将困难分解”那句格言的思路一致，还是很巧妙的。其实这条SQL语句的处理方法与面向过程语言里的中断控制处理很像。试着想象一下把这两张表当成两个文件，然后一行一行循环处理的过程。针对某一个员工循环判断其对各种技术的掌握情况，如果存在企业需求的技术，就进行减法运算；如果不存在就终止该员工的循环，继续对下一个员工执行同样的处理。

寻找相等的子集

SELECT SP1.sup AS s1,SP2.sup AS s2
 FROM SupParts SP1,SupParts SP2 
 WHERE SP1.sup < SP2.sup  	-- 生成供应商的全部组合
 AND SP1.part=SP2.part	 	-- 条件1：经营相同种类的零件
 GROUP BY SP1.sup,SP2.sup
 HAVING COUNT(*) = (SELECT COUNT(*) FROM SupParts SP3 WHERE SP3.sup=SP1.sup ) AND COUNT(*) = (SELECT COUNT(*) FROM SupParts SP4 WHERE SP4.sup=SP2.sup )  	-- 条件2：经营的零件的种类数相同;

用于删除重复行的高效SQL

-- 删除重复行：使用关联子查询
DELETE FROM Products P1
 WHERE 	rowid < (SELECT MAX(P2.rowid) FROM Products P2 where P1.name=P2.name AND P1.price=P2.price );

使用关联子查询的思路时按照{商品名，价格}组合汇总后，求出每个组合最大rowid，然后把其余的行都删除。直接求删除哪些行比较困难，所以这里先求出要留下的行，然后将其从全部组合种提取出来，最后把剩下的行删除——也就是补集的思路。这种使用关联子查询的方法是以{商品名，价格}的组合为单位来处理的（相当于面向过程语言里的中断控制处理）。现在，我们要做的是在子查询里直接求出要删除的rowid。
使用极值函数让每组只留下一个rowid——这一点与之前的做法是一样的。不同的是，这次我们需要把要求下的集合从表Products这个集合种减掉。

-- 用于删除重复行的高效SQL语句（1）：通过EXCEPT求补集
DELETE FROM Products
 WHERE rowid IN ( SELECT rowid FROM Products 
	EXCEPT 
	 	SELECT MAX(rowid) FROM Products GROUP BY name,price) ;

使用EXCEPT可以很轻松求得补集。此外，把EXCEPT换成NOT IN也可以实现相同的结果。

-- 删除重复行的高效SQL语句（2）：通过NOT IN求补集
DELETE FROM Products
 WHERE rowid NOT IN (SELECT MAX(rowid) FROM Products GROUP BY name,price) ;

这两种方法的性能优劣主要取决于表的规模，以及删除的行数与留下的行数之间的比例。不过，第二种方法有一个优点，那就是不支持EXCEPT的数据库也可以使用。
本节小结：
1.在集合运算方面，SQL的标准化进行的比较缓慢，直到现在，实现程度也因数据库的不同而参差不齐，因此大家在使用的时候需要注意。
2.如果集合运算符不指定ALL可选项，重复行会被排除掉，而且这种情况下还会发生排序，所以性能方面不够好。
3.UNION和INTERSECT都是有幂等性这一重要性质，而EXCEPT不具有幂等性。
4.标准SQL没有关系除法的运算符，需要自己实现。
5.判断两个集合是否相等时，可以通过幂等性或一一映射两种方法。
6.使用EXCEPT可以很简单地求得补集。