IN谓词如何在SQL中工作? [英] How does the IN predicate work in SQL?

问题描述

在为这个问题的答案进行了预配对后，我发现我无法验证我的答案.

After prepairing an answer for this question I found I couldn't verify my answer.

在我的第一份编程工作中，我被告知IN ()谓词中的查询将针对父查询中包含的每一行执行，因此应避免使用IN.

In my first programming job I was told that a query within the IN () predicate gets executed for every row contained in the parent query, and therefore using IN should be avoided.

例如，给定查询:

SELECT count(*) FROM Table1 WHERE Table1Id NOT IN (
SELECT Table1Id FROM Table2 WHERE id_user = 1)

Table1 Rows | # of "IN" executions
----------------------------------
      10    |       10
     100    |      100
    1000    |     1000
   10000    |    10000

这是正确的吗? IN谓词实际上是如何工作的?

Is this correct? How does the IN predicate actually work?

推荐答案

关于针对每一行执行子查询的警告是正确的-对于相关子查询.

The warning you got about subqueries executing for each row is true -- for correlated subqueries.

SELECT COUNT(*) FROM Table1 a 
WHERE a.Table1id NOT IN (
  SELECT b.Table1Id FROM Table2 b WHERE b.id_user = a.id_user
);

请注意，子查询引用外部查询的id_user列. Table1的每一行上的id_user值可能不同.因此，子查询的结果可能会有所不同，具体取决于外部查询中的当前行. RDBMS 必须执行多次子查询，一次为外部查询中的每一行.

Note that the subquery references the id_user column of the outer query. The value of id_user on each row of Table1 may be different. So the subquery's result will likely be different, depending on the current row in the outer query. The RDBMS must execute the subquery many times, once for each row in the outer query.

您测试的示例是不相关的子查询.大多数现代的RDBMS优化器都值得一试，他们应该能够知道子查询的结果何时不取决于外部查询的每一行中的值.在这种情况下，RDBMS将一次运行子查询，将其结果缓存，然后将其重复用作外部查询中的谓词.

The example you tested is a non-correlated subquery. Most modern RDBMS optimizers worth their salt should be able to tell when the subquery's result doesn't depend on the values in each row of the outer query. In that case, the RDBMS runs the subquery a single time, caches its result, and uses it repeatedly for the predicate in the outer query.

PS:在SQL中，IN()被称为谓词"，而不是语句.谓词是语言的一部分，其评估结果为true或false，但不一定必须作为语句独立执行.也就是说，您不能只将它作为SQL查询运行:"2 IN(1,2,3);"尽管这是一个有效的谓词，但它不是一个有效的语句.

PS: In SQL, IN() is called a "predicate," not a statement. A predicate is a part of the language that evaluates to either true or false, but cannot necessarily be executed independently as a statement. That is, you can't just run this as an SQL query: "2 IN (1,2,3);" Although this is a valid predicate, it's not a valid statement.

这篇关于IN谓词如何在SQL中工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！