or
Ravindra HV imported from SE
sql-server sql-server-2008-r2
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
    )
Why doesn't this invalid subquery raise a syntax error?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.