WHERE IN (SELECT NonExistingColumnName)会导致意外行为

Question

最近我遇到了这样的情况：我需要从一个表中删除一些行，但是拼写错误导致一个列名错误。在没有报错的情况下，表中所有的行都被删除了。以下是重现此问题的脚本。

现有一个 Order 表，其中包含四个带有 OrderID 的订单。还有一个 LIST_TO_DELETE 表，其中只有一个 ItemID。

我应该使用：

DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT ItemID FROM #LIST_TO_DELETE  )

我使用了另一种方法

DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT OrderID FROM #LIST_TO_DELETE  )

这导致在我只想删除一个订单时，所有 #Orders 表中的行都被删除了。

CREATE TABLE #Orders (OrderID INT, OrderName VARCHAR(100))
INSERT INTO #Orders(OrderID, OrderName) VALUES (1,'Order One'),(2,'Order Two'),(3,'Order Three'), (4,'Order Four')

CREATE TABLE #LIST_TO_DELETE (ItemID INT);INSERT INTO #LIST_TO_DELETE(ItemID) VALUES (1)

DECLARE @rowcount INT = 1
WHILE @rowcount > 0 
BEGIN
    DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT OrderID FROM #LIST_TO_DELETE  )
    SET @rowcount = @@rowcount
END 

SELECT * FROM #Orders

DROP TABLE #Orders
DROP TABLE #LIST_TO_DELETE

在我的原始代码中，Orders表是真实存在的，而LIST_TO_DELETE是一个表变量，但使用哪种类型的表似乎没有任何区别。 如果我使用OrderID或ItemID之外的任何列名，就会出现错误。

DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT OtherID FROM #LIST_TO_DELETE  )
 Invalid column name 'OtherID'

为什么这段代码会表现出这种行为？

- Don

Answer 1

这是一个已知的问题。

下面的语句将错误地从用户表中删除所有行：

DELETE FROM users WHERE user_id IN (SELECT user_id FROM groups);

即使“groups”表没有名为“user_id”的列，以下语句也不会出错：

但是，下面这条语句将抛出一个错误：

DELETE FROM users WHERE user_id IN (SELECT g.user_id FROM groups g);

Msg 207, Level 16, State 1, Line 1
Invalid column name user_id

编辑

DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT OtherID FROM #LIST_TO_DELETE  )
 Invalid column name 'OtherID'

这会抛出一个错误，因为在 #Orders 中不存在 OtherID

- Raj

---

我不认为我遇到过这种情况，但是没有别名的话，SQL Server会认为用户ID来自用户表。一个简单的测试是使用在任何一张表中都不存在的列名，看看会发生什么。 - Zohar Peled

那会引发一个错误。你关于 SQL 假定 user_id 来自 users 表是正确的。 - Raj

@Raj 你有微软MSDN或其他页面的链接，描述了这个问题吗？ - Don

为什么它假设OrderID来自Orders表，而代码明确指出我们想要从LIST_TO_DELETE表中获取OrderID。 我理解如果我使用了连接或同一张表两次，可能需要一个表别名。 SQL是否会在后台将“WHERE IN”转换为“JOIN”？ - Don

因为你可以在子查询中使用外部查询的字段，例如：delete from users where userid in (select userid from groups where groups.somefield = users.somefield)。我知道这个删除语句没有实际意义，只是为了演示可以在此处使用外部查询的字段。 - GuidoG

我没有任何与MSDN的链接，但是从一个编码标准文档中了解到了这种行为，该文档是我十多年前参与的一个项目的一部分。理想情况下，当没有限定时，内部查询会被视为相关子查询。 - Raj

Answer 2

阅读文章以了解发生了什么：子查询中的列名资格 通常情况下，语句中的列名都会自动地由同级别FROM子句引用的表来资格化。如果在子查询的FROM子句中引用的表中不存在某个列，则该列会自动地由外部查询的FROM子句中引用的表来资格化。

- Denis Rubashkin