SQL Server里的INTERSECT

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

 

INTERSECT与INNER JOIN

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

-- Create the 1st table
CREATE TABLE t1
(
    Col1 INT,
    Col2 INT,
    Col3 INT
)
GO

-- Create the 2nd table
CREATE TABLE t2
(
    Col1 INT,
    Col2 INT
)
GO

-- Create a unique Clustered Index on both tables
CREATE UNIQUE CLUSTERED INDEX idx_ci ON t1(col1)
CREATE UNIQUE CLUSTERED INDEX idx_ci ON t2(col1)
GO

-- Insert some records into both tables
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (NULL, 3, 3)
INSERT INTO t2 VALUES (2, 2), (NULL, 3)
GO
GO

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

SELECT Col1, Col2 FROM t1
INTERSECT
SELECT Col1, Col2 FROM t2
GO

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

SELECT t1.col1, t1.col2 FROM t1
INNER JOIN t2 ON t2.col1 = t1.col1
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)上的剩余谓语可以通过提供在导航结构里包含所有必须的列来剔除，如下所示：

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

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

 

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

-- Drop all supporting indexes
DROP INDEX id_nci ON t1
DROP INDEX idx_ci ON t1
DROP INDEX idx_ci ON t2
GO

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

 

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

小结

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

感谢关注！

参考文章：

https://www.sqlpassion.at/archive/2015/02/09/intersect-sql-server/