【Microsoft SQL Server 2008 技术内幕：T-SQL语言基础】四、集合运算

wxin

sql2008 t-sql
集合运算 (Set Operations)
UNION 并集

• UNION [DISTINCT]：求并集，删除重复行（隐含DISTINCT）
  
• UNION ALL：求并集，不比较，不删除重复行
  

INTERSECT 交集

• INTERSECT [DISTINCT]：求交集（隐含DISTINCT），删除重复行
  
• INTERSECT ALL (Sql Server不支持)：求交集，返回有重复行，但只返回重复行数较少的那个多集(参与交集运算的集合)的所有重复行。假如行R在多集A中重复出现x次，在多集B中重复出现y次，则R在结果集中出现min(x, y)次。
  

EXCEPT 差集，可使用外联或NOT EXISTS实现

• EXCEPT [DISTINCT]：求差集（隐含DISTINCT），删除重复行
  
• EXCEPT ALL (Sql Server不支持)：求差集，返回有重复行。假如行R在第一个多集中重复出现x次，在第二个多集中重复出现y次，x>y，则R在结果集中出现x-y次。
  

优先级：

• INTERSECT > ( UNION = EXCEPT )
  

集合运算的限制：

• 参与结合运算的表表达式中无法使用ORDER BY，ORDER BY只能作用于集合运算的结果。
  
• 但如果要想在参与集合运算的表表达式中使用TOP来限制行数，就需要使用表表达式将包含ORDER BY的查询包装一次。这种情况下ORDER BY只为TOP提供逻辑服务，并不控制结果的排序。
  

-- ==================================================
-- Sql Server INTERSECT ALL workaround
-- ==================================================
with INTERSECT_ALL as (
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- only those intersection(smaller) RowNum will remain
, region
, country
, city
from HR.Employees
INTERSECT
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- only those intersection(smaller) RowNum will remain
, region
, country
, city  
from Sales.Customers
)
select country, region, city
from INTERSECT_ALL

-- ==================================================
-- Sql Server EXCEPT ALL workaround
-- ==================================================
with EXCEPT_ALL as (
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- intersection(smaller) RowNum will be removed
, region
, country
, city
from HR.Employees
EXCEPT
select ROW_NUMBER() over (
partition by region, country, city
order by (select 0)
) as rownum -- intersection(smaller) RowNum will be removed
, region
, country
, city  
from Sales.Customers
)
select country, region, city
from EXCEPT_ALL
-- ==================================================
-- Using ORDER BY inside set operation
-- ==================================================
select empid, orderid, orderdate
from (
select top(2) empid, orderid, orderdate
from Sales.Orders
where empid = 3
order by orderdate desc, orderid desc
) as D1
union all
select empid, orderid, orderdate
from (
select top(2) empid, orderid, orderdate
from Sales.Orders
where empid = 5
order by orderdate desc, orderid desc
) as D2