SQL集合运算

 集合论是SQL语言的根基。

1 集合运算

注意事项：

1）SQL能操作具有重复行的集合，可以通过可选项ALL来支持。

如果直接使用UNION或INTERSECT，结果里不会出现重复的行。如果想在结果里留下重复行，可以加上可选项ALL。写作UNION ALL。

集合运算符为了排除掉重复行，会默认发生排序，而加上可选项ALL之后，就不会再排序了，所以性能会提升。

2) 集合运算符有优先级。

INTERSECT比UNION和EXCEPT的优先级更高。

1.1 实践

1.1.1 检查集合相等性

图 两个集合t_table_a与t_table_b

-- UNION,如果合并后与两个集合的行数一致，则两个集合相同
SELECT CASE 
WHEN COUNT(*) = (SELECT COUNT(*) FROM t_table_a)
AND COUNT(*) = (SELECT COUNT(*) FROM t_table_b) 
THEN '集合相等'  ELSE '集合不相等' END AS res 
FROM 
(SELECT *
FROM t_table_a
UNION
SELECT *
FROM t_table_b)tmp
-- 集合运算，如果A与B的并集等于A与B的交集。 则A=B
SELECT CASE WHEN COUNT(*) = 0 THEN '相等' ELSE '不相等' END AS res
FROM 
((SELECT *
FROM t_table_a
UNION 
SELECT *
FROM t_table_b)
EXCEPT 
(
	SELECT *
	FROM t_table_a
	INTERSECT 
	SELECT *
	FROM t_table_b
))tmp;

1.1.2 用差集实现关系除法运算

图 员工技能t_emp_skills 表与技能t_skills 表及期望输出

需求：找出精通t_skills 表所有技能的员工。

-- 差集 EXCEPT
SELECT DISTINCT emp 
FROM t_emp_skills e 
WHERE NOT EXISTS 
(
	 SELECT skill
	 FROM t_skills
	 EXCEPT 
	 SELECT skill 
	 FROM t_emp_skills 
	 WHERE emp = e.emp 
);	

需求：找出刚好拥有全部技术的员工（即擅长的技能和技能表的一摸一样，不多也不少）。

SELECT emp 
FROM t_emp_skills e 
WHERE NOT EXISTS (
	(SELECT skill
	FROM t_skills 
	EXCEPT 
	SELECT skill 
	FROM t_emp_skills
	WHERE emp = e.emp)
)
GROUP BY emp 
HAVING COUNT(*) = (SELECT COUNT(*) FROM t_skills);

1.1.3 寻找相等的子集

图 供应商-零件关系t_sup_parts表及期望输出

需求：找出经营的零件在种类数和种类上都完全相同的供应商组合。

SELECT s1.sup sup1,s2.sup sup2
FROM t_sup_parts s1
CROSS JOIN t_sup_parts s2 
WHERE s1.sup < s2.sup AND s1.part = s2.part
GROUP BY s1.sup,s2.sup
HAVING COUNT(*) = (SELECT COUNT(*) FROM t_sup_parts WHERE sup = s1.sup)
AND COUNT(*) = (SELECT COUNT(*) FROM t_sup_parts WHERE sup = s2.sup);

1.1.4 高效删除重复行

图 存在重复数据的t_fruit_info表

需求：删除表中重复的数据。

-- 使用关联子查询
DELETE FROM t_fruit_info f
WHERE row_id < (
	 SELECT *
	 FROM (
		 SELECT MAX(row_id)
		 FROM t_fruit_info 
		 WHERE `name` = f.name AND price = f.price
	 ) temp
);

关联子查询性能比较差。

-- 用差集运算 
DELETE FROM t_fruit_info
WHERE row_id IN (
	SELECT * FROM 
	(
		SELECT row_id
		FROM t_fruit_info 
		EXCEPT 
		(SELECT row_id
		FROM t_fruit_info
		GROUP BY `name`,price)
	) tmp
);
-- NOT IN 求补集 
DELETE FROM t_fruit_info
WHERE row_id NOT IN (
	SELECT * FROM 
	(
		SELECT MAX(row_id)
		FROM t_fruit_info 
		GROUP BY `name`,price
	) tmp
);