SQL Server中的集合运算: UNION, EXCEPT和INTERSECT示例代码详解
16lz
2022-01-04
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)UNIONSELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)
--提取表/派生表(derived table)可以是多列,列名、顺序可以不同,但列数必须相同SELECT * FROM (VALUES('a','Anna'),('b','Bob'),('c','Cassie'),('e','Elina')) Table1 (FC, Name)UNION ALLSELECT * FROM (VALUES('a','Anna'),('b','Bob'),('c','Cassie'),('d','David')) Table2 (FC, Name)
--返回结果为:[e]SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)EXCEPTSELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)
--返回结果为:[a,b,c]SELECT FC FROM (VALUES('a'),('b'),('c'),('e')) Table1 (FC)INTERSECTSELECT FC FROM (VALUES('a'),('b'),('c'),('d')) Table2 (FC)
1.使用UNION
代替Where
子句中的OR
,查询速度更快
--使用Where子句 + ORSELECT name, population, area FROM world WHERE area > 3000000 OR population > 25000000--使用UNIONSELECT name, population, area FROM world WHERE area > 3000000UNIONSELECT name, population, area FROM world WHERE population > 25000000
假设存在表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])EXCEPTSELECT [cust_id] FROM [Customers]--过滤出列表中存在于数据库中的项--返回结果为['1000000004','1000000005']SELECT [Id] AS [cust_id] FROM(VALUES('1000000004'),('1000000005'),('1000000006'),('1000000007')) dt ([Id])INTERSECTSELECT [cust_id] FROM [Customers]
Set Operators - EXCEPT and INTERSECT
Set Operators - UNION
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Android(安卓)- Manifest 文件 详解
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Andorid Dialog 示例【慢慢更新】
- Selector、shape详解(一)
- android2.2资源文件详解4--menu文件夹下的菜单定义
- Android(安卓)PureMVC
- Android发送短信方法实例详解
- Ubunu下搭建android NDK环境