SQL Server里的INTERSECT
在今天的文章里，我想讨论下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
下图显示了 INTERSECT 和 INNER 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 . 之间的区别。你也看到，有很好的索引设计对它非常重要，这样的话查询优化器可以生成很好的执行计划。
感谢关注！
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
