DELETE Statement with subquery strange behavior [duplicate]

Question

I have 2 tables with which I'm executing a delete statement. When I select a column with an invalid column name it throws an error. But when I use the column name of the first table it just deletes the records.

//Query:

CREATE TABLE FOO (ID INT)
GO
CREATE TABLE FOOBAR (NUMBER INT)
GO

INSERT INTO FOO VALUES(1)
INSERT INTO FOO VALUES(2)
INSERT INTO FOO VALUES(3)
INSERT INTO FOOBAR VALUES(1)
GO

-- The following will raise an error
SELECT ID FROM FOOBAR WHERE NUMBER = 1
GO
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.


-- The following statement will delete the entire table
DELETE FROM FOO WHERE ID IN
(SELECT ID FROM FOOBAR WHERE NUMBER = 1)
GO
--(3 row(S) affected)

When I used the Alias name it worked fine. Is it a bug or something else?

How does this happen?

Answer 1

In the following statement the inner select will get the column ID from table FOO and not from table FOOBAR.

DELETE FROM FOO WHERE ID IN
(SELECT ID FROM FOOBAR WHERE NUMBER = 1)

In order to make this more obvious you could try adding Alliases to the tables and do a select instead of a delete, just to see the results:

SELECT * FROM FOO F WHERE ID IN
(SELECT F.ID FROM FOOBAR FB WHERE FB.NUMBER = 1)