SQL经典案例之表的集合运算和优先级

SQL经典案例之表的集合运算符和优先级

• 创建测试数据
• 并集（UNION）
• 交集（INTERSECT）
• 差集（EXCEPT）
• 集合运算符的优先级

集合运算（Set Operations）是一种对两个或多个表进行操作的方法，从而得到一个新的结果集。主要的集合运算包括

• 并集（UNION）
• 交集（INTERSECT）
• 差集（EXCEPT）
• 笛卡尔积（CROSS JOIN）

注意事项：

1. 列数和类型匹配：进行集合运算时，各SELECT语句的列数必须相同，且相应列的数据类型应匹配或兼容。SQLite使用动态数据类型，不要求字段类型匹配或兼容。MySQL可能会尝试执行隐式类型转换。
2. 排序：如果需要排序结果集，可以在最外层加上 ORDER BY 子句，即集合运算符之前的SELECT语句不能出现排序子句

创建测试数据

create table table1(column1 int, column2 varchar(10));
insert into table1 values(1,'Oracle');
insert into table1 values(2,'MySQL');
insert into table1 values(3,'PostgreSQL');
create table table2(column1 int, column2 varchar(10));
insert into table2 values(1,'Oracle');
insert into table2 values(2,'MySQL');
insert into table2 values(4,'GaussDB');

并集（UNION）

并集运算用于合并两个或多个表的结果集，并去除重复的行。UNION ALL 会保留所有的重复行。

SELECT column1, column2, ...
FROM table1
UNION [DISTINCT | ALL]
SELECT column1, column2, ...
FROM table2;

• DISTINCT：对合并后的结果集进行去重，「 默认值 」
• ALL：保留合并结果中的重复数据

-- UNION
select column1,column2 from table1
UNION
select column1,column2 from table2;
-- 等价于 FULL JOIN
select COALESCE(t1.column1,t2.column1),COALESCE(t1.column2,t2.column2)
from table1 t1
full join table2 t2 on t1.column1=t2.column1 and t1.column2=t2.column2;

COALESCE函数是当左表字段为空时返回右表的字段，MySQL不支持全外连接查询。

交集（INTERSECT）

交集运算返回两个或多个表的结果集中都存在的行。

SELECT column1, column2, ...
FROM table1
INTERSECT [DISTINCT | ALL]
SELECT column1, column2, ...
FROM table2;

• DISTINCT：对合并后的结果集进行去重，「 默认值 」
• ALL：保留合并结果中的重复数据

MySQL不支持 INTERSECT 运算符；
PostgreSQL支持完整的DISTINCT和ALL选项；
Oracle21c开始支持ALL选项

-- INTERSECT
select column1,column2 from table1
INTERSECT
select column1,column2 from table2;
-- 等价于 DISTINCT + JOIN
select t1.column1,t1.column2 from table1 t1
join table2 t2 on t1.column1=t2.column1 and t1.column2=t2.column2;

差集（EXCEPT）

差集运算返回第一个表的结果集中存在但在第二个表的结果集中不存在的行。有些数据库系统（如SQL Server）支持这个操作，但在MySQL中需要使用其他方法（如左连接和 WHERE 子句）来实现。

SELECT column1, column2, ...
FROM table1
EXCEPT [DISTINCT | ALL]
SELECT column1, column2, ...
FROM table2;

MySQL不支持 EXCEPT 运算符；
PostgreSQL支持完整的DISTINCT和ALL选项；
Oracle21c开始支持EXCEPT关键字，等价于早期的MINUS运算符

-- EXCEPT
select column1,column2 from table1
EXCEPT
select column1,column2 from table2;
-- 等价于 LEFT JOIN + NULL
select t1.column1,t1.column2 from table1 t1
left join table2 t2 on t1.column1=t2.column1 and t1.column2=t2.column2
where t2.column1 is null;
-- Oracle 早期版本等价于 MINUS
select column1,column2 from table1
MINUS
select column1,column2 from table2;

集合运算符的优先级

• SQL标准：INTERSECT > UNION/EXCEPT
• Oracle和SQLite中所有集合运算符的优先级相同
• 相同等级运算符按从左到右执行顺序

-- PostgreSQL
select 1 as n
UNION ALL
select 1
INTERSECT
select 1;

-- Oracle 
select 1 as n from dual
UNION ALL
select 1 from dual
INTERSECT
select 1 from dual;
-- 括号调整执行顺序
select 1 as n from dual
UNION ALL
(select 1 from dual
INTERSECT
select 1 from dual);