Microsoft SQL Server 2008技术内幕：T-SQL语言基础（集合运算）

六、集合运算

6.1 集合运算的特点

6.1.1 集合查询不允许包含order by

• 集合是无序的
• 集合运算涉及的两个查询不能包含order by子句

6.1.2 null相等

• 对行进行比较时，集合运算认为两个null相等

6.2 all 和 distinct

6.2.1 all

• 在进行集合运算时，重复行会参与运算

6.2.2 distinct

• 在进行集合运算时，重复行不参与运算

6.3 union（并集）集合运算

6.3.1 union all

select country, region, city from hr.employees
union all
select country, region, city from sales.customers;

6.3.2 union distinct

• 即使不指定distinct，union运算默认重复行不参与运算

select country, region, city from hr.employees
union
select country, region, city from sales.customers;

6.4 intersect（交集）集合运算

6.4.1 intersect distinct

select country, region, city from hr.employees
intersect 
select country, region, city from sales.customers;

• 与intersect distinct具有相同效果
  • inner join
  • exists
  • 以上都会过滤null

6.4.2 intersect all

• intersect all
  • 重复行也参与计算
  • 如果重复行在交集中，则最终结果中重复行的数量等于两个集合中相应的重复行的数量的最小值
    • 集合a中有x行，集合b中有y行，则为min(x,y)
• SQL Server2008暂未实现intersect all
• 替代方案
  • 引入行号，将重复的变为不重复
  • over( partition by … order by (select 常量) )
    • 没有强烈的排序要求，自然排序

select empid,city,
	ROW_NUMBER() over(partition by city 
						order by (select 0)) 
	as rownum
from HR.Employees;

with intersect_all
as
(
	select country, region, city ,
		row_number() over(partition by country, region,city
							order by (select 0) )
		as rownum
	from hr.employees

	intersect 

	select country, region, city ,
		row_number() over(partition by country, region,city
							order by (select 0) )
		as rownum
	from sales.customers;
)
select country, region, city from intersect_all;

6.5 except（差集）集合运算

6.5.1 except distinct

• A except B
  • 表示在集合A中出现，但不在集合B中出现

select country, region, city from hr.employees
except 
select country, region, city from sales.customers;

• 与except distinct具有相同效果
  • 外联接加过滤条件，只取外部行
  • not exists
  • 以上都会过滤null

6.5.2 except all

• except all
  • 重复行也参与计算
  • 如果重复行在差集中，则最终结果中重复行的数量等于两个集合中相应的重复行的数量的差值
    • 集合a中有x行，集合b中有y行，则为x-y
• SQL Server2008暂未实现except all
• 替代方案

with except_all
as
(
	select country, region, city ,
		row_number() over(partition by country, region,city
							order by (select 0) )
		as rownum
	from hr.employees

	except 

	select country, region, city ,
		row_number() over(partition by country, region,city
							order by (select 0) )
		as rownum
	from sales.customers;
)
select country, region, city from except_all;

6.6 集合运算的优先级

• intersect优先级最高
• union和except优先级相等

6.7 避开不支持的逻辑查询处理

6.7.1 对集合运算结果的操作只允许使用order by

• 使用表表达式，将集合运算结果作为内部查询

with union_distinct
as
(
	select country, region, city from hr.employees
	union
	select country, region, city from sales.customers
)
select country, region, city from union_distinct
group by country;

6.7.2 参与集合运算的单个查询不支持order by

• 使用表表达式，将单个查询作为内部查询

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