当子查询格式错误时，T-SQL 从表中删除所有行 [英] T-SQL Deletes all rows from a table when subquery is malformed

问题描述

可能的重复:
sql server 2008 management studio 不检查我的查询语法

我今天遇到了一个问题，子查询不好，结果是父表中的所有行都被删除了.

I ran across an issue today where a subquery was bad and the result was all rows from the parent table were deleted.

TableA
ID,
Text,
GUID

TableB
ID,
TableAID,
Text

delete from TableB
where id in (
  select TableAID
  from TableA
  where GUID = 'fdjkhflafdhf'
)

如果您单独运行子查询，则会收到错误，因为表 A 中不存在列 (TableAID).如果您运行完整查询 - 它会删除表 B 中的所有记录而不会出错.

If you run the subquery by itself you get an error since the column (TableAID) doesn't exist in Table A. If you run the full query - it deletes all records from table B without an error.

我还尝试了以下查询，它们删除了 0 条记录(预期)

I also tried the following queries which removed 0 records (expected)

delete from TableB where id in (null) 
delete from TableB where id in (select null)

有人可以向我解释为什么在查询格式错误时会发生这种情况吗?为什么它似乎评估为 true?

Can someone explain to my why this is occurring when the query is malformed? Why does it seem to evaluate to true?

注意:这是在 SQL Server 2008 R2 上测试的

Note: This was tested on SQL Server 2008 R2

推荐答案

由于 TableAID 在 TableA 中不存在，查询正在使用 中的列表B.因此查询与以下内容相同:

As TableAID doesn't exist in TableA, the query is using the column from TableB. Therefore the query is the same as:

delete from TableB
where id in (
  select TableB.TableAID
  from TableA
  where GUID = 'fdjkhflafdhf'
)

所以本质上它是这样做的:

So in essence it's doing:

delete from TableB
where id in (TableAID)

如果您使用子查询，最好在引用时提及您的表名.以下将抛出异常:

If you are using sub-queries its best to mention your table names when referencing. The following WILL throw an exception:

 delete from TableB
    where id in (
      select TableA.TableAID
      from TableA
      where TableA.GUID = 'fdjkhflafdhf'
    )

此外，我会使用别名，以便我们知道我们指的是哪个查询:

Furthermore I would use an alias so that we know which query we are referring to:

 delete from TableB
    where id in (
      select a.TableAID
      from TableA a
      where a.GUID = 'fdjkhflafdhf'
    )

这篇关于当子查询格式错误时，T-SQL 从表中删除所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！