SQL备忘--集合运算

前言

• 本文讨论的是两个子查询结果的合并问题， 是行维度下的合并处理

  例如子查询A查出5条记录、子查询B查出3条记录，那么将两个结果合并，则共返回8条记录

• 行维度上要能进行合并，前置要求是：子查询的列字段是相同的，既要求数量、字段名相同，有要求类型匹配能互相兼容（可以隐式转换匹配）

交集运算（INTERSECT）

交集是取两个子查询中都存在的行数据，不存在的数据会被舍弃

演示案例：

（最后结果返回2、3）

sql案例

SELECT class_name
FROM t_class
WHERE student_num > 50			-- 查询人数超过50人的班级
INTERSECT						-- 交集合并得到满足两个条件的所有班级名称
SELECT class_name
FROM t_class_score
WHERE AVG(score) > 80			-- 查询平均分大于80分的班级名
GROUP BY class_name

交集可以改为等价的内连接查询，例如上例：

SELECT t1.class_name
FROM 
	(SELECT class_name
	FROM t_class
	WHERE student_num > 50)	 t1		-- 查询人数超过50人的班级
JOIN
	(SELECT class_name
	FROM t_class_score
	WHERE AVG(score) > 80			-- 查询平均分大于80分的班级名
	GROUP BY class_name) t2
ON t1.class_name = t2.class_name

并集运算（UNION）

并集是将两个子查询中的行数据合并相加后返回
两个子查询中共同存在的数据（交集数据），可选择在结果中只保留一份数据，还是两份都保留（）

演示案例：

1. 去重，Union [DISTINCT]
   （最后结果返回1、2、3、4）
   

2. 不去重， Union ALL
   （最后结果返回1、2、3、2、3、4， 共六个原酸）
   

sql案例

SELECT class_name
FROM t_class
WHERE student_num > 50			-- 查询人数超过50人的班级
UNION 						    -- 并集合并得到两者的集合，查出的班级是超过50人、或者平均分大于80分的；同时满足两者条件的班级名也只保留了一个
SELECT class_name
FROM t_class_score
WHERE AVG(score) > 80			-- 查询平均分大于80分的班级名
GROUP BY class_name

并集可以改为等价的外连接查询，例如上例：

SELECT COALESCE(t1.class_name, t2.class_name)	-- 外连接会出现NULL的情况，因此用COALESCE在两个结果中选一个不为NULL的
FROM 
	(SELECT class_name
	FROM t_class
	WHERE student_num > 50)	 t1		-- 查询人数超过50人的班级
FULL JOIN
	(SELECT class_name
	FROM t_class_score
	WHERE AVG(score) > 80			-- 查询平均分大于80分的班级名
	GROUP BY class_name) t2
ON t1.class_name = t2.class_name

差集运算（Except）

用于计算子查询结果差集，返回的结果为在子查询1但不在子查询2中的数据

演示案例：

（最后结果返回1）

sql案例

SELECT class_name
FROM t_class
WHERE student_num > 50			-- 查询人数超过50人的班级
EXCEPT 						    -- 排除掉人数超过50人的班级中、平均分大于80的班级
SELECT class_name
FROM t_class_score
WHERE AVG(score) > 80			-- 查询平均分大于80分的班级名
GROUP BY class_name

MYSQL 不支持EXCEPT运算符
ORACLE 21c开始支持EXCEPT，以前使用等价的MINUS关键字