SQL外部查询NOT IN内部查询引用外部查询

I have a bit of a T-SQL conundrum that appears to be working but I was wondering if someone could attempt to give me a breakdown as to what is happening here.Consider the following script:

我有一点似乎有效的T-SQL难题，但我想知道是否有人可以尝试给我一个关于这里发生了什么的细分。考虑以下脚本：

SELECT *
FROM TableA a
WHERE a.CustomerID NOT IN (SELECT b.CustomerID FROM TableB b WHERE a.CustomerID = b.CustomerID AND a.WorkOrder = b.WorkOrder)
AND a.[Date] > DATEADD(DD,-3,GETDATE())

I'm rather stumped as to how the compiler is not imploding on this script. How can it select where NOT IN on a subquery referencing the outer query? Get contents of TableA where CustomerID NOT IN CustomerID's from TableB etc... But when it finds a matching CustomerID in the subquery, the NOT IN kicks in and prevents the record from showing in the outer query select. I'm guessing this is where the compiler stops. But then because that specific CustomerID is not selected, it cannot join in the inner query, thus inner query does not select that CustomerID, then allowing the outer query to select that record? Yes? No? Falling down the rabbit hole? Is there a better way to write this?

关于编译器如何不对这个脚本进行内爆，我感到非常难过。如何在引用外部查询的子查询中选择NOT IN的位置？获取TableA的内容，其中CustomerID不是来自TableB等的CustomerID ...但是当它在子查询中找到匹配的CustomerID时，NOT IN会启动并阻止记录显示在外部查询选择中。我猜这是编译器停止的地方。但是因为没有选择特定的CustomerID，它不能加入内部查询，因此内部查询不选择那个CustomerID，然后允许外部查询选择该记录？是？没有？掉下兔子洞？有没有更好的方法来写这个？

Would appreciate if someone could elaborate on what happening here, or reference something that could explain. I could't really find anyone explaining this process, maybe not using the right search terms.

如果有人可以详细说明这里发生的事情，或者参考可以解释的事情，我将不胜感激。我真的找不到任何人解释这个过程，也许没有使用正确的搜索词。

Thank you!

谢谢！

1 个解决方案

#1

It is called a "Correlated subquery" and "the subquery may be evaluated once for each row processed by the outer query".

它被称为“相关子查询”和“子查询可以为外部查询处理的每一行评估一次”。

So here, for each row of TableA, the subquery seeks the matching data from TableB and determines if the NOT IN condition is met. Then on to the next row in TableA to repeat that cycle until all relevant rows of TableA have been evaluated.

所以在这里，对于TableA的每一行，子查询从TableB中寻找匹配数据并确定是否满足NOT IN条件。然后转到TableA中的下一行重复该循环，直到评估了TableA的所有相关行。

An alternative approach could be a "left excluding join" when you join the 2 tables but then ignore rows where a join exists.

当您加入2个表时，另一种方法可以是“左排除连接”，但忽略存在连接的行。

SELECT
      *
FROM TableA a
LEFT JOIN TableB b ON a.CustomerID = b.CustomerID
                  AND a.WorkOrder = b.WorkOrder
WHERE b.CustomerID IS NULL
AND a.[Date] > DATEADD(DD, -3, GETDATE())
;

or another "semi-join" alternative by using NOT EXISTS:

或使用NOT EXISTS的另一种“半连接”替代方案：

SELECT
      *
FROM TableA a
WHERE NOT EXISTS (
      SELECT NULL
      FROM TableB b
      WHERE a.CustomerID = b.CustomerID
      AND a.WorkOrder = b.WorkOrder
      )
AND a.[Date] > DATEADD(DD, -3, GETDATE())
;

Note that the subquery used for |NOT| EXISTS doesn't have to return any values through the select clause. Some prefer to use "select 1" or "select *" when using EXISTS but in truth it really doesn't matter which is used. Using "select NULL" is my preference.

请注意，子查询用于| NOT | EXISTS不必通过select子句返回任何值。有些人喜欢在使用EXISTS时使用“select 1”或“select *”，但实际上使用哪个并不重要。使用“select NULL”是我的偏好。

You can learn more about these alternatives by inspecting execution plans, see http://sqlfiddle.com/#!6/04064/2 for example.

您可以通过检查执行计划来了解有关这些替代方案的更多信息，例如参见http://sqlfiddle.com/#!6/04064/2。

Original query: "Left excluding join" alternative: "Not Exists" alternative:

原始查询：“左排除加入”替代：“不存在”替代方案：

#1