你的位置:首页 > 数据库

[数据库]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 table11 CREATE TABLE t212 (13   Col1 INT,14   Col2 INT15 )16 GO17 18 -- Create a unique Clustered Index on both tables19 CREATE UNIQUE CLUSTERED INDEX idx_ci ON t1(col1)20 CREATE UNIQUE CLUSTERED INDEX idx_ci ON t2(col1)21 GO22 23 -- Insert some records into both tables24 INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (NULL, 3, 3)25 INSERT INTO t2 VALUES (2, 2), (NULL, 3)26 GO27 GO

 

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

1 SELECT Col1, Col2 FROM t12 INTERSECT3 SELECT Col1, Col2 FROM t24 GO

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

1 SELECT t1.col1, t1.col2 FROM t12 INNER JOIN t2 ON t2.col1 = t1.col13 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 Index2 CREATE NONCLUSTERED index id_nci ON t1(Col1, Col2)3 GO

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

 

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

1 -- Drop all supporting indexes2 DROP INDEX id_nci ON t13 DROP INDEX idx_ci ON t14 DROP INDEX idx_ci ON t25 GO

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

 

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

 

小结

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

感谢关注!