T-SQL 语言基础： 集合运算(Union, Intersect, Except)

目录

1. 概念介绍
2. 并集运算 (Union)
   • UNION ALL 示例
   • UNION 示例
3. 交集运算 (Intersect)
   • INTERSECT 示例
4. 差集运算 (Except)
   • EXCEPT 示例
5. 集合运算优先级
6. 总结
7. 引用
8. 数据库脚本下载

概念介绍

在 SQL Server 中，集合运算用于对两个输入查询结果进行操作。集合运算包括并集 (Union)、交集 (Intersect) 和差集 (Except)。这些运算可以处理包含重复记录的多集。集合运算结果中的列名由第一个查询决定，两个查询必须包含相同的列数且相应列的数据类型必须兼容。

-- 集合运算时对输入两个集合（更确切的说，应该是多集运算）参与运算的集合可以是由两个输入的查询结果。
	-- 多集不是真正的集合，可以包含重复的记录
-- T-SQL 支持3中集合运算： 并集 UNION，交集 UNION 和差集 EXCEPT，
	-- EXCEPT 和 INTERSECT 运算是 2005 引入的
-- 集合运算会对两个输入查询生成的结果集进行逐行比较，根据比较结果和所使用的集合运算来确定某一行是否应该包含再集合运算的结果中
	-- 集合本身是无序的，所以集合运算设计的两个查询不能包含 ORDER BY 子句
-- 参与集合运算的两个查询结果集必须包含相同的列数，而且相应列必须具有兼容的数据类型
	-- 兼容类型 是指优先级较低的数据类型必须能隐式的转换为高级的数据类型

-- 集合运算结果中的列名由第一个查询决定，如果要为结果列分配列名，应该再第一个查询中分配相应的列名

-- 两个特点： 
	-- 1. 对行进行比较时，集合运算认为两个 NULL 相等
	-- 2. ANSI SQL 对每种集合运算都支持两个选项： DISTINCT 和 ALL
		-- DISTINCT 逻辑上可以从两个输入的多集中消除重复行
		-- ALL 对两个多集进行运算时不会删除重复行
			-- UNION ALL 并集
			-- UNION 隐含 DINSTINCT

并集运算 (Union)

并集运算将两个集合合并，返回所有元素。SQL Server 支持 UNION ALL 和 UNION。

UNION ALL 示例

UNION ALL 返回输入多集中出现的所有行，不进行去重。

SELECT country, region, city FROM HR.Employees
UNION ALL
SELECT country, region, city FROM Sales.Customers;

输出：101行

UNION 示例

UNION 运算隐含去重逻辑，返回的集合中包含两个输入集合中的所有行，不包含重复记录。

SELECT country, region, city FROM HR.Employees
UNION 
SELECT country, region, city FROM Sales.Customers;

输出：71行

交集运算 (Intersect)

交集运算返回两个输入集合中都存在的行。SQL Server 支持 INTERSECT 运算。

-- 交集由既属于A，也属于B的所有元素组成的集合

INTERSECT 示例

INTERSECT 运算返回两个输入集合中的共同行。

SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;

输出：

差集运算 (Except)

差集运算返回第一个结果集中存在但不在第二个结果集中的行。SQL Server 支持 EXCEPT 运算。

EXCEPT 示例

EXCEPT 运算返回第一个集合中存在、第二个集合中不存在的行。

SELECT country, region, city FROM HR.Employees
EXCEPT
SELECT country, region, city FROM Sales.Customers;

输出：

集合运算优先级

在 SQL Server 中，集合运算的优先级如下：

1. INTERSECT 运算的优先级高于 UNION 和 EXCEPT。
2. UNION 和 EXCEPT 运算的优先级相等。

优先级示例

查询返回未出现在雇员地址和客户地址交集中的供应商地址。

SELECT country, region, city FROM Production.Suppliers
EXCEPT 
SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;

输出：

总结

集合运算在 SQL 查询中非常有用，可以用来合并、比较和筛选两个结果集。了解并集、交集和差集运算的用法以及它们的优先级有助于编写高效的 SQL 查询。

引用

• SQL Server Documentation

数据库脚本下载

TSQLFundamentals2008