sql server 交集,差集的用法详解

概述

为什么使用集合运算：

　　在集合运算中比联接查询和exists/not exists更方便。

并集运算（union）

并集：两个集合的并集是一个包含集合a和b中所有元素的集合。

在t-sql中。union集合运算可以将两个输入查询的结果组合成一个结果集。需要注意的是：如果一个行在任何一个输入集合中出现，它也会在union运算的结果中出现。t-sql支持以下两种选项：

（1）union all：不会删除重复行

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

（2）union：会删除重复行

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

交集运算（intersect)

交集：两个集合（记为集合a和集合b）的交集是由既属于a，也属于b的所有元素组成的集合。

在t-sql中，intersect集合运算对两个输入查询的结果取其交集，只返回在两个查询结果集中都出现的行。

　　intersect集合运算在逻辑上会首先删除两个输入集中的重复行，然后返回只在两个集合中中都出现的行。换句话说：如果一个行在两个输入集中都至少出现一次，那么交集返回的结果中将包含这一行。

　　例如，下面返回既是雇员地址，又是客户地址的不同地址：

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

这里需要说的是，集合运算对行进行比较时，认为两个null值相等，所以就返回该行记录。

差集运算（except）

差集：两个集合（记为集合a和集合b）的由属于集合a，但不属于集合b的所有元素组成的集合。

　在t-sql中，集合之差使用except集合运算实现的。它对两个输入查询的结果集进行操作，反会出现在第一个结果集中，但不出现在第二个结果集中的所有行。

　　except结合运算在逻辑上首先删除两个输入集中的重复行，然后返回只在第一个集合中出现，在第二个结果集中不出现的所有行。换句话说：一个行能够被返回，仅当这个行在第一个输入的集合中至少出现过一次，而且在第二个集合中一次也没出现过。

　　此外，相比union和intersect，两个输入集合的顺序是会影响到最后返回结果的。

　　例如，借助except运算，我们可以方便地实现属于a但不属于b的场景，下面返回属于员工抵制，但不属于客户地址的地址记录：

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

集合运算优先级

sql定义了集合运算之间的优先级：intersect最高，union和except相等。

　　换句话说：首先会计算intersect，然后按照从左至右的出现顺序依次处理优先级相同的运算。

-- 集合运算的优先级
select country, region, city from production.suppliers
except
select country, region, city from hr.employees
intersect
select country, region, city from sales.customers;

上面这段sql代码，因为intersect优先级比except高，所以首先进行intersect交集运算。因此，这个查询的含义是：返回没有出现在员工地址和客户地址交集中的供应商地址。

集合运算的优先级

1.intersect>union=except

2.首先计算intersect,然后从左到右的出现顺序依次处理优先级的相同的运算。

3.可以使用圆括号控制集合运算的优先级，它具有最高的优先级。

在排序函数的over字句中使用order by ( select <常量> )可以告诉sql server不必在意行的顺序。

使用表表达式避开不支持的逻辑查询处理

集合运算查询本身并不持之除order by意外的其他逻辑查询处理阶段，但可以通过表表达式来避开这一限制。

　　解决方案就是：首先根据包含集合运算的查询定义一个表表达式，然后在外部查询中对表表达式应用任何需要的逻辑查询处理。

（1）例如，下面的查询返回每个国家中不同的员工地址或客户地址的数量：

select country, count(*) as numlocations
from (select country, region, city from hr.employees 
union 
select country, region, city from sales.customers) as ugroup by country;

（2）例如，下面的查询返回由员工地址为3或5的员工最近处理过的两个订单：、

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;

到此这篇关于sql server 交集,差集的用法详解的文章就介绍到这了,更多相关sql server 交集,差集 内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！