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运算符有两种形式:UNIONUNION 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

返回结果如下:

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

INTERSECT

INTERSECT用于获取两个集合的交集,分为:INTERSECTINTERSECT 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 ALLALL的含义是返回所有重复行。与之类似,INTERSECT ALLALL的含义是不删除交集中的重复项。换个角度看,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用于获取两个集合的差集,与UNIONINTERSECT类似,EXCEPT也分为两种形式:EXCEPTEXCEPT ALL。同样,SQL Server2014也不支持EXCEPT ALL特性。

Query1
EXCEPT
Query2

EXCEPT

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

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

准备如下数据:

USE WJChi;
?
SELECT Name,Age FROM #temp;
SELECT Name,Age FROM dbo.UserInfo;

那么,下面两条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 ALLEXCEPT的差异在于,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支持三个集合运算符:UNIONINTERSECTEXCEPT,每个运算符均支持两种行为:去重(不带ALL关键字)和保留重复项(带上ALL关键字)。

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

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

推荐阅读

T-SQL基础(三)之子查询与表表达式

原文地址:https://www.cnblogs.com/Cwj-XFH/p/10035453.html

时间: 11-28

T-SQL基础(四)之集合运算的相关文章

详解SQL集合运算

以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化. 本系列[T-SQL基础]主要是针对T-SQL基础的总结. [T-SQL基础]01.单表查询-几道sql查询题 [T-SQL基础]02.联接查询 [T-SQL基础]03.子查询 [T-SQL基础]04.表表达式-上篇 [T-SQL基础]04.表表达式-下篇 [T-SQL基础]05.集合运算 [T-SQL基础]06.透视.逆透视.分组集 [T-SQL基础]07.数据修改 [T-SQL基础]08.事务和并发 [

SQL集合运算

转载:详解SQL集合运算 本系列[T-SQL基础]主要是针对T-SQL基础的总结. [T-SQL基础]01.单表查询-几道sql查询题 [T-SQL基础]02.联接查询 [T-SQL基础]03.子查询 [T-SQL基础]04.表表达式-上篇 [T-SQL基础]04.表表达式-下篇 [T-SQL基础]05.集合运算

笔记-Microsoft SQL Server 2008技术内幕:T-SQL语言基础-06 集合运算

T-SQL支持3种集合运算:并集(UNION).交集(INTERSECT)和差集(EXCEPT).集合运算涉及的两个查询不能包含ORDER BY子句. UNION ALL集合运算 UNION ALL不会对行进行比较,也不会删除重复行.假设查询Query1返回m行,查询Query2返回n行,则Query1 UNION ALL Query2返回(m+n)行. SELECT country, region, city FROM HR.Employees UNION ALL SELECT country

sql的基础语句-单行函数,dual,数字函数,日期函数,表连接,集合运算,分组报表,单行子查询,多行子查询

3. 单行函数 3.1 转换函数 select ascii('A'),chr(65) from dual; select to_char(1243123),1231451 from dual;靠左边的就是字符串,靠右边的就是数字 select to_char(123512a121) from dual;   --错误的写法,没有引号表示数字,但是数字里面包含了字母,不合法的输入值 select to_number('123141211') from dual; select to_number(

[SQL] SQL 基础知识梳理(四) - 数据更新

SQL 基础知识梳理(四) - 数据更新 [博主]反骨仔 [原文]http://www.cnblogs.com/liqingwen/p/5929786.html 目录 一.插入数据 1.INSERT 语句的基本语法 --语法: --INSERT INTO <表名>(列1, 列2, ...) VALUES (值1, 值2, ...) INSERT INTO dbo.Shohin ( shohin_id , shohin_mei , shohin_bunrui , hanbai_tanka , s

SQL集合运算 差集 并集 交

SQL-3标准中提供了三种对检索结果进行集合运算的命令:并集UNION:交集INTERSECT:差集EXCEPT(在Oracle中叫做 MINUS).在有些数据库中对此的支持不够充分,如MySql中只有UNION,没有其他两种.实际上这些运算都可以通过普通的SQL来实现,虽然有时有些繁琐. 假设有两个表(或视图)s,t,s中有两个字段sa,sb:t中有两个字段ta,tb: 差集EXCEPT: PLAIN TEXT SQL: SELECTsaFROMs EXCEPT SELECTtaFROMt;

SQL Server调优系列基础篇(索引运算总结)

原文:SQL Server调优系列基础篇(索引运算总结) 前言 上几篇文章我们介绍了如何查看查询计划.常用运算符的介绍.并行运算的方式,有兴趣的可以点击查看. 本篇将分析在SQL Server中,如何利用先有索引项进行查询性能优化,通过了解这些索引项的应用方式可以指导我们如何建立索引.调整我们的查询语句,达到性能优化的目的. 闲言少叙,进入本篇的正题. 技术准备 基于SQL Server2008R2版本,利用微软的一个更简洁的案例库(Northwind)进行解析. 简介 所谓的索引应用就是在我们

SQL之集合运算

UNION(并集)集合运算 1.UNION ALL集合运算 该集合运算返回在输入的多集中出现的所有行,它实际上不会对行进行比较,也不会删除重复行.假设查询Query1返回m行,查询Query2返回n行,则该集合运算后返回(m+n)行 1 SELECT country, region, city FROM HR.Employees 2 UNION ALL 3 SELECT country, region, city FROM Sales.Customers; 2.UNION DISTINCT集合运

[Oracle系列整理04] oracle pl/sql 基础

PL/SQL块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事务控制语句 (COMMIT,ROLLBACK,SAVEPOINT),而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句 (GRANT,REVOKE) 1.检索单行数据    1.1使用标量变量接受数据  v_ename emp.ename%type;  v_sal   emp.sal%type;  select ename,sal into v_ename,v_sal fro

Oracle实践--PL/SQL基础之表分区

PL/SQL基础入门之表分区 PL/SQL:过程语言(Procedure  Language)和结构化语言(Structured Query Language)结合而成的编程语言,是对SQL的扩展,支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制语句,可创建存储过程,程序包和触发器等,给sql语句的执行添加程序逻辑,与Oracle服务器和Oracle工具紧密集成,具有可移植性,灵活性和安全性. ---------------------------------------------