Bizarre behavior of a query

Question

I found this query from a developer:

DELETE FROM [MYDB].[dbo].[MYSIGN] where USERID in
(select USERID from [MYDB].[dbo].[MYUSER] where Surname = 'Rossi');

This query deletes every record in table MYSIGN.

The field USERID does not exists in table MYUSER. If I run only the subquery:

select USERID from [MYDB].[dbo].[MYUSER] where Surname = 'Rossi'

It throws the right error, because the missing column.

We corrected the query using the right column, but we didn't figure out:

• Why the first query works?
• Why it deletes every record?

Specs: database is on a SQL SERVER 2016 SP1, CU3.

Answer 1

Apparently you have USERID in [MYDB].[dbo].[MYSIGN] so it's exactly how sql-server resolves unprefixed USERID in (select USERID from [MYDB].[dbo].[MYUSER] where Surname = 'Rossi') - it resolves it to [MYDB].[dbo].[MYSIGN].USERID

Use aliases and it will fail

DELETE FROM [MYDB].[dbo].[MYSIGN] where USERID in
(select t.USERID from [MYDB].[dbo].[MYUSER] t where Surname = 'Rossi');

It's something referred as "accidental correlated sub-query" as @NenadZivkovic named it, i like the term.