SQL Server里的INTERSECT

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

在今天的文章里,我想讨论下SQL Server里的INTERSECT设置操作。INTERSECT设置操作彼此交叉2个记录集,返回2个集里列值一样的记录。下图演示了这个概念。

 

INTERSECT与INNER JOIN

你会发现,它和2个表间的INNER JOIN几乎一样。但今天我会介绍它们之间的一些重要区别。让我们从创建作为输入的2个简单表开始。 

复制代码
 1 -- Create the 1st table
 2 CREATE TABLE t1
 3 (
 4     Col1 INT,
 5     Col2 INT,
 6     Col3 INT
 7 )
 8 GO
 9 
10 -- Create the 2nd table
11 CREATE TABLE t2
12 (
13     Col1 INT,
14     Col2 INT
15 )
16 GO
17 
18 -- Create a unique Clustered Index on both tables
19 CREATE UNIQUE CLUSTERED INDEX idx_ci ON t1(col1)
20 CREATE UNIQUE CLUSTERED INDEX idx_ci ON t2(col1)
21 GO
22 
23 -- Insert some records into both tables
24 INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (NULL, 3, 3)
25 INSERT INTO t2 VALUES (2, 2), (NULL, 3)
26 GO
27 GO
复制代码

从T-SQL代码里你可以看到,我也在2个表上创建了唯一聚集索引,并插入了一些测试记录。现在让我们来彼此交叉这2个表:

1 SELECT Col1, Col2 FROM t1
2 INTERSECT
3 SELECT Col1, Col2 FROM t2
4 GO

SQL Server返回2条记录:列值为2和列值为NULL的记录。这是和INNER JOIN的第1个大区别:如果NULL值出现在2个表里,这些记录会被忽略。当你在Col列上进行2个表之间的INNER JOIN操作,含NULL值的记录不会返回:

1 SELECT t1.col1, t1.col2 FROM t1
2 INNER JOIN t2 ON t2.col1 = t1.col1
3 GO

下图显示了INTERSECTINNER JOIN方法结果集的不同:

 

现在我们来分析下INTERSECT设置操作的执行计划。因为在Col列上你有支持的索引,查询优化器可以翻译INTERSECT操作为传统的INNER JOIN逻辑操作。

 

但这里Nested Loop(Inner Join)并不真正进行INNER JOIN操作。我们来看下为什么。当你查看Nested Loop运算符属性时,你会看到在Clustered Index Seek (Clustered)运算符上有剩余谓语(residual predicate)。

 

剩余谓语在Col2上评估,因为那列不是刚才创建的聚集索引导航结构的一部分。如我刚开始说的,SQL Server需要在2个表所有列找到匹配的行。使用Clustered Index Seek (Clustered)运算符和剩余谓语,SQL Server只检查在t1表里是否有同样列值的匹配记录。而且Nested Loop运算符本身只返回从一个表的列值——这里是t1表。

 

因此INNER JOIN只是个左半连接(Left Semi Join):SQL Server检查在右表里是否有我们匹配的记录——如果是的话,匹配的记录从左表返回。Clustered Index Seek (Clustered)上的剩余谓语可以通过提供在导航结构里包含所有必须的列来剔除,如下所示:

1 -- Create a supporting Non-Clustered Index
2 CREATE NONCLUSTERED index id_nci ON t1(Col1, Col2)
3 GO

现在当你再次看INTERSECT运算符的执行计划,你会看到SQL Server在刚才创建的索引进行Index Seek (NonClustered)操作,剩余谓语已经不再需要。

 

现在当我们删除所有支持的索引结构,我们来看执行计划会变成什么样。

复制代码
1 -- Drop all supporting indexes
2 DROP INDEX id_nci ON t1
3 DROP INDEX idx_ci ON t1
4 DROP INDEX idx_ci ON t2
5 GO
复制代码

当你再次对2个表进行INTERSECT,现在在执行计划里你会看到Nested Loop (Left Semi Join)运算符。SQL Server现在需要在执行计划里进行左半物理连接,通过在内部上进行Table Scan运算符和在Nested Loop里用剩余谓语进行逐行比较。

 

这个执行计划并不真的高效,因为在内部Table Scan需要反复进行——对来自外表返回的每一行。如果我们想尽可能高效的进行INTERSECT设置操作,支持的索引非常重要。 

小结

INTERSECT设置操作并不可怕,但几乎没人很懂它。当你用它时,你要意识到它和INNER JOIN.之间的区别。你也看到,有很好的索引设计对它非常重要,这样的话查询优化器可以生成很好的执行计划。



本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4711588.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
14天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
SQL 存储 关系型数据库
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
71 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
51 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
253 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
175 1
|
3月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
187 3
|
3月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。