Is there any explanation so as to why SSMS did not raise any compilation error for the below delete query with a subquery that is invalid on its own?

Below is the sequence of steps to reproduce the behavior:

    CREATE TABLE [dbo].[delete_test]
    (
        [id] [int] IDENTITY(1,1) NOT NULL,
    	[name] [nchar](10) NOT NULL
    ) ON [PRIMARY];


Seed data:

    insert dbo.delete_test(name) values(N'a'),(N'b'),(N'c'),(N'd'),(N'e');

Create a backup table:

    select id, name
      into dbo.delete_test_backup 
      from dbo.delete_test where id > 2;

Below statement should ideally not execute and, even if it executes, should only delete rows with `id` values that have been backed up :

    delete dbo.delete_test where id in (
    	select id delete_test_backup 
    );

The message shows that 5 rows were deleted, but I expected only 3 rows to be deleted:

    (5 row(s) affected)

Attempt to execute the sub-query as a standalone fails :

    select id delete_test_backup ;

> Invalid column name 'id'

Below one executes as expected :

    select id from dbo.delete_test_backup ;

> Output :

    id
    --
    3
    4
    5
Top Answer
aaron bertrand (imported from SE)
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](https://blogs.sentryone.com/aaronbertrand/best-practices-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
    )

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.