SqlServer 集合运算符

1、集合运算符概述

（1）集合运算符运用与集合之间的运算、

（2）多元集合： 指的是来自两个输入查询的集合，可能包含重复项

（3）T-SQL 支持三种集合运算符 union 、intersect 、except

2、集合运算符的一般形式和要求

（1）一般形式

input query1
<set_operater>
input query2
order by ...

(2)要求

①安装集合运算符定义，集合运算符应用于两个集合，集合是无序的，因此所涉及的两个query 都不能有order by子句 ，但是可以在运算符结果中使用order by 进行排序

②集合运算符所涉及的两个查询应当具有相同的列数，并且数据类型兼容

③集合运算符结果由第一个query确定，因此如果需要分配别名，应该在第一个查询中分配

④集合运算符比较行时，认为两个NULL 是相等的

⑤标准的SQL对运算符支持 两种行为 ， DISTINCT 和 ALL , distinct 消除重复行、all 不会消除重复行，直接返回多元集合，sql server 三种集合运算符默认都是distinct , 且只有UNION支持 all ,需显式指定

3、UNION 运算符

常用，略

4、INTERSECT

(1)intersect 用于取集合A和B 的交集

(2)只要某行返回了，他在多个多元集合中都至少出现一次

(3）返回的结果默认是distinct ,消除重复行的，且认为NULL和NULL是相等的

(4) 标准SQL 实际支持 intersect all 的行为，但是在sql server 2012 中尚未实现

5、EXCEPT运算符

（1）用于计算A和B 的差异（A-B）

（2）返回的行，至少在A中出现1次、在B中出现0次

6、集合运算符的优先级

intersect > union = except

7、规避不支持的逻辑运算

集合元素符的结果，只能使用order by 逻辑阶段，如果需要对运算符结果应用除了order by 之外的其他逻辑阶段，就需要使用表表达式，如派生表

select country ,count(*) as numlocations
from 
(
  select country, region, city from HR.Employees
  UNION 
  select country, region, city from Sales.Customers
) as U
group by U.country

有时确实需要在在集合运算涉及的两个query中使用orderby ,比如两个query都要限制行数、分页等 ，也可以使用类似的方案解决

SELECT empid, orderid, orderdate
FROM (SELECT empid, orderid, orderdate
      FROM Sales.Orders
      WHERE empid = 3
      ORDER BY orderdate DESC, orderid DESC
      OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS D1

UNION ALL

SELECT empid, orderid, orderdate
FROM (SELECT empid, orderid, orderdate
      FROM Sales.Orders
      WHERE empid = 5
      ORDER BY orderdate DESC, orderid DESC
      OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS D2;