1
select f1 
from table1 
where f1 in (select f1) 
--running OK.

select f1 
from table1 
where f1 in (select f1 from tablex) 
-- even the column f1 does not exist in tablex, running OK.

delete from table1 
where f1 in (select f1 from tablex)
--If you do this, you may have an accident (delete all records from table1) 
--even the column f1 does not exist in tablex.

Those above 3 SQL statements are all running OK in SQL Server 2008 - 2017.

Pratik Bhavsar
  • 808
  • 8
  • 32
Ginger
  • 31
  • 4

1 Answers1

7

Since f1 isn't prefixed with tablex and is not in tablex it's bound to f1 from table1. And of course table1.f1 is in (table1.f1).

That's not a bug, that's how binding works in SQL. See "Subqueries (SQL Server)" - "Qualifying column names in subqueries":

(...) If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query. (...)

And this is a good example why making a habit of always qualifying columns can be useful -- at least when there's more than one table involved (by subqueries, joins, etc.). Try

... in (select tablex.f1 from tablex)

and you'll get the error you expect.

You can also use table aliases to shorten the qualified columns, as in:

... in (select x.f1 from tablex x)
sticky bit
  • 36,626
  • 12
  • 31
  • 42