Why doesn't this invalid subquery raise a syntax error?
This is the same as:

(
  SELECT id AS delete_test_backup
)


In other words, you have merely assigned a column alias to id, and there is no reference at all to a table called delete_test_backup.

According to the ANSI standard, what SQL Server is supposed to do in this case - when it has a subquery and does not find id at that scope - is traverse to the outer scope(s) until it finds one. If it does, it assumes that's the one you meant.

See:


Best practices : Properly referencing columns
KB #298674
Connect #735178
Connect #624370
Connect #392492
Connect #362016
Connect #265772
Connect #772612



  * Microsoft decided to eradicate all of Connect instead of archiving the content, and also disappeared the knowledge base article for some reason.


To avoid the problem, always use table aliases, which allow you to be 100% explicit about which table a column comes from (not naming every key column id helps, too). Of course you can leave these prefixes out accidentally, just like you left out the FROM, but the following syntax would have failed as you expect, and most other omissions would have left you with a parsing error rather than accidental execution:

(
  SELECT x.id FROM delete_test_backup AS x
)