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

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