SQL Server中的集合运算: UNION, EXCEPT和INTERSECT示例代码详解

sql server中的集合运算包括union(合并),except(差集)和intersect(相交)三种。

集合运算的基本使用

1.union(合并两个查询结果集，隐式dinstinct，删除重复行)

--合并两个提取表/派生表(derived table), 返回结果为:[a,b,c,d,e]
select fc from (values('a'),('b'),('c'),('e')) table1 (fc)
union
select fc from (values('a'),('b'),('c'),('d')) table2 (fc)

2.union all(简单合并两个查询结果集，不删除重复行)

--提取表/派生表(derived table)可以是多列，列名、顺序可以不同，但列数必须相同
select * from (values('a','anna'),('b','bob'),('c','cassie'),('e','elina')) table1 (fc, name)
union all
select * from (values('a','anna'),('b','bob'),('c','cassie'),('d','david')) table2 (fc, name)

3.except(返回出现在第一个结果集但不出现在第二个结果集中的所有行)

--返回结果为:[e]
select fc from (values('a'),('b'),('c'),('e')) table1 (fc)
except
select fc from (values('a'),('b'),('c'),('d')) table2 (fc)

4.intersect(返回第一个查询结果集和第二个查询结果集共有的部分)

--返回结果为:[a,b,c]
select fc from (values('a'),('b'),('c'),('e')) table1 (fc)
intersect
select fc from (values('a'),('b'),('c'),('d')) table2 (fc)

集合运算的使用场景

1.使用union代替where子句中的or，查询速度更快

--使用where子句 + or
select name, population, area from world where area > 3000000 or population > 25000000

--使用union
select name, population, area from world where area > 3000000
union
select name, population, area from world where population > 25000000

2.使用except和intersect, 过滤出列表中不存在/存在于数据库中的项

假设存在表customers, 数据如下表所示

cust_id	cust_name	cust_address	cust_city	cust_state	cust_country	cust_contact	cust_email
1000000001	village toys	200 maple lane	detroit	mi	usa	john smith	[e-mail]
1000000002	kids place	333 south lake drive	columbus	oh	usa	michelle green	null
1000000003	fun4all	1 sunny place	muncie	in	usa	jim jones	[e-mail]
1000000004	fun4all	829 riverside drive	phoenix	az	usa	denise l. stephens	[e-mail]
1000000005	the toy store	4545 53rd street	chicago	il	usa	kim howard	null

--过滤出列表中不存在于数据库中的项
--返回结果为['1000000006','1000000007']
select [id] as [cust_id] from
(
values('1000000004'),('1000000005'),('1000000006'),('1000000007')
) dt ([id])
except
select [cust_id] from [customers]

--过滤出列表中存在于数据库中的项
--返回结果为['1000000004','1000000005']
select [id] as [cust_id] from
(
values('1000000004'),('1000000005'),('1000000006'),('1000000007')
) dt ([id])
intersect
select [cust_id] from [customers]

--对于sqlserver 2008以前的版本
select [id] as [cust_id] from
(
  select '1000000004' union all
  select '1000000005' union all
  select '1000000006' union all
  select '1000000007'
) dt ([id])
intersect
--except
select [cust_id] from [customers]

//使用c#动态生成sql语句
var list = new list<string>(){"1000000004","1000000005","1000000006","1000000007"};
string sqlquery = string.format($@"
  select [id] as [cust_id] from
  (
    values('{string.join("'),('", list)}')
  ) dt ([id]
  intersect
  --except
  select [cust_id] from [customers]"
);

更多参考

set operators - except and intersect

set operators - union

到此这篇关于sql server中的集合运算: union, except和intersect的文章就介绍到这了,更多相关sql server中的集合运算内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！