DELETE and Subquery (IN) Problem

When you have a DELETE used with an IN (subquery) all rows will be deleted if your subquery is wrong
What do I mean by this? Let's say you have a table named Table1 with 1 column, the name of that column is Col1
When you do SELECT ID FROM Table1 you get an error.
However when you do
DELETE FROM TEST1 WHERE ID IN
(SELECT ID FROM Table1)
all rows are deleted


Let's start with our test

--Create tables and data
CREATE TABLE TEST1 (ID INT)
GO
CREATE TABLE TEST2 (BLA INT)
GO

INSERT INTO TEST1 VALUES(1)
INSERT INTO TEST1 VALUES(2)
INSERT INTO TEST1 VALUES(3)
INSERT INTO TEST2 VALUES(1)
GO

-- The following will raise an error
SELECT ID FROM TEST2 WHERE BLA = 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 TEST1 WHERE ID IN
(SELECT ID FROM TEST2 WHERE BLA = 1)
GO
--(3 row(s) affected)


--One way of doing it
DELETE FROM TEST1 WHERE ID IN
(SELECT B.ID FROM TEST2 B WHERE BLA = 1)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.


--Another way
DELETE FROM TEST1 WHERE exists
(SELECT * FROM TEST2 B WHERE BLA = 1 AND b.ID =TEST1.ID)
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.