SQL Server – Why Doesn’t This Invalid Subquery Raise a Syntax Error?

sql serversql-server-2008-r2t-sql

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

Best Answer

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:

* 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
)
Related Question