即使列不存在，数据也用 IN 子句删除 [英] Data deleted with IN clause even if the column doesn't exist

问题描述

所以，这是您可以使用的测试查询:

So, here is the test query you could play with:

select top 10 * into #tmp FROM A
delete from #tmp WHERE xxx_id in (select xxx_id FROM B)

实际上，这10条记录都被删除了.问题是为什么这10条记录被删除了?

Actually, all these 10 records are deleted. The question is why are these 10 records deleted?

注意:xxx_id只是A表中的一列，B表中不存在.但删除语句无论如何有效".

Note: xxx_id is one column in table A only, it doesn't exist in table B. But the delete statement "works" anyway.

以下是这种行为的演示:http://sqlfiddle.com/#!6/963f9/1/1

Here's a demonstration of this behaviour: http://sqlfiddle.com/#!6/963f9/1/1

我在 MSDN 中找到了答案:http://social.msdn.microsoft.com/Forums/en-US/418722dc-a7bf-44c5-a2f6-e8d1cd00dbdc/in-clause-ignores-error-in-subquery-possible-bug?forum=transactsql实际上，MSSQL 试图在子查询中将 xxx_id 盲注到表 B，如果没有找到，它会尝试将它盲注到表 A.所以，查询等于:

I found the answer in MSDN: http://social.msdn.microsoft.com/Forums/en-US/418722dc-a7bf-44c5-a2f6-e8d1cd00dbdc/in-clause-ignores-error-in-subquery-possible-bug?forum=transactsql Actually, the MSSQL tried to blind the xxx_id to table B in subquery, if not found, it tries to blind it to table A. So, the query equals:

delete from #tmp WHERE xxx_id = xxx_id

并且所有数据都被删除了.

and all data was deleted.

推荐答案

这是众所周知的行为.我并不是说这有点出乎意料.

It's rather well-known behaviour. I'm not saying it's not somewhat unexpected.

问题是 SQL Server 尝试将名称绑定到对象，这是 id 对查询所做的第一件事.对于子查询，它首先尝试将列绑定到子查询中的表；如果不成功，它会将列绑定到外部查询中的表.

The thing is that SQL Server tries to bind names to objects as one of first things id does with the query. With a subquery, it tries to bind columns to tables in the subquery first; if it don't succeed, it will bind columns to tables in outer query.

总是对表使用别名实际上是一个很好的理由.

It's actually an excellent reason to always use aliases with tables.

这篇关于即使列不存在，数据也用 IN 子句删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！