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	sales@villagetoys.com
1000000002	kids place	333 south lake drive	columbus	oh	usa	michelle green	null
1000000003	fun4all	1 sunny place	muncie	in	usa	jim jones	jjones@fun4all.com
1000000004	fun4all	829 riverside drive	phoenix	az	usa	denise l. stephens	dstephens@fun4all.com
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中的集合运算内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

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

相关推荐