站长资源数据库
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