SQL中“in”和连接哪个更适合删除操作？

Question

我有另一位程序员编写了一堆删除语句，看起来像这样:

DELETE dbo.Test WHERE TestId IN (SELECT TestId FROM #Tests )

这个很简单，但是有些语句中会有子查询和子子查询。

我总是把这些语句写成join。我觉得这就像是一个内联函数，会被反复调用。

然而，我知道优化器能够做出一些非常神奇的事情，而且新的东西也在不断添加。我已经有一段时间没有研究过Join与In之间的区别了，我想问一下现在是否仍然应该使用Join。

使用"join"还是"in"有影响吗？

- Vaccano

Answer 1

现代大部分SQL优化器可以根据这样的语句进行联接，但并不是保证，而且查询变得越复杂，优化器选择正确操作的可能性就越小。

一般来说，在这种情况下使用IN不是一个好的做法。(个人意见警告) 它真的不应该那样使用。

一个很好的经验法则(再次强调，这是有争议的，但不是错误的)是，在使用IN时，应该坚持有限的列表。例如:

SELECT DISTINCT * FROM foo WHERE id IN (1, 2, 3, ...);

当与另一个表格对比时，以下其中一种是首选：

SELECT DISTINCT f.* FROM foo AS f 
INNER JOIN bar as b on b.foo_id = f.id;

SELECT DISTINCT * FROM foo AS f
WHERE EXISTS (SELECT NULL FROM bar AS b WHERE b.foo_id = f.id);

根据你所做的事情和数据的性质，这些内容的效果可能会有所不同。

请注意，在这个简单的例子中，IN、JOIN和EXISTS很可能会产生完全相同的查询计划。然而，当你开始处理多个表之间的一些严肃的业务逻辑时，你可能会发现查询计划显著地分歧。

- Jeremy Holovacs

---

如果 in 不是用来这样使用的，那么它应该如何使用？我看不出那种用法有什么问题 - 一些 DBMS 甚至不支持在 delete 语句中使用连接，你就没有其他选择了，而且它们的性能也很好。并且：连接不一定是 IN 条件的替代品，两者具有不同的目的，可能会返回不同的结果。 - user330315

@a_horse_with_no_name IN 通常用于与列值进行比较的有限值列表。一般来说，除非您实际上有一个有限的列表要查找，否则最好使用 EXISTS 子句或 JOIN 而不是 IN。总的来说，这将产生更一致的结果。 - Jeremy Holovacs

Answer 2

有三种方式可以看待代码。它是否具有功能性？它是否提供了良好的代码维护/可读性？它是否执行良好？

从功能上讲，使用IN子句或连接没有区别，如果两者执行相同的操作。

从维护/可读性方面来看，在简单情况下，连接语法可能更加直观。然而，如果在IN子句中使用的子查询是一个复杂的多表连接操作，那么它可能更具描述性，并且更容易在以后进行调试（请想象一下需要查看代码但上下文有限的人的处境）。

最后，从性能的角度来看，这将取决于表中的行数、可用索引（包括它们的统计信息）以及成本优化器如何处理查询（这可能因SQL版本而异），进而决定哪个的性能更好。

因此，与IT领域中的大多数决策一样，真正的答案是......取决于具体情况。

- Charles

Answer 3

最有效的途径是

Delete t1
From table1 t1 
Inner Join table2 t2 on t1.col1=t2.col2

在table2中，您可以分配临时表(#Tests)，这将更快。

- Maverick