SQL Delete clears the table instead of erroring

问题:

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

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:

--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

There's a SQL Fiddle demontrating this here: 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".

Why is this?

回答1:

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

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.

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

delete TableA where ColumnA in (select ColumnA from TableB)

The inner query is producing

• one row for each record in TableB
• one column for each row, whose value is ColumnA from outer query

So the DELETE goes through

回答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.

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