"WHERE x IN (SELECT value FROM table)“对于大表来说效率低吗？

Question

以下两个SQL查询返回相同的结果：

    SELECT * FROM Table1
    WHERE Table1.Value1 = 'something' OR Table1.Value2 IN (SELECT Value2 FROM Table2)

    SELECT * FROM Table1
    LEFT JOIN Table2 
    ON Table1.Value2 = Table2.Value2
    WHERE (Table1.Value1 = 'something' OR Table2.Value2 IS NOT NULL)

类似地，这两个查询返回相同的结果：

    SELECT * FROM Table1
    WHERE Table1.Value1 = 'something' AND Table1.Value2 NOT IN (SELECT Value2 FROM Table2)

    SELECT * FROM Table1
    LEFT JOIN Table2
    ON Table1.Value2 = Table2.Value2
    WHERE Table1.Value1 = 'something' AND Table2.Value2 IS NULL

就我个人而言，我发现使用" in“或"NOT IN”的选项更容易阅读(特别是因为我的实际查询中已经有一堆连接)。但是，如果Table2中的值的数量变大(目前它只返回三个结果)，该查询是否会变得低效？或者查询优化器会找出它并在幕后将其转换为连接吗？我使用的是SQL Server 2012。

Answer 1

第一个会更好，因为：

SELECT <cols> 
  FROM dbo.Table1
  WHERE Value1 = 'something' 
  OR EXISTS (SELECT 1 FROM dbo.Table2 WHERE Value2 = Table1.Value2);

虽然您的性能问题-假设Value2在这两个位置都被索引，并且您实际上只选择所需的列，而不是使用*强制扫描或查找-将是OR。如果对Value1进行了适当的索引，您可能会考虑这种替代方案，至少是为了测试性能差异(当您只有三行时，您将希望查看计划，而不仅仅是测量时间)：

SELECT <cols>
  FROM dbo.Table1 
  WHERE Value1 = 'something'
UNION ALL
SELECT <cols>
  FROM dbo.Table1
  WHERE Value1 <> 'something'
  AND EXISTS (SELECT 1 FROM dbo.Table2 WHERE Value2 = Table1.Value2);

对于NOT IN查询，这将更加可靠，并且至少与您提供的两个选项一样有效：

SELECT <cols>
  FROM dbo.Table1
  WHERE Value1 = 'something' 
  AND NOT EXISTS (SELECT 1 FROM dbo.Table2 WHERE Value2 = Table1.Value2);

索引在这里将是关键，但重要的是要了解NOT IN和LEFT OUTER JOIN可能会让您陷入困境。请参阅以下文章：

http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

Answer 2

• 这两个查询不是等价的:当使用IN (或not IN)时，对于Table1中的每一行，您将得到0或1结果行。使用join时，每行可能出现0、1或多次。因此，“两个SQL查询返回相同的结果”-只是因为特定的数据。或者Table2在使用UNION作为

的Value2

• 上有唯一的索引/主键

选择...其中Table1.Value1 = 'something‘UNION (ALL) SELECT ...其中Table1.Value2 = Table2.Value2

可能还会给出不同的结果，因为UNION将删除重复项(这可能很有价值)，并且如果某些结果行与两个条件都匹配，则UNION ALL可能会使它们加倍

如果您要在第一个查询中使用EXISTS()而不是IN()，请使用

• ...你很可能会得到相同的执行计划，因为sql优化器会认识到这些操作是相等的，并会选择相同的优化方式。
• 即使你可以在语句中使用子查询，sql优化器也可能会以不使用子查询的方式重建计划。换句话说，两个写得不同的相等查询最有可能针对同一计划进行优化。
• 用于可能无法工作的非常复杂的查询，因为sql可能没有足够的时间来完全完成优化，并在停止的地方停止。在这种情况下，这种不同但相似查询可能具有不同的结果。你需要尝试和测试。

计划和性能将取决于数据、参数类型(常量、变量、计算值)、统计数据、索引...对于这些标准的某些组合，Query-1将比Query-2更优，反之亦然。

要获得正确的答案，您需要分析和比较执行计划