T-SQL基础（四）之集合运算

三个运算符

T-SQL支持三个集合运算符：UNION、INTERSECT、EXCEPT。

集合运算符查询的一般形式如下：

Query1
<set_operator>
Query2
-- 这里，ORDER BY子句对最终结果集进行排序
[ORDER BY...]

ORDER BY

在逻辑查询处理方面，集合运算符应用于两个查询结果集，且外部的ORDER BY子句（如果有的话）应用于集合运算所得到的结果集。

每个独立的查询可以使用除了ORDER BY之外的所有逻辑查询处理阶段，原因如下：

  1. ORDER BY会对查询结果集进行排序
  2. 排序后的结果集不在表示一个集合而是游标
  3. 集合运算符只能用于集合间运算

因此，每个独立的查询语句中不能使用ORDER BY子句。

其它查询逻辑

对集合运算结果集使用除ORDER BY之外的查询逻辑则易引发逻辑错误：

USE WJChi;

SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
UNION ALL
SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2
WHERE Age>26;

-- 上述写法等价于（注意WHERE条件）
SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
UNION ALL
SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2
WHERE U2.Age>26;

可以借助表表达式对集合运算符运算结果集使用ORDER BY之外的查询逻辑：

USE WJChi;

SELECT * FROM 
(
    SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
    UNION ALL
    SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2 
) AS T
WHERE T.Age>26;

上述查询也可使用派生表之外的表表达式，如：CTE。

集合的列

用于集合运算符的两个查询必须返回相同列数且对应列数据类型相互兼容的结果集。在进行比较运算时，集合运算符会认为两个NULL值是相等的。

集合运算符返回结果集中的列名是第一个查询中的列名：

USE WJChi;

SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
UNION
SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2 
ORDER BY Age

返回结果如下：


UNION

UNION用于获取两个集合的并集。

UNION运算符有两种形式：UNION、UNION ALL：

UNION

USE WJChi;

SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
UNION
SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2 
ORDER BY Age

返回结果如下：


UNION ALL

USE WJChi;

SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
UNION ALL
SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2 
ORDER BY Age

返回结果如下：


从上面两个结果集中可以看到，UNION与UNION ALL的区别是：UNION会去除结果集中的重复元素，而UNION ALL不会，从性能上来讲，UNION ALL优于UNION。严格来讲，UNION ALL运算结果集不能称为集合，因为集合不存在重复元素。

INTERSECT

INTERSECT用于获取两个集合的交集，分为：INTERSECT和INTERSECT ALL两种形式，二者区别同UNION运算符。

INTERSECT

可以使用内联接或者EXSITS谓词来替代INTERSECT，但在比较运算时，INTERSECT将两个NULL值视为相等，而替代方案不会。

INTERSECT只关注行的内容是否相同，不关注行出现的次数：

USE WJChi;

SELECT Name AS 姓名,Age FROM dbo.UserInfo AS U1 
INTERSECT
SELECT Name,Age AS 年龄 FROM dbo.UserInfo AS U2 
ORDER BY Age;

INTERSECT ALL

SQL标准中包含INTERSECT ALL，但在SQL Server2014中未实现该特性，在SQL Server2014中使用INTERSECT ALL会报错：

不支持 INTERSECT 运算符的 'ALL' 版本。

UNION ALL中ALL的含义是返回所有重复行。与之类似，INTERSECT ALL中ALL的含义是不删除交集中的重复项。换个角度看，INTERSECT ALL不仅关心两侧存在的行，还关心每一侧行出现的次数，即：

如果某一数据在第一个输入中出现了a次，在第二个输入中出现了b次，那么在运算结果中该行出现min(a,b)次。

下面，我们借助开窗函数ROW_NUMBER()实现了INTERSECT ALL的效果：

USE WJChi;

SELECT 
ROW_NUMBER() OVER(PARTITION BY Name,Age ORDER BY Age) AS RowNumber,
Name,Age
FROM dbo.UserInfo;

经过开窗函数ROW_NUMBER()的处理后，原本相同的数据被视为不同。

USE WJChi;

-- 实现INTERSECT ALL效果
SELECT T.Name,T.Age FROM 
(
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY Name,Age ORDER BY Age) AS RowNumber,
    Name,Age
    FROM dbo.UserInfo
    
    INTERSECT
    
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY Name,Age ORDER BY Age) AS RowNumber,
    Name,Age
    FROM dbo.UserInfo
) AS T
ORDER BY T.Age;

查询结果如下：


EXCEPT

EXCEPT用于获取两个集合的差集，与UNION与INTERSECT类似，EXCEPT也分为两种形式：EXCEPT和EXCEPT ALL。同样，SQL Server2014也不支持EXCEPT ALL特性。

Query1
EXCEPT
Query2

EXCEPT

与UNION、INTERSECT不同，EXCEPT运算符对于两个查询的先后顺序有要求：EXCEPT返回存在于Query1中出现且不在Query2中出现的行，EXCEPT只关注行是否重复，而不关注行出现的次数。

可以使用外联接或者NOT EXISTS来替代EXCEPT，但在比较运算时，EXCEPT将两个NULL值视为相等，而替代方案不会。

准备如下数据：

USE WJChi;

SELECT Name,Age FROM #temp;
SELECT Name,Age FROM dbo.UserInfo;
except_temp.jpg

那么，下面两条SQL的运算结果集均不包含任何数据：

SELECT Name ,Age FROM #temp 
EXCEPT
SELECT Name,Age FROM dbo.UserInfo
ORDER BY Age;

SELECT Name ,Age FROM dbo.UserInfo 
EXCEPT
SELECT Name,Age FROM #temp
ORDER BY Age;

EXCEPT ALL

EXCEPT ALL与EXCEPT的差异在于，EXCEPT ALL不止考虑行是否重复，还会考虑行出现的次数：

如果某一数据在第一个输入中出现了a次，在第二个输入中出现了b次，那么在运算结果中该行出现a-b次。若a<b则运算结果中不包含该行。

同样，我们借助开窗函数ROW_NUMBER()来实现EXCEPT ALL效果：

USE WJChi;

SELECT 
T.Name,T.Age 
FROM 
(
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY Name,Age ORDER BY Age) AS RowNumber,
    Name,Age
    FROM #temp
    
    EXCEPT
    
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY Name,Age ORDER BY Age) AS RowNumber,
    Name,Age
    FROM dbo.UserInfo
) AS T 
ORDER BY T.Age;

小结

标准SQL支持三个集合运算符：UNION、INTERSECT、EXCEPT，每个运算符均支持两种行为：去重（不带ALL关键字）和保留重复项（带上ALL关键字)。

T-SQL未提供对INTERSECT ALL与EXCEPT ALL的支持，我们可以通过开窗函数ROW_NUMBER()来实现。

另外需要注意一点，集合运算符认为两个NULL是相等的。

推荐阅读

T-SQL基础（三）之子查询与表表达式

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 158,425评论 4赞 361
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,058评论 1赞 291
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,186评论 0赞 243
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,848评论 0赞 204
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,249评论 3赞 286
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,554评论 1赞 216
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,830评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,536评论 0赞 197
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,239评论 1赞 241
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,505评论 2赞 244
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,004评论 1赞 258
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,346评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,999评论 3赞 235
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,060评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,821评论 0赞 194
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,574评论 2赞 271
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,480评论 2赞 267

推荐阅读更多精彩内容