PostgreSQL / 系统课时
使用集合运算
使用集合运算
本课解决多个查询结果需要合并、求交集或求差集的问题。你将使用 UNION、INTERSECT 和 EXCEPT 比较结果集,并掌握去重、排序和列类型要求。
学习目标
- 使用三种集合运算组合查询结果
- 区分
UNION与UNION ALL - 正确处理列数量、类型和排序规则
三种集合运算
UNION 合并两边结果并去重;INTERSECT 只保留两边都出现的行;EXCEPT 保留左边存在、右边不存在的行。
参与运算的查询必须返回相同数量的列,对应列的数据类型也要兼容。结果列名通常取自第一个查询。
主示例:比较线上与门店用户
下面的 SQL 创建两个用户来源表,并依次计算并集、交集和差集。
sql
CREATE TEMP TABLE online_users (
user_id integer PRIMARY KEY
);
CREATE TEMP TABLE store_users (
user_id integer PRIMARY KEY
);
INSERT INTO online_users (user_id) VALUES (1), (2), (3);
INSERT INTO store_users (user_id) VALUES (3), (4);
-- 并集:所有出现过的用户
SELECT user_id FROM online_users
UNION
SELECT user_id FROM store_users
ORDER BY user_id;
-- 交集:同时在线上和门店出现的用户
SELECT user_id FROM online_users
INTERSECT
SELECT user_id FROM store_users
ORDER BY user_id;
-- 差集:只在线上出现的用户
SELECT user_id FROM online_users
EXCEPT
SELECT user_id FROM store_users
ORDER BY user_id;预期结果
text
并集
user_id
---------
1
2
3
4
交集
user_id
---------
3
差集
user_id
---------
1
2EXCEPT 有方向性:交换左右查询后,结果会变成只在门店出现的用户,也就是 4。
UNION 与 UNION ALL
UNION 会去除重复行,通常需要额外的比较或排序工作。确认业务允许重复,或后续还要自行聚合时,可以使用 UNION ALL 保留全部行,通常开销更低。
是否重复是按整行判断,而不是只看某一列。查询返回多列时,只有所有对应列都相同,才会被视为重复行。
排序与括号
放在集合运算末尾的 ORDER BY 作用于最终结果。需要对某个分支单独执行 ORDER BY、LIMIT 时,应使用括号包住该分支。
混合多个集合运算时,INTERSECT 的结合优先级高于 UNION 和 EXCEPT。为了避免误读,复杂表达式应主动加括号明确执行顺序。
类型与 NULL
对应列必须能够转换为共同类型,例如整数可以与更宽的数值类型组合。类型差异过大时,应显式使用 CAST。
集合运算会把两个 NULL 视为可去重的相同行值,因此不要直接套用普通比较中 NULL = NULL 的理解。
按自己的节奏,确认真正理解。