T-SQL Deletes all rows from a table when subquery

问题:

Possible Duplicate:
sql server 2008 management studio not checking the syntax of my query

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

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.

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)

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

Note: This was tested on SQL Server 2008 R2

回答1:

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