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

sql2008 t-sql

[b][size=large]集合运算 (Set Operations)[/size][/b]

[b]UNION 并集[/b]
[list]
[*]UNION [DISTINCT]：求并集，删除重复行（隐含DISTINCT）
[*]UNION ALL：求并集，不比较，不删除重复行
[/list]
[b]INTERSECT 交集[/b]
[list]
[*]INTERSECT [DISTINCT]：求交集（隐含DISTINCT），删除重复行
[*]INTERSECT ALL (Sql Server不支持)：求交集，返回有重复行，但只返回重复行数较少的那个多集(参与交集运算的集合)的所有重复行。假如行R在多集A中重复出现x次，在多集B中重复出现y次，则R在结果集中出现min(x, y)次。
[/list]
[b]EXCEPT 差集[/b]，可使用外联或NOT EXISTS实现
[list]
[*]EXCEPT [DISTINCT]：求差集（隐含DISTINCT），删除重复行
[*]EXCEPT ALL (Sql Server不支持)：求差集，返回有重复行。假如行R在第一个多集中重复出现x次，在第二个多集中重复出现y次，x>y，则R在结果集中出现x-y次。
[/list]
[b]优先级：[/b]
[list]
[*]INTERSECT > ( UNION = EXCEPT )
[/list]
[b]集合运算的限制：[/b]
[list]
[*]参与结合运算的表表达式中无法使用ORDER BY，ORDER BY只能作用于集合运算的结果。
[*]但如果要想在参与集合运算的表表达式中使用TOP来限制行数，就需要使用表表达式将包含ORDER BY的查询包装一次。这种情况下ORDER BY只为TOP提供逻辑服务，并不控制结果的排序。
[/list]

 -- ================================================== 
 -- 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