SQL Delete清除表而不是错误

I have a piece of SQL which (you would think) wouldn't compile, but which instead deletes all rows from the target table.

我有一块SQL(你会认为)不会编译,而是删除目标表中的所有行。

Consider this setup:

考虑这个设置:

create table TableA (ColumnA varchar(200));
create table TableB (ColumnB varchar(200));

insert TableA values ('A'),('B'),('C');
insert TableB values ('A');

Then the following sql:

然后是以下sql:

--Returns all rows from TableA
select * from TableA;

--Does not error (ColumnA does not exist on TableB)
delete TableA where ColumnA in (select ColumnA from TableB)

--No Rows are returned
select * from TableA;

The delete statement above causes all rows to be removed from TableA, rather than erroring that ColumnA doesn't exist in TableB

上面的delete语句导致从TableA中删除所有行,而不是错误地表示TableB中不存在ColumnA

There's a SQL Fiddle demontrating this here: http://www.sqlfiddle.com/#!3/9d883/6

这里有一个SQL小提琴:http://www.sqlfiddle.com/#!3/9d883/6

It seems that the ColumnA from TableA is being picked up, but expected it to be "out of scope".

似乎TableA中的ColumnA被拾取,但预计它将“超出范围”。

Why is this?

为什么是这样?

2 个解决方案

#1

That works as expected, due to the correlation between ColumnA in the inner query to the outer.

由于内部查询中的ColumnA与外部之间的相关性,这可以按预期工作。

This commonly used correlated query pattern is valid

这种常用的相关查询模式是有效的

DELETE TableA WHERE NOT EXISTS (select * from TableB where TableB.ID=TableA.ID)

It removes TableA entries that don't have a dependent record in TableB.

它删除了TableB中没有依赖记录的TableA条目。

It shows that you can reference TableA columns in a correlated query. In your query

它表明您可以在相关查询中引用TableA列。在您的查询中

delete TableA where ColumnA in (select ColumnA from TableB)

The inner query is producing

内部查询正在生成

one row for each record in TableB

表B中的每条记录占一行

one column for each row, whose value is ColumnA from outer query

每行一列,其值为外部查询的ColumnA

So the DELETE goes through

所以DELETE经历了

#2

While I understand the confusion, it is behaving as it should. ColumnA is still "in scope". In fact you could join on it in your subquery if you wanted. The brackets don't limit the scope, but from a readability standpoint I can see the confusion that it creates.

虽然我理解这种混乱,但它的表现却应该如此。 ColumnA仍处于“范围内”。事实上,如果你愿意,你可以在你的子查询中加入它。括号不限制范围,但从可读性的角度来看,我可以看到它产生的混乱。

This is another example of why it's a good idea to always prefix your column names with the table name (or alias).

这是为什么始终使用表名(或别名)为列名添加前缀的另一个示例。

#1