TSQL操作符IN和INNER JOIN的区别

Question

使用SQL Server 2014：

以下语句是否有性能差异？

DELETE FROM MyTable where PKID IN (SELECT PKID FROM @TmpTableVar)

与

DELETE FROM MyTable INNER JOIN @TmpTableVar t ON MyTable.PKID = t.PKID

- Allan Xu

---

请查看每个执行计划。在这种情况下，它们可能会完全相同。 - Sean Lange

我进行了一些测试，两者在几乎所有情况下都很相似。https://dev59.com/HJnga4cB1Zd3GeqPSws5#38445287 - TheGameiswar

澄清一下：EXPLAIN 命令用于获取执行计划信息。 - Mike Robinson

但是即使有不同的统计数据，在运行时统计数据也将相同。不确定您所说的更广泛的规则是什么意思？ - Sean Lange

@AllanXu 执行计划就是规则。查询将根据主要定义在MyTable上的索引和该表的统计信息来执行。表变量没有索引或统计信息，因此大小更改不会影响查询的执行方式。但如果您在2016年使用了内存表，则情况会非常不同。 - Panagiotis Kanavos

Answer 1

在您提供的示例中，执行计划将是相同的（很可能）。但是，具有相同的执行计划并不意味着它们对于该语句来说是最佳的执行计划。

我在两个查询中看到的问题是使用“表变量”。SQL Server始终假定表变量中只有1行。仅在SQL Server 2014及更高版本中，这种假设已更改为100行。

因此，无论您有多少行，SQL Server在表变量中始终假定您有一行。您可以稍微更改代码，通过使用“临时表”来让SQL Server更好地了解在该表中会有多少行，并且由于您的表变量中有一个“PK_ID”列，因此您还可以在该表上创建索引，以便让SQL Server为此查询提供最佳的执行计划。

SELECT PKID INTO #Temp
FROM @TmpTableVar

-- Create some index on the temp table here .....

DELETE FROM MyTable
WHERE EXISTS (SELECT 1 
              FROM #Temp t
              WHERE MyTable.PKID = t.PKID)

注意

如果您在表变量上使用IN运算符，则会正常工作，因为它是主键列。但是，如果您在可空列上使用IN运算符，则结果可能会让您惊讶。只要在正在检查的列中发现NULL值，IN运算符就会出问题。

我个人更喜欢使用Exists运算符进行此类查询，但内连接也应该可以正常工作，但如果可以避免，请勿使用IN运算符。

- M.Ali

---

感谢您的帮助。@tableVariables周围的行数是否也适用于内部连接方法？我考虑采用内部连接作为更安全的方法。 - Allan Xu

没问题，是的，它可能会导致SQL Server生成效率较低的执行计划，因为SQL Server假设该表中只有一行，基于主键列的连接应该只返回一行。但这可能并非总是如此，在这种情况下避免使用表变量。 - M.Ali