在今天的文章里，我想討論下SQL Server里的INTERSECT設置操作。INTERSECT設置操作彼此交叉2個記錄集，返回2個集里列值一樣的記錄。下圖演示了這個概念。

你會發現，它和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個表上創建了唯一聚集索引，并插入了一些測試記錄?，F在讓我們來彼此交叉這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

下圖顯示了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 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.之間的區別。你也看到，有很好的索引設計對它非常重要，這樣的話查詢優化器可以生成很好的執行計劃。

感謝關注！